Managing Database Users and Permissions in MySQL

How to create MySQL users, grant and revoke permissions, and remove accounts when they are no longer needed.

Why user management matters

Every interaction with a MySQL database happens as a user. Each user has permissions that define what they can and cannot do. Getting this right keeps unauthorised access out and gives people only the access they need for their job.

Creating users in MySQL

Use CREATE USER with a username and authentication details:

CREATE USER 'new_user'@'localhost' IDENTIFIED BY 'password';

That creates new_user with the password password, allowed to connect only from the local machine. Replace localhost with a specific host for remote access, or use % to allow connections from anywhere.

Granting permissions

Once a user exists, grant them the permissions they need. MySQL lets you be quite specific.

Basic permissions

Granting read and write access to a database:

GRANT SELECT, INSERT, UPDATE ON my_database.* TO 'new_user'@'localhost';

That lets new_user select, insert, and update records in all tables of my_database.

Advanced permissions

MySQL also supports finer control:

• Column-level permissions: restrict access to specific columns.

  GRANT SELECT(name, email) ON my_database.customers TO 'new_user'@'localhost';

• Procedure and function permissions: grant rights to run specific stored procedures and functions.

  GRANT EXECUTE ON PROCEDURE my_database.my_procedure TO 'new_user'@'localhost';

Revoking permissions

Remove permissions with REVOKE:

REVOKE UPDATE ON my_database.* FROM 'new_user'@'localhost';

That removes new_user‘s ability to update records in my_database.

Deleting users

To remove a user entirely, use DROP USER:

DROP USER 'new_user'@'localhost';

That removes new_user from MySQL completely.

Best practices

1. Least privilege: grant the minimum permissions needed for the role.
2. Review access regularly: check that permissions still match what people actually need.
3. Use strong passwords: enforce or encourage complex passwords.
4. Monitor activity: keep an eye on what high-privilege users are doing.
5. Update promptly: remove or change access as soon as someone’s role changes or they leave.

Grant only what is needed, review it periodically, and revoke access the moment it is no longer required.