We are using AWS RDS Mysql, and we are trying to give SELECT access to all databases i.e RO only, but for a single database, we want to give all permissions i.e CRUD. It's not working out unfortunately with Create, user is not able to create a table.
user data-team-user
Commands I tried :
GRANT SELECT ON *.* TO 'data-team-user'@'%';
GRANT ALL PRIVILEGES ON `api-gateway-copy`.* TO 'data-team-user';
GRANT CREATE, SELECT,INSERT,UPDATE,DELETE ON `api-gateway-copy` TO 'data-team-user'@'%';
GRANT CREATE,SELECT,INSERT,UPDATE,DELETE ON *.* TO 'data-team-user'@'%';
SHOW GRANTS FOR 'data-team-user';
GRANT SELECT, INSERT, UPDATE, DELETE, CREATE, DROP, INDEX, ALTER, CREATE TEMPORARY TABLES, LOCK TABLES, EXECUTE, CREATE VIEW, SHOW VIEW, CREATE ROUTINE, ALTER ROUTINE, REFERENCES, TRIGGER ON `api-gateway-copy`.* TO 'data-team-user';
flush privileges;
GRANT CREATE ON `api-gateway-copy`.* TO 'data-team-user'@'%';
Still :
SHOW GRANTS FOR 'data-team-user';
+--------------------------------------------------------------------------------------+
| Grants for data-team-user@% |
+--------------------------------------------------------------------------------------+
| GRANT USAGE ON *.* TO 'data-team-user'@'%' |
| GRANT SELECT ON `tmp`.* TO 'data-team-user'@'%' |
| GRANT SELECT ON `sys`.* TO 'data-team-user'@'%' |
| GRANT SELECT ON ``.* TO 'data-team-user'@'%' |
| GRANT SELECT ON ``.* TO 'data-team-user'@'%' |
| GRANT SELECT ON ``.* TO 'data-team-user'@'%' |
| GRANT SELECT ON ``.* TO 'data-team-user'@'%' |
| GRANT SELECT ON ``.* TO 'data-team-user'@'%' |
| GRANT SELECT ON ``.* TO 'data-team-user'@'%' |
| GRANT SELECT ON ``.* TO 'data-team-user'@'%' |
| GRANT SELECT ON ``.* TO 'data-team-user'@'%' |
| GRANT SELECT ON ``.* TO 'data-team-user'@'%' |
| GRANT SELECT ON ``.* TO 'data-team-user'@'%' |
| GRANT SELECT ON ``.* TO 'data-team-user'@'%' |
| GRANT SELECT ON ``.* TO 'data-team-user'@'%' |
| GRANT SELECT, INSERT, UPDATE, DELETE ON `api-gateway-copy`.* TO 'data-team-user'@'%' |
| GRANT SELECT ON ``.* TO 'data-team-user'@'%' |
| GRANT SELECT ON ``.* TO 'data-team-user'@'%'
Best Answer
RDS only allows you to grant privileges on the schemas you create. System level schemas are blocked as the “master” user only has certain privileges with the
with grant
option.That being said the usual statements of
GRANT ALL PRIVILEGES
and*.*
don’t work. You have to grant privileges explicitly by schema to a user and those grants can only be at most the same ones you have as the “master” user.This article is for creating another “master” user and you can use it as a reference to model the permissions you are trying to setup by removing the privileges you don’t want the user to have.
Additional Master User in AWS RDS