AWS RDS MySQL User Creation

When you create a AWS RDS MySQL instance, you will be asked to also create a master username/password.  The master RDS user acts like the root user of all it's databases, with full access to SQL commands such as DROP and CREATE.  While you can use this root user within your applications, you should create an application specific database user with limited privileges, which will prevent harm from accidental and malicious use.

Note: AWS (Amazon Web Services) RDS (Relational Database Service) is a managed service, and it doesn't provide SYS access (SUPER privileges)

View permissions of root user

Show grants for RDS MySQL root user ie master username

SQL: 
SHOW GRANTS FOR 'professor';

Result:

GRANT SELECT, INSERT, UPDATE, DELETE, CREATE, DROP, RELOAD, PROCESS, REFERENCES, INDEX, ALTER, SHOW DATABASES, CREATE TEMPORARY TABLES, LOCK TABLES, EXECUTE, REPLICATION SLAVE, REPLICATION CLIENT, CREATE VIEW, SHOW VIEW, CREATE ROUTINE, ALTER ROUTINE, CREATE USER, EVENT, TRIGGER ON *.* TO `professor`@`%` WITH GRANT

Create new application user

Create a new user to be used by just the application

SQL:
CREATE USER 'express_app'@'%' IDENTIFIED BY '20charRandomPwd';

Note: The username should be related to the application, and the password does not need to be readable as it will only be used within the application configuration.

Grant permissions

For application users, remove:

CREATE, DROP, RELOAD, PROCESS, REFERENCES, INDEX, ALTER, SHOW DATABASES, CREATE TEMPORARY TABLES, LOCK TABLES, CREATE VIEW, SHOW VIEW, CREATE ROUTINE, ALTER ROUTINE, CREATE USER, EVENT

Leaving the basics:

SELECT, INSERT, UPDATE, DELETE, EXECUTE, REPLICATION SLAVE, REPLICATION CLIENT, TRIGGER

Note: MySQL permissions reference

SQL:
GRANT SELECT, INSERT, UPDATE, DELETE, EXECUTE, REPLICATION SLAVE, REPLICATION CLIENT, TRIGGER ON *.* TO `express_app`@`%` WITH GRANT OPTION;

Note: If using multiple databases in a RDS instance, then grant access only to the required databases ie ON `planet.*` TO `express_app` 

You can now use the newly created, and restricted, database user express_app in your application.

-End of Document-

Thanks for reading