MySQL User Management - Complete Administration Tutorial

Creating MySQL Users

To create a new user in MySQL, you use the CREATE USER statement. This command defines the username, host, and authentication method.

Specifying the host limits where the user can connect from, enhancing security.

• Syntax: CREATE USER 'username'@'host' IDENTIFIED BY 'password';
• Example: CREATE USER 'alice'@'localhost' IDENTIFIED BY 'StrongPass123';
• Use strong, unique passwords for each user.

Assigning Privileges

After creating a user, you assign privileges to control what actions they can perform on the database.

Privileges can be global, database-specific, or table-specific.

• Use the GRANT statement to assign privileges.
• Example: GRANT SELECT, INSERT ON mydb.* TO 'alice'@'localhost';
• Always follow the principle of least privilege.

Modifying and Revoking User Privileges

You can modify user privileges using the GRANT and REVOKE commands as needed.

Revoking unnecessary privileges helps maintain security.

• Syntax to revoke: REVOKE privilege ON database.table FROM 'username'@'host';
• Example: REVOKE INSERT ON mydb.* FROM 'alice'@'localhost';
• Use SHOW GRANTS to review current privileges.

Best Practices for User Management

Adhering to best practices ensures your MySQL user management is secure and efficient.

• Create users with minimal required privileges.
• Use strong, unique passwords and change them regularly.
• Limit user access by host where possible.
• Regularly audit user privileges and remove unused accounts.
• Avoid using root for routine database operations.