REVOKE command in SQL is used to remove one or more privileges assigned to a database user. The basic syntax for REVOKE command is given below.
REVOKE priv_type [(col_list)] [, priv_type [(col_list)]]…
ON [obj_typ] priv_level
FROM user [, user_name]
The list of privileges that need to be revoked is mentioned right after the REVOKE statement. Each privilege is separated by a comma. Inside the ON clause, the privilege levels and object type are specified. In the FROM keyword, the user account that requires privilege revoking is mentioned. Multiple user accounts mentioned inside FROM clause must be separated by commas.
GRANT OPTION privilege is necessary to use REVOKE command in MySQL. The following MySQL REVOKE syntax can be used to revoke all the privileges given to the users.
REVOKE ALL PRIVILEGES, GRANT OPTION FROM user [, user]…
A global CREATE USER privilege or UPDATE privilege is necessary to use the SQL syntax given above.
Syntax for Revoking Proxy User Privileges
REVOKE PROXY ON user FROM user [, user]
In MySQL, proxy user is also a valid user who has privileges to impersonate other users. The proxy user will have all the privileges given to the impersonated user. The SHOW GRANTS statement can be used to list the privileges provided to a user.
Example for MySQL REVOKE
Let us consider that a user account named “Harry” has privileges to SELECT, DELETE and UPDATE in a database named “company” database. If the privileges like UPDATE and DELETE needs to be revoked; it can be done by following the procedures given below.
Check for the privileges allowed to our user account. It can be done by using the SHOW GRANTS option and the MySQL code is given below.
SHOW GRANTS FOR ‘Harry’@’localhost’;
Revoke the UPDATE and DELETE privileges by using the MySQL statement given below
REVOKE UPDATE, DELETE ON ‘company’ .* FROM ‘Harry’@’localhost’;
SHOW GRANTS statement can be used to check for the privileges given to the user “Harry”. The following MySQL code can be used to check for privileges.
SHOW GRANTS FOR ‘Harry’@’localhost’;
All the the privileges given to the user “Harry” can be revoked by using the following SQL code
REVOKE ALL PRIVILEGES, GRANT OPTION FROM ‘Harry’@’localhost’;
If you check for privileges after revoking everything then the resultant output will be
GRANT USAGE ON *.* TO ‘Harry’@’localhost’
USAGE statement means there are no privileges for the user mentioned above.
Effects of MySQL REVOKE statement
- Changes made in global privileges will be taken effect after the client’s connection to MySQL.
- After the USE statement, the change in database privileges are applied.
- The new privileges will be applied to all the queries.
It is highly challenging for a user to REVOKE certain privileges from unwanted users while retaining the privileges of legitimate users. So GRANT OPTION and CASCADE clauses are used to check the standard of the SQL user and also maintains the product documentation.