AWS RDS : Setting Create and other grants only for single Database is not working 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'@'%'
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