How do I create another admin user for my Amazon RDS DB instance that is running MySQL?

Resolution

By default, an RDS DB instance that runs MySQL has one admin account. However, you can create a new user that has all the same permissions as the admin account. To create a new user with these permissions, complete the following steps:

1.    Connect to your RDS MySQL instance.

2.    Run the SHOW GRANTS command to get a list of the permissions that are currently available to the admin account. Then, copy that list of permissions to use later:

mysql> SHOW GRANTS for admin_username;

You see an output that's similar to the following message:

+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Grants for admin@% |
+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| 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 'admin'@'%' WITH GRANT OPTION |
+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+

Note: In this case, the admin account has the user name admin.

3.    Create a new user with the CREATE USER command:

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

Note: Replace new_admin_user and password with your user name and password.

4.    Run the GRANT command to grant the list of permissions that you got in step 2 to the new user:

Note: The following permissions apply to MySQL 5.7. Permissions might change across different major versions of MySQL

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_admin_user'@'%' WITH GRANT OPTION;

The new user now has the same permissions as the admin account.

Related information

Common DBA tasks for MySQL DB instances

How do I allow users to authenticate to an Amazon RDS MySQL DB instance using their IAM credentials?