9

Migrating Ownership of Your Stored Routines, Views, and Triggers in MySQL

 3 years ago
source link: https://www.percona.com/blog/migrating-ownership-of-your-stored-routines-views-and-triggers-in-mysql/
Go to the source link to view the article. You can view the picture content, updated content and better typesetting reading experience. If the link is broken, please click the button below to view the snapshot at that time.
neoserver,ios ssh client

Migrating Ownership MySQL“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.

MySQL
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:

MySQL
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:

MySQL
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:

MySQL
$ 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:

Shell
$ 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.


report

Recommend

About Joyk


Aggregate valuable and interesting links.
Joyk means Joy of geeK