Mysql – Can’t create a new user with all privileges on AWS RDS MySQL instance

amazon-rdsamazon-web-servicesMySQL

I've initiated a AWS RDS MySQL instance and would like to create an additional user, who has all privileges on all databases;

grant all privileges on *.* to "someuser"@"10.0.0.0/255.255.0.0";
ERROR 1045 (28000): Access denied for user 'root'@'%' (using password: YES)

As you can see, I'm not allowed to do so, even though I'm triggering the command as root.

How can I solve this?

It is not allowed in RDS to grant all permissions to *.*, but you can use a trick:

GRANT ALL PRIVILEGES ON `%`.* TO 'someuser'@XXX;

Also, don't forget: ALL [PRIVILEGES] gives all permissions except GRANT OPTION and PROXY!

It uses a reverse DNS lookup. It takes the IP address of the client and uses whatever PTR record is returned for that name.

In my opinion doing authentication based on the name is not very useful at all, I suggest you consider using IP addresses instead.

See this document about how Mysql uses DNS.

There appears to be no root user in your mysql database. The debian-sys-maint user is the root equivalent. Here is how to add a root user:

mysql -u debian-sys-maint -p

When prompted for a password, enter the one located in /etc/mysql/debian.cnf once inside the database, you should be able to run:

grant all privileges on *.* to 'root'@'localhost' identified by 'yourpassword' with grant option