How do I create another administration user for my Amazon RDS for MySQL DB instance?

Resolution

By default, Amazon RDS for MySQL has one primary administration account. It's a best practice to create another user with minimal permissions to use with your application. For more information, see Role-based privilege model for RDS for MySQL.

Note: Depending on the version of MySQL that you use, you can also run the GRANT command to attach dynamic permissions to a user.

For MySQL versions 8.0.36 and higher

To use rds_superuser_role to grant role-based permissions to users, complete the following steps:

1. As the primary administration user, run the SHOW GRANTS command to see the list of permissions for the rds_superuser_role role:

   SHOW GRANTS FOR rds_superuser_role@'%';

2. Run the CREATE USER command to create a new administrative user:

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

3. Run the GRANT command to grant the new role to the new user:

   grant rds_superuser_role to 'new_admin_user'@'%';

4. Run the following command to activate the rds_superuser_role for the new user:

   set role all;

5. Run the following query to verify that you granted rds_superuser_role to the new user:

   select current_role();

   Example output:

   +--------------------------+  
   | current_role() |  
   +--------------------------+  
   | `rds_superuser_role`@`%` |  
   +--------------------------+

For MySQL versions lower than 8.0.36

Complete the following steps:

1. Connect to your RDS for MySQL DB instance.

2. Run the following command to get a list of the permissions that are currently available to the administrative account:

   SHOW GRANTS for admin_username;

   Note: Replace admin_username with your username.

3. Copy your output of the list of permissions to use in step 5.
   Example output:

   +--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+| 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 |  
   +--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+

4. Run the following command to create a new user:

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

   Note: Replace new_admin_user and password with your username and password.

5. Run the following GRANT command and include the list of permissions that you got in step 2 to attach to the new 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 'new_admin_user'@'%' WITH GRANT OPTION;

6. Use the new user to log in.

7. Run the following query to verify that you granted the permissions to the new administrative user:

   SHOW GRANTS for admin_username;

   Note: Replace admin_username with your username.

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 IAM credentials?