» MySQL Adminsitration » Revoking Privileges from Users by Using MySQL REVOKE

Revoking Privileges from Users by Using MySQL REVOKE

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

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

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 detail.
  • You list a list of privileges you want to revoke from an account after the REVOKE keyword. Each privilege is separated by a 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 list multiple users in FROM clause. Each user is separated by a comma.
In order to revoke privileges from an account, you must have GRANT OPTION privilege and privileges you are revoking.To revoke all privileges you use the following MySQL REVOKE Syntax:
REVOKE ALL PRIVILEGES, GRANT OPTION FROM user [, user]…
 Note that to use the above syntax; 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 syntax as below:
REVOKE PROXY ON user FROM user [, user]...
A quick remind that a proxy user is a valid user in MySQL who can impersonate as another user therefore has all privileges of that user.Before revoking privileges of a user, it is better to check if the user has that privilege by using SHOW GRANT S statement as follows:
SHOW GRANTS FOR user;

MySQL REVOKE examples

Suppose account rfc has privileges SELECT, UPDATE and DELETE on our sample database classicmodels. If you want to revoke UPDATE and DELETE privilege from rfc you can do as follows:First we check the privileges of rfc by using SHOW GRANTS statement:
SHOW GRANTS FOR 'rfc'@'localhost';


GRANT SELECT, UPDATE, DELETE ON 'classicmodels'.* TO 'rfc'@'localhost'
If you do not followthe previous tutorial on granting privileges to user, you can first grant the privileges SELECT, UPDATE and DELETE for rfc connecting from localhost to the database classicmodels as follows:
GRANT SELECT, UPDATE, DELETE ON  classicmodels.* TO 'rfc'@'localhost';
 Second, we can revoke the UPDATE and DELETE privileges from user rfc as follows:
REVOKE UPDATE, DELETE ON classicmodels.*  FROM 'rfc'@'localhost';
Finally we can check the privileges of user rfc again by using SHOW GRANTS command.
SHOW GRANTS FOR 'rfc'@'localhost';


GRANT SELECT ON 'classicmodels'.* TO 'rfc'@'localhost'
 If you want to revoke all privileges of user rfc you can do it as follows:
REVOKE ALL PRIVILEGES, GRANT OPTION FROM 'rfc'@'localhost';
If you check the privileges of rfc again by using SHOW GRANTS statement, you will see the user rfc 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 are made to the global privileges only effect next time client connect to the database server. All current connected clients do not affect by the changes.
  • Database privilege’s change applies after the next USE statement.
  • Table and column privilege’s changes apply to all queries issued after the changes are made.

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

Tags: MySQL REVOKE, MySQL REVOKE syntax, MySQL revoke all privileges.

Search