Secure Management and Separation of MySQL User Accounts

I committed the taboo of sharing the MySQL master user account within the team for a certain project, so I created each account with the optimal permissions granted. I was embarrassed to admit that I had never performed the account creation operation before, so I summarized the procedures I researched.

The Need for Account Separation

The master user account should be retained solely for database administration and configuration changes and is not used for regular application operations. The following reasons necessitate account separation:

1. Principle of Least Privilege: The user account for the application should have only the minimum database permissions required by that application. This minimizes the impact if the account is misused. For example, if the application only needs to read and write data, the user account should be granted SELECT, INSERT, UPDATE, and DELETE permissions.
2. Separation: Account separation refers to the concept of dividing user accounts by application or by different functionalities. This approach helps to prevent the impact of unauthorized account use from spreading to other areas.
3. Auditing: By using individual user accounts, you can closely track the behavior of an application. By tracking what each account is doing, problem identification and early detection of wrongdoing become easier.

How to Add an Account

1.Connect to the RDS instance using the MySQL client tool (MySQL Workbench) as the master user: You will need the connection information (endpoint, port number), master username, and master password.

• Endpoint (MySQL Hostname)
• Port number (MySQL Server Port)
• Master username (Username)
• Master password (Password)

2.Create a new user: Use the following SQL command:

CREATE USER 'newuser'@'%' IDENTIFIED BY 'newpassword';

Replace newuser with the new username and newpassword with the new password for that user. Using '%' allows connections from any host. You may replace this with a specific IP address or hostname to restrict connections.

3.Grant permissions to the new user: Use the following SQL command:

GRANT SELECT, INSERT, UPDATE, DELETE ON database_name.* TO 'newuser'@'%';

Replace database_name with the name of the database for which you want to grant permissions. 'SELECT', 'INSERT', 'UPDATE', 'DELETE' are examples of permissions you can grant to the new user. Adjust these permissions as needed. Main permissions include:

1. ALL PRIVILEGES: All permissions.
2. ALTER: Permission to alter the table structure.
3. CREATE: Permission to create databases or tables.
4. DROP: Permission to delete databases or tables.
5. DELETE: Permission to delete records.
6. INSERT: Permission to add records.
7. SELECT: Permission to reference data.
8. UPDATE: Permission to update records.
9. GRANT OPTION: Permission to grant or revoke permissions from other users.
10. PROCESS: Permission to view or manage server processes.
11. RELOAD: Permission to reload server settings.
12. SHUTDOWN: Permission to shut down the server.
13. FILE: Permission to access files at the filesystem level.
14. REFERENCES: Permission to create foreign key constraints.
15. INDEX: Permission to create or delete indexes.
16. ALTER ROUTINE: Permission to modify or delete stored procedures and functions.
17. CREATE ROUTINE: Permission to create stored procedures and functions.
18. EXECUTE: Permission to execute stored procedures and functions.
19. CREATE VIEW: Permission to create views.
20. SHOW VIEW: Permission to display the structure of views.
21. CREATE TEMPORARY TABLES: Permission to create temporary tables.
22. LOCK TABLES: Permission to lock tables.
23. REPLICATION CLIENT: Permission to request information about master/slave replication.
24. REPLICATION SLAVE: Permission to operate as a replication slave.

4.Apply changes: Execute the following SQL command to apply the changes to the database.

FLUSH PRIVILEGES;

Passwords should be securely stored in something like AWS Systems Manager Parameter Store and changed regularly.

How to Change the Account Password

To change the password of the newly created MySQL user, use the following SQL command:

ALTER USER 'username'@'%' IDENTIFIED BY 'newpassword';

Replace 'username' with the name of the user you want to change the password for and 'newpassword' with the new password. The new user can be used to connect from database client tools like MySQL Workbench.

How to Delete an Account

To delete a user, use the following SQL command:

DROP USER 'username'@'%';

Replace 'username' with the name of the user you want to delete. To execute these commands, you must connect to the database with a user that has sufficient privileges (for example, the master user set initially). Also, these operations affect the security of the database, so they must be performed by someone with the appropriate authority and knowledge.

How to Check Account Permissions

To verify the permissions assigned to a user, use the following SQL command:

SHOW GRANTS FOR 'username'@'%';

It will be displayed like this:

How to Check the List of Accounts

To verify the list of created accounts, use the following SQL command:

SELECT User, Host FROM mysql.user;

It will be displayed like this:

Log Auditing

To record database operations in detail in the logs, you must configure the database engine properly. In MySQL, you need to set the general_log system variable to ON to enable the General Query Log.

To check the general_log, execute the following SQL command:

SELECT * FROM mysql.general_log;

Since enabling general_log can impact performance and storage usage, you should turn it ON only when debugging.

SET GLOBAL general_log = 'ON';

To check the slow_log (Slow Query Log), execute the following SQL command:

SELECT * FROM mysql.slow_log;

Note that fine-tuning and monitoring these logs should be carried out carefully to ensure optimal performance and security. Make sure to consult the relevant MySQL documentation or work with a knowledgeable database administrator when making these changes.