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 admin_sync@`%`;<p> 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