rds unable to grant role to a user using root user

user_1548432 0
Viewed 227

I have a mysql rds instance, when you make the instance you declare a root user and a password.

I am then using terraform to create a new user and give the user a role. However i get the following error:

 Error running SQL (GRANT 'test_role' TO 'test_user'@'%'): Error 1227: Access denied; you need (at least one of) the WITH ADMIN, ROLE_ADMIN, SUPER privilege(s) for this operation

Putting terraform aside, if i attempt to assign a role to a user with mysql directly. I get the same error

CREATE ROLE 'test_role';
GRANT SELECT, EXECUTE ON checkpoint_gg.* TO 'test_role';
CREATE USER 'test_user'@'%' IDENTIFIED BY 'password';
GRANT 'test_role' TO 'test_user'@'%';

SHOW GRANTS FOR 'root'@'%';

'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, CREATE ROLE, DROP ROLE ON *.* TO `root`@`%` WITH GRANT OPTION'
'GRANT APPLICATION_PASSWORD_ADMIN,BACKUP_ADMIN,FLUSH_OPTIMIZER_COSTS,FLUSH_STATUS,FLUSH_TABLES,FLUSH_USER_RESOURCES,INNODB_REDO_LOG_ARCHIVE,PASSWORDLESS_USER_ADMIN,SHOW_ROUTINE ON *.* TO `root`@`%` WITH GRANT OPTION'

mysql 8

terraform amazon-rds mysql
2 Answers

I contacted aws technical support and they managed to replicate the issue and suggest a solution.

aws technical support

since RDS is a managed service, to maintain the system integrity and stability, super user privileges are not provided even to the master user of the DB instance, and therefore, such error message is expected, as the RDS MySQL master user by default does not have the ADMIN, ROLE_ADMIN, SUPER privileges.

They suggested, interestingly enough the master/root user can assign those roles to itself.

GRANT ROLE_ADMIN on *.* to root;

Once it has that privilege we can then grant a role to a user

GRANT 'test_role' TO 'test_user'@'%';

I did not know the master root user (not rdsadmin) could give it self admin role, when itself is not an admin or does not have super privileges.

user_15484320

  1. Please try to create rds cluster with master username other than root, it can be reserved username.

  2. As mentioned in the error, your user does not have ADMIN, ROLE_ADMIN or SUPER permissions. Grant one of this permission. Also make sure that you actually use @'%' user, not @'localhost'

user_198113930
Related