Access Denied Issue When Running GRANT ALL ON *.* on Amazon RDS

The "Access Denied" Issue on "GRANT ALL ON *.*" on Amazon RDS

Many of our Integrate.io Sync customers use either their on-premise MySQL instances or Amazon RDS. Recently, we noticed some of our customers having issues when using GRANT on their MySQL instance. We dove into the issue and noticed that the issue happened just for Amazon RDS users. Here is the GRANT statement that was causing issues:

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

The issue occurs when using the *.* to provide the GRANT to the user. With Amazon RDS, since it is AWS's own cloud product (and to protect the instance itself), the master account does not have access to the mysql database. Thus, setting *.* does not make sense within RDS. To workaround the issue, we will use this little tidbit from the MySQL docs: The _ and % wildcards are permitted when specifying database names in GRANT statements that grant privileges at the global or database levels.

Solution

Now, that we know we can use % as the wildcard at the database level, let’s try this:

mysql> GRANT ALL ON '%'.* TO 'admin_sync'@'%';
Query OK, 0 rows affected (0.00 sec)

Success! This works on Amazon RDS (even on regular MySQL instances as well; the user just won't have access to the mysql database -- which is probably a good thing). Hope this is helpful to anyone who is having these issues on Amazon RDS.