MySQL RDS - Associating a user to a group fails with "Error Code: 1227. Access denied"

0

I am connected to MySQL RDS instance of our database with super admin privileges. When I execute the following statements below it results in error

Error Code: 1227. Access denied; you need (at least one of) the WITH ADMIN, ROLE_ADMIN, SUPER privilege(s) for this operation 0.047 sec

CREATE ROLE read_only_user; GRANT SELECT on * TO read_only_user; grant 'read_only_user' TO 'myuser@company.com';

We are in the process of migrating to Aurora and the same stamens works fine in an Auora instance of the database.

vlaiosa
asked 3 months ago217 views
1 Answer
0

Take a look at this post: https://repost.aws/knowledge-center/duplicate-master-user-mysql

Error Code 1227 - Access Denied: This error suggests that your current user account does not have sufficient privileges to perform the operation you are attempting.

Verify Your Privileges: First, ensure that your user account truly has super admin privileges. You can check this by executing the following SQL command: SHOW GRANTS FOR 'yourusername'@'yourhost';

answered 3 months ago
  • My user does have super admin privileges (see below). Also, I have an instance of a MySQL Aurora database with the same super admin privileges and it works fine.

    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 admin@% 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 admin@% WITH GRANT OPTION

You are not logged in. Log in to post an answer.

A good answer clearly answers the question and provides constructive feedback and encourages professional growth in the question asker.

Guidelines for Answering Questions