I was totally unaware about the fact that even a master account doesn’t have all the privileges in an RDS database(MySQL) until I got stuck with this issue. Today, I was asked to create a secondary admin user in one of our production DB with all privileges. The MySQL DB instance was running in AWS RDS. I tried the following command

mysql> GRANT ALL ON *.* TO 'admin_sync'@'%';
ERROR 1045 (28000): Access denied for user 'admin'@'%' (using password: YES)

I got the above error while trying to grant all privileges. I was sure about the command because the same command was working fine for non-RDS mysql instances. Few minutes of googling has given me the fix.

 mysql> GRANT ALL ON `%`.* TO [email protected]`%`;

Query OK, 0 rows affected (0.00 sec)

 

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 here since I don’t have access to the mysql.* tables which are restricted by Amazon.  I can’t grant permissions on *.* since that would match MySQL, and `%`.* appears to not match those system tables.

So, the quick fix is to use `%`.* instead of *.*. 

The _ and % wildcards are permitted when specifying DB names in GRANT statements that grant privileges at the global or database levels.

 

References

https://dev.mysql.com/doc/refman/8.0/en/grant.html

http://www.fidian.com/problems-only-tyler-has/using-grant-all-with-amazons-mysql-rds