MySQL User Management & Security – Accounts, Privileges, and Best Practices

In any secure database system, proper user management and privilege control form the foundation of data protection. MySQL allows administrators to define fine-grained access policies, authenticate users, and assign only the required permissions—enhancing security and minimizing risk.

Introduction – Why Manage Users and Secure MySQL?

User management in MySQL helps protect sensitive data, enforce proper access control, and mitigate the risk of unauthorized or accidental data manipulation.

In this guide, you’ll learn:

• How to create, modify, and delete MySQL users
• How to assign and revoke privileges
• How to use roles (MySQL 8+)
• Password policies and best security practices

Topics Covered

MySQL User Accounts – What They Are

A user in MySQL is uniquely defined by a combination of username and host:

'username'@'host'

• 'username': The login name
• 'host': The machine/IP the user connects from

View All Users:

SELECT user, host FROM mysql.user;

Creating Users

Syntax

CREATE USER 'username'@'host' IDENTIFIED BY 'password';

Example:

CREATE USER 'dev_user'@'localhost' IDENTIFIED BY 'StrongP@ss123';

Restricts access to local connections with a secure password.

Granting Privileges

Syntax

GRANT privileges ON database.table TO 'username'@'host';

Example:

GRANT SELECT, INSERT ON company.* TO 'dev_user'@'localhost';

Grants the user read and insert access to all tables in company.

Grant All Privileges (Admin Role)

GRANT ALL PRIVILEGES ON *.* TO 'admin_user'@'%' WITH GRANT OPTION;

Use with caution. Grants full access and permission to delegate rights.

Revoking Privileges

Syntax

REVOKE privileges ON database.table FROM 'username'@'host';

Example:

REVOKE INSERT ON company.* FROM 'dev_user'@'localhost';

Removes INSERT access on the company database.

Modifying & Dropping Users

Change Password:

ALTER USER 'dev_user'@'localhost' IDENTIFIED BY 'NewP@ss456';

Drop User:

DROP USER 'dev_user'@'localhost';

Role Management (MySQL 8+)

Roles help organize permissions and simplify account management.

Create Role:

CREATE ROLE 'readonly';

Grant Privileges to Role:

GRANT SELECT ON finance.* TO 'readonly';

Assign Role to User:

GRANT 'readonly' TO 'report_user'@'%';
SET DEFAULT ROLE 'readonly' TO 'report_user'@'%';

Viewing Current User & Permissions

Who Am I?

SELECT CURRENT_USER();

Show Grants:

SHOW GRANTS FOR 'dev_user'@'localhost';

Common Privileges in MySQL

Security Best Practices

Real-World Use Cases

Summary – Recap & Next Steps

MySQL user management ensures your database remains secure, controlled, and auditable. By combining users, privileges, roles, and best practices, you can enforce strict access control without limiting productivity.

Key Takeaways

• Use CREATE USER, GRANT, REVOKE, and DROP USER for account lifecycle
• Grant only the privileges required for the task
• MySQL 8+ roles simplify permission assignments
• Always follow security best practices and monitor access regularly

Real-World Relevance

These practices are essential in production environments, shared servers, SaaS platforms, and data-driven applications where access control, compliance, and safety are critical.

FAQ – MySQL User & Security

Can I create a user that can only SELECT?

Yes:

GRANT SELECT ON mydb.* TO 'readonly'@'localhost';

Can I restrict a user to one table only?

Yes:

GRANT SELECT ON mydb.customers TO 'limited_user'@'localhost';

What is WITH GRANT OPTION?

Allows the user to pass their privileges to others. Use with caution.

How can I disable remote root access?

UPDATE mysql.user SET host = 'localhost' WHERE user = 'root';
FLUSH PRIVILEGES;

How do I view all privileges granted to a user?

SHOW GRANTS FOR 'username'@'host';