In this blog post, we’ll look at what is needed when migrating MySQL users to Amazon RDS. We’ll discuss how we can transform MySQL user grants and make them compatible with Amazon RDS.
In order to deliver a managed service experience, Amazon RDS does not provide shell access to the underlying operating system. It also restricts access to certain procedures that require advanced privileges.
Every MySQL instance has some users with ALL PRIVILEGES, and you can’t directly migrate these users to Amazon RDS because it does not support following privileges for regular users.
- SUPER – Enable use of other administrative operations such as CHANGE MASTER TO, KILL, PURGE BINARY LOGS, SET GLOBAL, and mysqladmin debug command. Level: Global.
- SHUTDOWN – Enable use of mysqladmin shutdown. Level: Global.
- FILE – Enable the user to cause the server to read or write files. Level: Global.
- CREATE TABLESPACE – Enable tablespaces and log file groups to be created, altered, or dropped. Level: Global.
The RDS parameter groups manage changes to the MySQL configuration (dynamic and non-dynamic variables). Amazon RDS also provides stored procedures to perform various administrative tasks that require SUPER privileges.
For example, we’ve got this user in MySQL instance running on Amazon EC2.
1 2 3 4 5 6 7 | db01 (none)> show grants for percona@'%'; +-----------------------------------------------------------------------------------------------------------------------------------+ | Grants for percona@% | +-----------------------------------------------------------------------------------------------------------------------------------+ | GRANT ALL PRIVILEGES ON *.* TO 'percona'@'%' IDENTIFIED BY PASSWORD '*497030855D20D6B22E65436D0DFC75AA347B32F0' WITH GRANT OPTION | +-----------------------------------------------------------------------------------------------------------------------------------+ 1 row in set (0.00 sec) |
If we try to run the same grants in RDS, it will fail.
1 2 | [RDS] (none)> GRANT ALL PRIVILEGES ON *.* TO 'percona'@'%' IDENTIFIED BY PASSWORD '*497030855D20D6B22E65436D0DFC75AA347B32F0' WITH GRANT OPTION; ERROR 1045 (28000): Access denied for user 'admin'@'%' (using password: YES) |
We’ll follow these steps for migrating users to RDS.
- Identify users with privileges that aren’t supported by RDS.
- Export their grants using pt-show-grants.
- Import grants in a separate clean MySQL instance running the same version.
- Remove the forbidden privileges using the REVOKE statement.
- Export grants again using pt-show-grants and load them to RDS.
Identify users having privileges that aren’t supported by RDS
First, we’ll find the users with privileges that aren’t supported by Amazon RDS. I’ve excluded the localhost users because there is no direct shell access in RDS and you shouldn’t migrate these users.
1 2 3 4 5 6 7 8 9 10 11 | db01 (none)> select concat("'",user,"'@'",host,"'") as 'user', CONCAT("REVOKE SUPER, SHUTDOWN, FILE, CREATE TABLESPACE ON *.* FROM '",user,"'@'",host,"';") as 'query' from mysql.user where host not in ('localhost','127.0.0.1') and (Super_Priv='Y' OR Shutdown_priv='Y' OR File_priv='Y' OR Create_tablespace_priv='Y'); +---------------+----------------------------------------------------------------------------+ | user | query | +---------------+----------------------------------------------------------------------------+ | 'appuser'@'%' | REVOKE SUPER, SHUTDOWN, FILE, CREATE TABLESPACE ON *.* FROM 'appuser'@'%'; | | 'percona'@'%' | REVOKE SUPER, SHUTDOWN, FILE, CREATE TABLESPACE ON *.* FROM 'percona'@'%'; | +---------------+----------------------------------------------------------------------------+ 2 rows in set (0.00 sec) |
We’ve two users with incompatible grants. Let’s transform their grants to make them compatible with RDS. We’ll use the query in second column output later in this process.
Export grants using pt-show-grants
The next step is exporting these two users’ grants using pt-show-grants:
1 2 3 4 5 6 7 8 9 10 | [root@db01 ~]# pt-show-grants --only='appuser'@'%','percona'@'%' -- Grants dumped by pt-show-grants -- Dumped from server Localhost via UNIX socket, MySQL 5.6.38-83.0 at 2018-02-24 10:02:21 -- Grants for 'appuser'@'%' GRANT FILE ON *.* TO 'appuser'@'%' IDENTIFIED BY PASSWORD '*46BDE570B30DFEDC739A339B0AFA17DB62C54213'; GRANT ALTER, CREATE, CREATE TEMPORARY TABLES, DELETE, DROP, EXECUTE, INSERT, LOCK TABLES, SELECT, TRIGGER, UPDATE ON `sakila`.* TO 'appuser'@'%'; -- Grants for 'percona'@'%' GRANT ALL PRIVILEGES ON *.* TO 'percona'@'%' IDENTIFIED BY PASSWORD '*497030855D20D6B22E65436D0DFC75AA347B32F0' WITH GRANT OPTION; |
As we can see from above output, both users have at least one privilege that isn’t supported by RDS. Now, all we need to do is to import these users into a separate clean MySQL instance running the same version, and REVOKE the privileges that aren’t supported by RDS.
Import users in a separate MySQL instance running the same version
I’m going to import grants in a separate VM where I’ve just installed Percona Server for MySQL 5.6. Let’s call this instance as db02:
1 2 | [root@db02 ~]# pt-show-grants --host=db01 --only='appuser'@'%','percona'@'%' --user=percona --ask-pass | mysql Enter password: |
Remove the forbidden privileges using the REVOKE statement
In this step, we will use REVOKE statement from Step 1 to remove the privileges that aren’t supported by Amazon RDS:
1 2 3 4 5 | db02 (none)> REVOKE SUPER, SHUTDOWN, FILE, CREATE TABLESPACE ON *.* FROM 'appuser'@'%'; Query OK, 0 rows affected (0.00 sec) db02 (none)> REVOKE SUPER, SHUTDOWN, FILE, CREATE TABLESPACE ON *.* FROM 'percona'@'%'; Query OK, 0 rows affected (0.00 sec) |
Export grants again using pt-show-grants and load them to RDS
At this point, db02 has the grants that are compatible with RDS. Let’s take a look at them:
1 2 3 4 5 6 7 8 9 10 | [root@db02 ~]# pt-show-grants --only='appuser'@'%','percona'@'%' -- Grants dumped by pt-show-grants -- Dumped from server Localhost via UNIX socket, MySQL 5.6.39-83.1 at 2018-02-24 10:10:38 -- Grants for 'appuser'@'%' GRANT USAGE ON *.* TO 'appuser'@'%' IDENTIFIED BY PASSWORD '*46BDE570B30DFEDC739A339B0AFA17DB62C54213'; GRANT ALTER, CREATE, CREATE TEMPORARY TABLES, DELETE, DROP, EXECUTE, INSERT, LOCK TABLES, SELECT, TRIGGER, UPDATE ON `sakila`.* TO 'appuser'@'%'; -- Grants for 'percona'@'%' GRANT ALTER, ALTER ROUTINE, CREATE, CREATE ROUTINE, CREATE TEMPORARY TABLES, CREATE USER, CREATE VIEW, DELETE, DROP, EVENT, EXECUTE, INDEX, INSERT, LOCK TABLES, PROCESS, REFERENCES, RELOAD, REPLICATION CLIENT, REPLICATION SLAVE, SELECT, SHOW DATABASES, SHOW VIEW, TRIGGER, UPDATE ON *.* TO 'percona'@'%' IDENTIFIED BY PASSWORD '*497030855D20D6B22E65436D0DFC75AA347B32F0' WITH GRANT OPTION; |
These grants look good, these can be safely migrated to RDS now. Let’s do it:
1 2 3 4 5 6 7 8 | [RDS] mysql> GRANT USAGE ON *.* TO 'appuser'@'%' IDENTIFIED BY PASSWORD '*46BDE570B30DFEDC739A339B0AFA17DB62C54213'; Query OK, 0 rows affected (0.32 sec) [RDS] mysql> GRANT ALTER, CREATE, CREATE TEMPORARY TABLES, DELETE, DROP, EXECUTE, INSERT, LOCK TABLES, SELECT, TRIGGER, UPDATE ON `sakila`.* TO 'appuser'@'%'; Query OK, 0 rows affected (0.31 sec) [RDS] mysql> GRANT ALTER, ALTER ROUTINE, CREATE, CREATE ROUTINE, CREATE TEMPORARY TABLES, CREATE USER, CREATE VIEW, DELETE, DROP, EVENT, EXECUTE, INDEX, INSERT, LOCK TABLES, PROCESS, REFERENCES, RELOAD, REPLICATION CLIENT, REPLICATION SLAVE, SELECT, SHOW DATABASES, SHOW VIEW, TRIGGER, UPDATE ON *.* TO 'percona'@'%' IDENTIFIED BY PASSWORD '*497030855D20D6B22E65436D0DFC75AA347B32F0' WITH GRANT OPTION; Query OK, 0 rows affected (0.34 sec) |
We have successfully migrated users to Amazon RDS, which would have failed in direct migration.
What about rest of the users that don’t have SUPER/SHUTDOWN/FILE/CREATE TABLESPACE privileges? Well, it’s easy. We can migrate them directly using pt-show-grants. They don’t need any transformation before migration.
List them using the following query:
1 2 3 4 5 6 7 8 | db01 (none)> select concat("'",user,"'@'",host,"'") as 'user' from mysql.user where host not in ('localhost','127.0.0.1') and (Super_Priv<>'Y' AND Shutdown_priv<>'Y' AND File_priv<>'Y' AND Create_tablespace_priv<>'Y'); +-----------------------+ | user | +-----------------------+ | 'readonly'@'%' | | 'repl'@'192.168.56.5' | +-----------------------+ 2 rows in set (0.01 sec) |
Export them using pt-show grants and load to RDS.
1 2 | [root@db01 ~]# pt-show-grants --only='readonly'@'%','repl'@'192.168.56.5' | mysql --host=<rds.endpoint> --user=percona -p Enter password: |
Conclusion
Amazon RDS is a great platform for hosting your MySQL databases. When migrating MySQL users to Amazon RDS, some grants might fail because of having privileges that aren’t supported by RDS. Using pt-show-grants from Percona Toolkit and a separate clean MySQL instance, we can easily transform grants and migrate MySQL users to Amazon RDS without any hassle.
You state that RDS users cannot have the SUPER, FILE, SHUTDOWN or CREATE TABLESPACE privileges. This is not strictly correct.
It’s completely true that because RDS is a shared service, you may not have an RDS user that has the SUPER privileges upon the MySQL system schema(s), ‘mysql’ in particular, since with SUPER privileges on the system schemas you could potentially impact other RDS users’ service or access/damage their data. However, this in no way prevents you from granting ALL PRIVILEGES to a user that you create on a database that you have also created in your RDS instance. You simply cannot GRANT ALL PRIVILEGES ON *.* or GRANT ALL PRIVILEGES ON mysql.*.