Archive for the ·

aws

· Category...

resolving MySQL ‘Access denied for user’ error for root on AWS RDS

no comments

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;
(note the substitution of the mysql `%` 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.

AWS authorize security groups across acounts

no comments

I recently needed to authorize one Amazon EC2 instance to access another EC2 instance’s solr server.  The instances, however, were owned by separate AWS accounts.  The solution was easy enough, but hidden in the documentation, rather than in the first results of my search.  Credit goes to Tyler Harms for spotting it.

In the AWS security group section of the administration console, just add the AWS account which owns the security group as a prefix.  So, security group ‘sg-blahblah’ becomes ‘1234567/sg-blahblah’.  It is the same format to do it from the command line tools for AWS.  Voilà:

AWS Security Groups across accounts

AWS Security Groups across accounts