Managing Users in MySQL

Managing users in MySQL is essential for securing your database and controlling access to data. MySQL provides tools to create users, assign privileges, modify credentials, and audit access.

Creating a New User

A user in MySQL is identified by a combination of username and host, and secured with a password. This step is foundational for role-based access control in multi-user environments.

Syntax:

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

Example:

CREATE USER 'developer'@'localhost' IDENTIFIED BY 'devpass123';
CREATE USER 'remote_user'@'%' IDENTIFIED BY 'securepass';

Listing All Users

MySQL accounts are stored in the mysql.user system table. This query returns a list of usernames and their associated host permissions.

Syntax:

SELECT User, Host FROM mysql.user;

Assigning Privileges – GRANT

Privileges define what operations a user can perform, such as reading, modifying, or managing schema objects. This step enforces least-privilege access and protects sensitive data.

Syntax:

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

Examples:

GRANT ALL PRIVILEGES ON sales_db.* TO 'developer'@'localhost';
GRANT SELECT ON sales_db.* TO 'analyst'@'localhost';
FLUSH PRIVILEGES;

Revoking Privileges – REVOKE

Revoking privileges removes previously granted permissions. This is useful when roles change or access needs to be restricted.

Syntax:

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

Example:

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

Viewing User Privileges

To check what privileges a user has in MySQL, use the SHOW GRANTS statement.

Syntax:

SHOW GRANTS FOR 'username'@'host';

Example:

SHOW GRANTS FOR 'developer'@'localhost';

Modifying a User’s Password

Changing passwords is essential for account security, especially when credentials are compromised or need periodic rotation.

Syntax:

ALTER USER 'username'@'host' IDENTIFIED BY 'new_password';

Example:

ALTER USER 'developer'@'localhost' IDENTIFIED BY 'newpass456';

Dropping a User

Dropping a user permanently deletes their access. Once dropped, the user cannot log in or perform any operations unless recreated.

Syntax:

DROP USER 'username'@'host';

Example:

DROP USER 'testuser'@'localhost';