Mysql dump with View dependent on user

MySQLmysqldump

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)

Placeholder table in top of file with other tables.
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.

Best Answer

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.

Related Solutions

Mysql – How to open a MySQL Database Dump

Although there are a number of tools out there, I prefer the command line for tasks such as this. Better performance, and just simpler.

Make sure you're logged into the mysql client:

mysql -u <username> -p

The first step is to create a database in mysql that you want the data in:

CREATE DATABASE <database name>;

Then you need to open--or use--that new database, which is empty:

USE <database name>;

All that's left is to do the import:

\. /path/to/file (or if in Windows, \. C:\PATH\TO\FILE)

If all goes well, you'll see a bunch of output about "Query successful, N rows affected" and so on.

Mysql 5.1.X How to upload data from dump file without the partitioning commands

If your target database is an older MySQL version such as MySQL 5.0, then partitioning commands will already be ignored since they are wrapped in comments like this:

/*!50500 PARTITION BY RANGE  COLUMNS(column)
(PARTITION partition_name VALUES LESS THAN ('value') ENGINE = InnoDB,
 PARTITION partition_name VALUES LESS THAN ('value') ENGINE = InnoDB) */;

If you are loading into MySQL 5.1 or higher, then one trick that came to my mind is to change the version number in the comment section. You do not have to manually edit the dump file, you can process the file through sed. For example:

sed 's\!50500 PARTITION\!60000 PARTITION\g' dumpfile.sql | mysql

Best Answer

Related Solutions

Mysql – How to open a MySQL Database Dump

Mysql 5.1.X How to upload data from dump file without the partitioning commands

Related Question