Is there a way I can grant roles to user in AWS RDS rather directly granting privileges to users?

This error is due to granting privileges you don't have i.e. command having (xxx *.*). In order to protect the instance itself, RDS doesn’t allow even the master account to access to the mysql database. The mysql.* tables are considered off-limits since you don’t have access to the mysql.* tables which are restricted by Amazon.  

So, the quick fix is to use %.* instead of *.*.  i.e. GRANT myPrivileges ON `%`.* TO 'myUser'@`%`;
The _ and % wildcards are permitted when specifying DB names in GRANT statements that grant privileges at the global or database levels.
https://devopslife.io/access-denied-when-using-grant-all-on-in-aws-rds-mysql/

Hi @btan - Ok, got it, not sure, using % is the standard way of granting access but it's the same case with `%`.*

mysql> 
mysql> create role 'ReadRole';
Query OK, 0 rows affected (0.22 sec)

mysql> grant select on `%`.* to 'ReadRole';
Query OK, 0 rows affected (0.21 sec)

mysql> create user 'user1'@'%' identified by 'secret';
Query OK, 0 rows affected (0.21 sec)

mysql> 
mysql> 
mysql> grant 'ReadRole' to 'user1'@'%';
ERROR 1227 (42000): Access denied; you need (at least one of) the WITH ADMIN, ROLE_ADMIN, SUPER privilege(s) for this operation
mysql>

Select allOpen in new window

Can't use the role created to grant to users!.

At least from the link shared it worked based on >> GRANT ALL ON `%`.* TO admin_sync@`%`;
Would this even is something you want to try if works without error  (switching to user1 from admin_sync

It only works if you directly grant privileges to user, but what I'm trying is to grant roles to user. Is it working for you if you grant the role to user in rds?

The % will exclude system table hence avoiding touching all tables. You may want to consider more specific table instead such as schema2.*
In any case, you can still use GRANT SELECT ON instead of GRANT ALL

I don't have such instance to test. If the outcome is the same probably  the closet  syntax to go about troubleshooting.

mysql> 
mysql> create database mydb;
Query OK, 1 row affected (0.21 sec)

mysql> create role 'ReadRole';
Query OK, 0 rows affected (0.21 sec)

mysql> grant select on mydb.* to 'ReadRole';
Query OK, 0 rows affected (0.22 sec)

mysql> create user 'user1'@'%' identified by 'secret';
Query OK, 0 rows affected (0.21 sec)

mysql> grant 'ReadRole' to 'user1'@'%';
ERROR 1227 (42000): Access denied; you need (at least one of) the WITH ADMIN, ROLE_ADMIN, SUPER privilege(s) for this operation
mysql> 

Select allOpen in new window

I presume this will have same error too  >> grant select on mydb.* to 'user1'@'%';
Anyway, there is a relevant article on this https://forums.aws.amazon.com/thread.jspa?threadID=339775

though it is trying to create another master user as user1 does not have the privileged, it maybe worth a try than trim further. I see the difference from existing is the "." and "WITH GRANT OPTION" (and of course the list of permission)
https://aws.amazon.com/premiumsupport/knowledge-center/duplicate-master-user-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 ' user_X’@'%' WITH GRANT OPTION;

 

I already have this master user with grant option created, and from which I'm granting a role to the user. I don't want to waste time trying any further. I will directly grant privileges to the user rather granting the role. Please ignore this question.

@Tomas - Got it. It seems to be working after granting ROLE_ADMIN to the admin user.
How about the following if have to grant all privileges to the user?

mysql> create role 'AdminRole';
Query OK, 0 rows affected (0.38 sec)

mysql> grant all privileges on *.* to 'AdminRole';
ERROR 1045 (28000): Access denied for user 'admin'@'%' (using password: YES)

Select allOpen in new window

Using `%`.* in place of *.* works, but it looks like a tweak, is there any better way to grant all privileges to a role?

"%" is not a tweak but an escape char.
Recall my past post  "...d % wildcards are permitted when specifying DB names in GRANT statements that grant privileges at the global or database levels."