1

I use simple mysqldump with only parameters are login and db details. No option of anykind is set. Now, when I recieve the dump file, it export the view in steps (I am sure you all know it, but just for my clarification)

  1. Placeholder table in top of file with other tables.
  2. then at end, it drop this table and create View with definer.

This works good if I have to upload this database back to original server. But how I handle this when I have to import this data in development server where I do not get the same username as defined in production database? Is there any option I can use to elimmiate the "definer" in view. Why doesn't it simply use "Create View as..." syntax?

I try to read about it, but doesn't found any good information, Please enlight me on the topic thanks.

2

The reason for the definer clause is that view can act as protective layers above the tables. For example, instead of limiting users to be able to read specific columns from a table with otherwise sensitive data, one can create a view which only presents allows columns, and let users select from that view.

Sometimes you may allow users to read a view even though you forbid them from any access to the underlying tables, in which case security=definer and definer=some_user_usually_root.

Other times views just help out in simplifying common complex joins & aggregations, and have nothing to do with security. In which case you want the users of this view to use it under same set of privileges as they would for underlying tables. In which case security=invoker.

So far I've explained why the security stuff is even there. Now for your second question: what should you do if you don't have the definer user in your new database: for this reason it is common to make the definer root@localhost, which is an account typically found everywhere.

In your case I'm guessing that's not what you have, in which case you'll need to modify the dump file before loading into MySQL. Not to worry, you can do it on the fly with sed. There's no built-in mechanism to fix this for you.

1
  • Thanks for great explanation. Wondering why often documentation doesn't use such language. I am currently use sed only, but usage is clear now and make sense. – Sumit Gupta Aug 3 '14 at 2:30

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.