How to Revoke MySQL User Privileges

Securing a database environment requires careful management of user permissions. This article provides a clear, step-by-step guide on how to revoke access rights from an existing MySQL user. You will learn how to log in to your MySQL server, view a user’s current privileges, strip specific or all permissions using the REVOKE statement, and apply the changes to ensure your database remains secure.

Step 1: Log in to MySQL as an Administrator

To modify user privileges, you must log in to the MySQL server with an account that has administrative capabilities, such as the root user. Run the following command in your terminal:

mysql -u root -p

Enter your administrative password when prompted to access the MySQL command-line interface.

Step 2: Identify the User and Their Current Privileges

Before revoking access, it is best practice to verify the exact username, host, and existing privileges of the user. Run the SHOW GRANTS command:

SHOW GRANTS FOR 'username'@'hostname';

Replace 'username' with the MySQL user and 'hostname' with the host they connect from (e.g., 'localhost' or '%' for any host).

Step 3: Revoke Access Rights

Depending on your security requirements, you can revoke specific privileges or strip all access rights entirely.

Option A: Revoke Specific Privileges

If you want to remove only certain permissions (such as INSERT or DELETE) on a specific database, use the following syntax:

REVOKE INSERT, DELETE ON database_name.* FROM 'username'@'hostname';

Option B: Revoke All Privileges

To strip every database privilege from the user while keeping the user account active, use the ALL PRIVILEGES and GRANT OPTION keywords:

REVOKE ALL PRIVILEGES, GRANT OPTION FROM 'username'@'hostname';

Step 4: Apply the Changes

To ensure that MySQL immediately registers the privilege updates, flush the privileges from the memory cache:

FLUSH PRIVILEGES;

Step 5: Verify the Revocation

Confirm that the privileges have been successfully removed by checking the user’s grants once more:

SHOW GRANTS FOR 'username'@'hostname';

The output should now reflect the reduced permissions or show only the default usage grant (USAGE), which allows the user to log in but perform no operations.