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!
% and * in this context?
Commented
Nov 20, 2023 at 17:50
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'@'%';
grant all privileges on *.* ..., as it creating new databases and managing a lot, so it's cumbersome to grant on each one.
Commented
Apr 3, 2019 at 13:47
*.* 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.
Commented
Apr 3, 2019 at 17:18
I had to use two GRANT queries to maximize permissions for a new user on RDS MySQL.
As others have mentioned, this query grants full permissions to all databases except certain system databases (Note the `%` instead of *):
GRANT ALL PRIVILEGES ON `%`.* TO 'myuser'@'host';
For my needs, I also needed as many permissions as possible on the systems tables (so this user could, for example, create new users). For that, I found I could specify all permissions EXCEPT CREATE TABLESPACE, FILE, SHUTDOWN, and SUPER globally:
GRANT EXECUTE, PROCESS, SELECT, SHOW DATABASES, SHOW VIEW, ALTER, ALTER ROUTINE, CREATE, CREATE ROUTINE,
DELETE, CREATE VIEW, INDEX, EVENT, DROP, TRIGGER, REFERENCES, INSERT, CREATE USER,
UPDATE, RELOAD, LOCK TABLES, REPLICATION SLAVE, REPLICATION CLIENT, CREATE TEMPORARY TABLES ON *.* TO 'myuser'@'host' WITH GRANT OPTION;
I imagine the exact list of allowed permissions may change with new versions of MySQL and over time, so hopefully this answer doesn't age too quickly.
Edit: Credit where credit is due - most of this I figured out thanks to this blog post on the issue. I found a slightly different list of disallowed permissions: http://www.fidian.com/problems-only-tyler-has/using-grant-all-with-amazons-mysql-rds
Since you are essentially trying to create a new root user you can instead give your app the credentials of the existing master user that was created when setting up the RDS. Not that it's a good practice but should solve your problem.
"someuser"@"10.0.0.%"