I want another user with the same permissions as the master user for my Amzon RDS DB instance running MySQL. How do I duplicate or clone the master user?

Although a DB instance running MySQL can only have one master user, it's possible to create a new user that has all the same permissions as the master user. To do this, perform the following actions:

1.    Begin by getting a list of the permissions currently available to the master user by running the SHOW GRANTS command:

mysql> SHOW GRANTS FOR 'master_user';

The command provides output similar to the following (note: "master_user" is the master user's username):

+----------------------------+
| Grants for master_user@%
+----------------------------+
| GRANT SELECT, INSERT, UPDATE, DELETE, CREATE, DROP, RELOAD, PROCESS, REFERENCES, INDEX, ALTER, SHOW DATABASES, CREATE TEMPORARY TABLES, LOCK TABLES, EXECUTE, REPLICATION SLAVE, REPLICATION CLIENT, CREATE VIEW, SHOW VIEW, CREATE ROUTINE, ALTER ROUTINE, CREATE USER, EVENT, TRIGGER ON *.* TO 'master_user'@'%' IDENTIFIED BY PASSWORD '' WITH GRANT OPTION |

Copy this list of permissions to use later.

2.    Create a new user by using the CREATE USER command (note: replace "new_master_user" and "password" with your preferred username and password):

mysql> CREATE USER 'new_master_user'@'%' IDENTIFIED BY 'password';

3.    Grant the list of permissions you got in step 1 to the new user by using the GRANT command:

mysql> GRANT SELECT, INSERT, UPDATE, DELETE, CREATE, DROP, RELOAD, PROCESS, REFERENCES, INDEX, ALTER, SHOW DATABASES, CREATE TEMPORARY TABLES, LOCK TABLES, EXECUTE, REPLICATION SLAVE, REPLICATION CLIENT, CREATE VIEW, SHOW VIEW, CREATE ROUTINE, ALTER ROUTINE, CREATE USER, EVENT, TRIGGER ON *.* TO 'new_master_user'@'%' WITH GRANT OPTION;

The new user will now have the same permissions as the master user.


Did this page help you? Yes | No

Back to the AWS Support Knowledge Center

Need help? Visit the AWS Support Center

Published: 2017-02-06