Summary: in this tutorial, you will learn how to use MySQL REVOKE statement to revoke privileges from MySQL accounts.
We 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
- How to create a MySQL user
- How to grant privileges to a MySQL user
Introduction to the MySQL REVOKE Statement
In order to revoke privileges from a user account, you use the MySQL
REVOKE statement. MySQL allows you to revoke one or more privileges or all privileges from a user.
The following illustrates the syntax of revoking specific privileges from a user:
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.
- First, specify a list of privileges that you want to revoke from a user right after the
REVOKEkeyword. You need to separate privileges by commas.
- Second, specify the privilege level at which privileges is revoked in the
- Third, specify the user account that you want to revoke the privileges in the
Note that to revoke privileges from a user account, you must have
GRANT OPTION privilege and the privileges that you are revoking.
To revoke all privileges from a user, you use the following form of the REVOKE statement:
REVOKE ALL PRIVILEGES, GRANT OPTION FROM user [, user]…
To execute the
REVOKE ALL statement , 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 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
First, you check the privileges of user using
SHOW GRANTS statement:
SHOW GRANTS FOR rfc;
GRANT SELECT, UPDATE, DELETE ON 'classicmodels'.* TO 'rfc'@'%'
Note that did follow the granting privileges to user tutorial, you can create rfc account and grant the
DELETE privileges to it as follows:
CREATE USER IF EXISTS rfc IDENTIFIED BY 'dolphin';
GRANT SELECT, UPDATE, DELETE ON classicmodels.* TO rfc;
Second, you can revoke the
DELETE privileges from the
REVOKE UPDATE, DELETE ON classicmodels.* FROM rfc;
Third, you can check the privileges of the
rfc user again using the
SHOW GRANTS command.
SHOW GRANTS FOR 'rfc'@'localhost';
GRANT SELECT ON 'classicmodels'.* TO 'rfc'@'%'
If you want to revoke all privileges of the
rfc user, you execute the following command:
REVOKE ALL PRIVILEGES, GRANT OPTION FROM rfc;
If you check the privileges of the
rfc user again, you will see the
rfc user has no privilege.
SHOW GRANTS FOR rfc;
GRANT USAGE ON *.* TO 'rfc'@'%'
USAGE privilege means no privileges in MySQL.
When the MySQL REVOKE command takes effect
The effect of MySQL REVOKE statement depends on the privilege level as follows:
- The 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 currently connected users.
- The changes of the database privileges are applied after the next
- The changes of table and column privilege are applied to all queries issued after the changes were made.
In this tutorial, you’ve learned how to use the MySQL REVOKE statement to revoke privileges from MySQL users.