Can't create a new user with all privileges on AWS RDS MySQL instance

Question

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?

Solution 1

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!

Solution 2

When using RDS (Managed DB service) you simply can't create a root user as you're trying to do.

Simply use

CREATE USER 'username'@'%' IDENTIFIED BY 'userpassword';
GRANT SELECT ON [your_database].[some_table] TO 'username'@'%';

Comments

[Michael - sqlbot]

@chevallier if you really want to grant all available privileges on *.* you'll need to run SHOW GRANTS; as the admin user, then copy that list of explicit grants to build the command to make the grant to the new user. You can't grant what you don't have, yourself, and RDS does not give you ALL PRIVILEGES.