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

in Product
 • Updated on 

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

Many of our FlyData 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"

After some digging, we came across this great blog post that described the exact issue in detail. 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.

Worry-free replication from source to Redshift & Snowflake
Unlimited sync during trial
No credit-card required
World class support
Try FlyData for free
Amazon Partner Logo Certified AWS
Redshift partner
Get started. Try FlyData.
Quick setup. No credit card required. Unlimited sync during trial.
Fast and secure Enterprise-grade security and near real-time sync.
World-class support Proactive monitoring from technical experts, 24/7.
Straightforward pricing Pay for the rows you use, and nothing you don’t.