How to Revoke Privileges for Users in MySQL

Often database administrators need to revoke user privileges in MySQL, to ensure there is no authorized database access or operations. In this article, we will learn how to revoke privileges for users in MySQL.

How to Revoke Privileges for Users in MySQL

You can revoke all or some of the privileges using REVOKE statement in MySQL. You can use revoke statement to revoke privileges to database, functions or stored procedures.

1. Revoke All Privileges

Here is an example to revoke all privileges in MySQL.

REVOKE ALL PRIVILEGES ON *.* FROM '<user_name>'@'localhost';
REVOKE ALL PRIVILEGES ON *.* FROM '<user_name>'@'%';

The first statement revokes all privileges to local database user while the second one revokes privileges on user accessing from any machine. Here are a couple of examples of the same.

REVOKE ALL PRIVILEGES ON *.* FROM 'test_user'@'localhost';
REVOKE ALL PRIVILEGES ON *.* FROM 'test_user'@'%';

The above commands will remove all privileges of specified users. If you want to remove privileges to specific database, then replace *.* in above commands with database name, for which you want to remove privileges. Here are the example commands to revoke all privileges on database mydb for test_user.

REVOKE ALL PRIVILEGES ON mydb.* FROM 'test_user'@'localhost';
REVOKE ALL PRIVILEGES ON mydb.* FROM 'test_user'@'%';

Similarly, you can also remove privileges on a given function by adding keyword FUNCTION after ON followed by function name. Here is an example to revoke user access to function calc_num.

REVOKE ALL PRIVILEGES ON FUNCTION calc_num FROM 'test_user'@'localhost';
REVOKE ALL PRIVILEGES ON FUNCTIOn calc_num FROM 'test_user'@'%';

2. Revoke Specific Privileges

Instead of revoking all privileges, you can also revoke specific permissions such as SELECT, DELETE, UPDATE for specific user, as per your requirement.

Here is an example to remove SELECT & UPDATE privileges for user.

REVOKE SELECT, UPDATE PRIVILEGES ON *.* FROM 'test_user'@'localhost';
REVOKE SELECT, UPDATE PRIVILEGES ON *.* FROM 'test_user'@'%';

The above commands will revoke SELECT, UPDATE privileges on all databases for given users. If you want to remove these privileges for specific database (e.g. mydb) then replace *.* with the database name.

REVOKE SELECT, UPDATE PRIVILEGES ON mydb.* FROM 'test_user'@'localhost';
REVOKE SELECT, UPDATE PRIVILEGES ON mydb.* FROM 'test_user'@'%';

In this article, we have learnt how to revoke permissions for MySQL users. You can customize them as per your requirement.

How to Revoke Privileges for Users in MySQL