Introduction

Managing users and securing your MySQL server is essential to control access, protect data, and prevent unauthorized operations. MySQL provides powerful tools to handle users, assign roles, and enforce fine-grained access control using privileges.

1. Creating a New User:- MySQL allows you to create multiple users with different privileges for better access control.

Syntax:

CREATE USER ‘username’@’host’ IDENTIFIED BY ‘password’;

Example:

CREATE USER ‘developer’@’localhost’ IDENTIFIED BY ‘devpass123’;

• ‘localhost’ restricts login from the local machine only.
• You can use ‘%’ to allow access from any host.

2. Listing All Users:- To see the list of users in your MySQL system:

Syntax:-

SELECT User, Host FROM mysql.user;

3. Assigning Privileges – GRANT:- Privileges control what a user is allowed to do, like SELECT, INSERT, UPDATE, etc.

Syntax:

GRANT privilege_list ON database.table TO ‘username’@’host’;

Examples:

• Give full access to one database:

GRANT ALL PRIVILEGES ON sales_db.* TO ‘developer’@’localhost’;

• Read-only access:

GRANT SELECT ON sales_db.* TO ‘analyst’@’localhost’;

• Apply the changes:

FLUSH PRIVILEGES;

4. Revoking Privileges – REVOKE:- You can remove previously granted permissions using REVOKE.

Syntax:

REVOKE privilege_list ON database.table FROM ‘username’@’host’;

Example:

REVOKE INSERT, UPDATE ON sales_db.* FROM ‘analyst’@’localhost’;

5. Viewing User Privileges:- To check what privileges a user has:

Syntax:

SHOW GRANTS FOR ‘username’@’host’;

Example:

SHOW GRANTS FOR ‘developer’@’localhost’;

6. Modifying a User’s Password:- You can reset or change a password at any time.

Syntax:

ALTER USER ‘username’@’host’ IDENTIFIED BY ‘new_password’;

Example:

ALTER USER ‘developer’@’localhost’ IDENTIFIED BY ‘newpass456’;

7. Dropping a User:- To delete a user account completely:

Syntax:

DROP USER ‘username’@’host’;

Example:

DROP USER ‘testuser’@’localhost’;

8. Securing MySQL Server:- Security goes beyond just passwords. Here are steps you can take:
9. Run Secure Installation Wizard:

sudo mysql_secure_installation

This helps you:

• Set root password
• Remove anonymous users
• Disable remote root login
• Remove test databases

1. Disable Remote Root Login: Only allow root to connect from localhost, not from remote machines.
2. Limit Host Access: Use ‘username’@’localhost’ instead of ‘username’@’%’ wherever possible.
3. Use Strong Passwords: Avoid weak or common passwords. Use tools to generate complex ones.
4. Use SSL/TLS: Encrypt client-server communication using SSL certificates to prevent data interception.

9. User Roles 

MySQL 8.0+ supports roles, which are groups of privileges that can be assigned to users.

1. Create and Assign a Role:

CREATE ROLE ‘read_only’;

GRANT SELECT ON *.* TO ‘read_only’;

GRANT ‘read_only’ TO ‘report_user’@’localhost’;

1. Activate the role:

SET DEFAULT ROLE ‘read_only’ TO ‘report_user’@’localhost’;

10. Important Security Tips

• Never use the root account for application access.
• Avoid using % in user hostnames unless absolutely required.
• Regularly rotate passwords and review privileges.
• Always use LIMIT and WHERE clauses in update/delete operations to prevent accidental data loss.

Keep MySQL server updated to avoid known vulnerabilities.