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

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.

 

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