“It would be nice to have an option, that would allow to suppress the DEFINER statement in the CREATE VIEW statements generated by mysqldump. This would help when transferring data structures between databases with different security models.”
TLDR;
Use mysqlpump with option --skip-definer instead of mysqldump.
The Story
This was requested as MySQL Bug #24680 on Nov 29, 2006. This feature request got large Community support. Even if we cannot see the number of people who voted for this request, the number of comments is impressive.
The request is very reasonable: mysqldump is widely used during application development and it is a very common practice to migrate database structure between developers’ machines and to the production servers.
Imagine a situation where developer Sveta creates a database and adds few objects with DEFINER clauses there. We will use only one for this post but in reality, she can have dozens.
1 2 | mysql> CREATE VIEW large_tables AS SELECT * FROM information_schema.tables WHERE DATA_LENGTH > 100000000; Query OK, 0 rows affected (0,01 sec) |
Once you create a view default DEFINER is the user who created this view:
1 2 3 4 5 6 7 | mysql> SELECT DEFINER, TABLE_SCHEMA, TABLE_NAME FROM information_schema.views WHERE TABLE_NAME='large_tables'; +---------+--------------+--------------+ | DEFINER | TABLE_SCHEMA | TABLE_NAME | +---------+--------------+--------------+ | sveta@% | definers | large_tables | +---------+--------------+--------------+ 1 row in set (0,01 sec) |
And this causes issues when another user tries to import such a view into a different server:
1 2 3 4 5 6 7 8 9 10 11 | mysql> CREATE USER production; Query OK, 0 rows affected (0,01 sec) mysql> GRANT ALL ON definers.* TO production@'%'; Query OK, 0 rows affected (0,01 sec) mysql> GRANT SESSION_VARIABLES_ADMIN ON *.* TO production@'%'; Query OK, 0 rows affected (0,01 sec) $ mysqldump -usveta definers | mysql -uproduction production ERROR 1227 (42000) at line 61: Access denied; you need (at least one of) the SUPER or SET_USER_ID privilege(s) for this operation |
Here is the content of line 61:
1 2 3 4 5 6 7 8 9 10 | $ mysqldump -usveta definers | head -n 62 | tail /*!50001 DROP VIEW IF EXISTS `large_tables`*/; /*!50001 SET @saved_cs_client = @@character_set_client */; /*!50001 SET @saved_cs_results = @@character_set_results */; /*!50001 SET @saved_col_connection = @@collation_connection */; /*!50001 SET character_set_client = utf8mb4 */; /*!50001 SET character_set_results = utf8mb4 */; /*!50001 SET collation_connection = utf8mb4_0900_ai_ci */; /*!50001 CREATE ALGORITHM=UNDEFINED */ /*!50013 DEFINER=`sveta`@`%` SQL SECURITY DEFINER */ |
So this is a CREATE VIEW operation that failed during import.
Unfortunately, mysqldump still does not have an option that allows migrating definers.
But since August 2015 and MySQL 5.7.8 we have a solution that, unfortunately, was overlooked in favor of the famous tool mysqldump.
Version 5.7.8 and all which created after it, come with a new dump tool: mysqlpump that has the option --skip-definer and allows to migrate database objects without any issue:
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 | $ mysqlpump -h127.0.0.1 -P3306 -usveta --skip-definer definers | mysql -h127.0.0.1 -P13000 -uproduction definers Dump completed in 17 $ mysql -h127.0.0.1 -P13000 -uproduction definers -e "SHOW FULL TABLES" +--------------------+------------+ | Tables_in_definers | Table_type | +--------------------+------------+ | large_tables | VIEW | +--------------------+------------+ $ mysql -h127.0.0.1 -P13000 -uproduction definers -e "SELECT DEFINER, TABLE_SCHEMA, TABLE_NAME FROM information_schema.views WHERE TABLE_NAME='large_tables';" +--------------+--------------+--------------+ | DEFINER | TABLE_SCHEMA | TABLE_NAME | +--------------+--------------+--------------+ | production@% | definers | large_tables | +--------------+--------------+--------------+ |
Note that mysqlpumpautomatically adds CREATE DATABASE into the dump and full path to the database objects. E.g. CREATE ALGORITHM=UNDEFINED VIEW `definers`.`large_tables` AS select … Therefore this method cannot be used to migrate view, routine, or trigger definitions between different databases on the same server.
For more information about mysqlpump and why you should switch to this tool from mysqldump read this blog post, The mysqlpump Utility.
Percona Distribution for MySQL is the most complete, stable, scalable, and secure, open-source MySQL solution available, delivering enterprise-grade database environments for your most critical business applications… and it’s free to use!
What about migration from user A to user B?
what are caveats of doing this: update mysql.proc set defer = ‘user B’ where defer = ‘user A’;
?
Well, you are not supposed to update privilege tables directly. But regardless, this won’t help you if you want to migrate from Server A where you have user A to server B where you have user B and do not have user A: the dump file would not simply be imported.
Hi Sveta,
thank you for blogging about this problematic with mysqldump. I replied with another post using the same example but with MySQL Shell that must be preferred to mysqlpump. see: https://blogs.oracle.com/mysql/migrate-ownership-of-your-stored-routines%2c-views-and-triggers-for-mysql-in-2021
cheers,
Can it create nice single SQL file that contains all databases, tables, routines and other objects?
No as single file is not compatible with parallel load… however you can create a nice single zip (or tar, thx, …) file containing all that 😉
MySQL Shell is definitely better, but one thing I’m struggling to achieve is how to dump the DDL of all the routines, excluding the DDL of my tables… excludeTables should be able to accept wildcards or at least have the option for “all”
mysqlpump is lacking the skip-lock-tables options to play nice with PXC in strict mode and for some reason, even when excluding the tables I keep getting ALTER TABLE for the indexes and USE schema…