Sometimes, I need another admin-level user for my MySQL databases. In fact, I usually like to replace the ‘root’ user with another full-permissions user, for slightly improved security.
Usually, I’d start this off like so:
GRANT ALL PRIVILEGES ON *.* TO newuser@'%' IDENTIFIED BY 'somethingsecure' WITH GRANT OPTION;
I was surprised to find that running this command with the root user on Amazon’s RDS instance of MySQL fails with the following message: ERROR 1045 (28000): Access denied for user ‘root’@‘%’ (using password: YES)
I was pretty miffed about this. As
root, I should be able to grant whatever I want! After banging on the keyboard in frustration for a while, I tried this slight variation, out of sheer blind desperation:
GRANT ALL PRIVILEGES ON `%`.* TO newuser@'%' IDENTIFIED BY 'somethingsecure' WITH GRANT OPTION;
%wildcard for the globbing star ‘*’.
Voila! It worked!
Curious, I found this blog post about it. It looks like the RDS user is restricted by default, without the SUPER privilege. Because of this, root cannot grant privileges on the system tables. MySQL does allow the use of ‘%’ or “_” as wildcards for the database, which will allow GRANT on all of the user-created databases and tables. In my case, that was good enough, but if you need SUPER, there is still an abstruse way to get it on RDS.