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 that has 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 the preceding message, the admin account has the user name admin.

3. To create a new user, run the following CREATE USER command:

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

   Note: Enter your user name and password in place of new_admin_user and password.

4. To grant the list of permissions that you got in step 2 to the new user, run the GRANT command:
   Note: The following permissions apply to MySQL 5.7:

   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;

   Note: Permissions might change across different major versions of MySQL

   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 for MySQL DB instance through their Amazon IAM credentials?