Summary: in this tutorial, you will learn how to use MySQL REVOKE statement to revoke privileges from MySQL accounts.

We are highly recommend that you follow the tutorials below to have a better understanding of how MySQL REVOKE works:

• Getting Started with MySQL Access Control System
• MySQL Create User
• How to Use MySQL GRANT to Grant Privileges to Account

MySQL REVOKE Syntax

In order to revoke privileges from an account, you use the MySQL REVOKE statement. The syntax of MySQL REVOKE statement is as follows:

REVOKE   privilege_type [(column_list)]      [, priv_type [(column_list)]]...
ON [object_type] privilege_level
FROM user [, user]...

Let’s examine the MySQL REVOKE statement in more detail.

• You specify a list of privileges that you want to revoke from an account right after the REVOKE keyword. You need to separate privileges by comma.
• ON clause specifies the privilege level at that privileges are to be revoked.
• After FROM keyword, you specify the account that you want to revoke the privileges. You can specify multiple accounts in the FROM clause. You separate the accounts by comma.

In order to revoke privileges from an account, you must have GRANT OPTION privilege and privileges that you are revoking. To revoke all privileges, you use the following MySQL REVOKE syntax:

REVOKE ALL PRIVILEGES, GRANT OPTION FROM user [, user]…

To execute the above command, you must have the global CREATE USER privilege or the UPDATE privilege for the mysql database.

To revoke proxy user, you use the REVOKE PROXY command as follows:

REVOKE PROXY ON user FROM user [, user]...

A proxy user is a valid user in MySQL who can impersonate as another user therefore the proxy user has all privileges of the user that it impersonates.

Before revoking privileges of a user, it is good practice to check if the user has the privileges by using the SHOW GRANTS statement as follows:

SHOW GRANTS FOR user;

MySQL REVOKE examples

Suppose rfc account has privileges SELECT, UPDATE and DELETE in the classicmodels sample database . If you want to revoke UPDATE and DELETE privileges from the rfc  account, you can do so as follows:

First, you check the privileges of rfc account using SHOW GRANTS statement:

SHOW GRANTS FOR 'rfc'@'localhost';

GRANT SELECT, UPDATE, DELETE ON 'classicmodels'.* TO 'rfc'@'localhost'

If you have not followed the previous tutorial on granting privileges to user, you can first grant the SELECT, UPDATE and DELETE privileges for rfc account that connects from localhost to the classicmodels database as follows:

GRANT SELECT, UPDATE, DELETE ON  classicmodels.* TO 'rfc'@'localhost';

Second, you can revoke the UPDATE and DELETE privileges from the rfc account:

REVOKE UPDATE, DELETE ON classicmodels.*  FROM 'rfc'@'localhost';

Third, you can check the privileges of the rfc account again using the SHOW GRANTS command.

SHOW GRANTS FOR 'rfc'@'localhost';

GRANT SELECT ON 'classicmodels'.* TO 'rfc'@'localhost'

If you want to revoke all privileges of the rfc account, you run the following command:

REVOKE ALL PRIVILEGES, GRANT OPTION FROM 'rfc'@'localhost';

If you check the privileges of the rfc account again, you will see the rfc account has no privilege.

SHOW GRANTS FOR 'rfc'@'localhost';

GRANT USAGE ON *.* TO 'rfc'@'localhost'

Note that USAGE privilege means no privileges in MySQL.

When MySQL REVOKE takes effect

The effect of MySQL REVOKE statement depends on the privilege level as follows:

• Changes that are made to the global privileges only take effect when the client connects to the MySQL in the subsequent sessions. The changes are not applied to all current connected accounts.
• The change of database privileges is applied after the next USE statement.
• Table and column privilege’s changes are applied to all queries that are issued after the changes are made.

In this tutorial, you’ve learned how to use MySQL REVOKE statement to revoke privileges from MySQL accounts.