MySQL REVOKE

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

Introduction to the MySQL REVOKE statement

The REVOKE statement revokes one or more privileges from a user account.

The REVOKE statement has several forms.

Revoke one or more privileges

The following illustrates the basic syntax of the REVOKE statement that revokes one or more privileges from user accounts:

REVOKE privilegee [,privilege]..
ON [object_type] privilege_level
FROM user1 [, user2] ..;Code language: SQL (Structured Query Language) (sql)

In this syntax:

• First, specify a list of comma-separated privileges that you want to revoke from a user account after the REVOKE keyword.
• Second, specify the object type and privilege level of the privileges after the ON keyword; check it out the GRANT statement for more information on the privilege level.
• Third, specify one or more user accounts from which you want to revoke the privileges in the FROM clause.

Note that to execute this form of REVOKE statement, you must have GRANT OPTION privilege or you must have the privileges that you are revoking.

Revoke all privileges

To revoke all privileges from a user, you use the following form of the REVOKE ALL statement:

REVOKE 
    ALL [PRIVILEGES], 
    GRANT OPTION 
FROM user1 [, user2];Code language: SQL (Structured Query Language) (sql)

To execute the REVOKE ALL statement, you must have a global CREATE USER privilege or the UPDATE privilege for the mysql system database.

Revoke Proxy

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

REVOKE PROXY 
ON proxied_user 
FROM proxy_user1[,proxy_user1]...;Code language: SQL (Structured Query Language) (sql)

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

It is a good practice to show the privileges of the user accounts using the SHOW GRANTS statement before you revoke the privileges from the user:

SHOW GRANTS FOR user;Code language: SQL (Structured Query Language) (sql)

MySQL REVOKE examples

Let’s take some examples of revoking privileges.

1) Using MySQL REVOKE to revoke privileges from a user account example

First, create a user account named rfc@localhost:

CREATE USER rfc@localhost 
IDENTIFIED BY 'Secret1Pass!';Code language: SQL (Structured Query Language) (sql)

Second, grant rfc@localhost the SELECT, UPDATE, and INSERT privileges on the classicmodels database:

GRANT SELECT, UPDATE, INSERT 
ON classicmodels.*
TO rfc@localhost;Code language: SQL (Structured Query Language) (sql)

Third, display the granted privileges of the rfc@localhost user:

SHOW GRANTS FOR rfc@localhost;Code language: SQL (Structured Query Language) (sql)

Fourth, revoke the UPDATE and INSERT privileges from rfc@localhost:

REVOKE INSERT, UPDATE
ON classicmodels.*
FROM rfc@localhost;Code language: SQL (Structured Query Language) (sql)

Fifth, display the privileges of rfc@localhost:

SHOW GRANTS FOR rfc@localhost;Code language: SQL (Structured Query Language) (sql)

2) Using MySQL REVOKE to revoke all privileges from a user account example

First, grant the EXECUTE privilege to the rfc@localhost:

GRANT EXECUTE
ON classicmodels.*
TO rfc@localhost;Code language: SQL (Structured Query Language) (sql)

Second, show the previously granted privileges of rfc@localhost user:

Third, revoke all privileges of the rfc@localhost user account by using the REVOKE ALL statement:

REVOKE ALL, GRANT OPTION
FROM rfc@localhost;Code language: SQL (Structured Query Language) (sql)

Finally, show the privileges of the rfc@localhost to verify the revoke:

SHOW GRANTS FOR rfc@localhost;Code language: SQL (Structured Query Language) (sql)

The rfc@localhost has no privileges. Please note that USAGE privilege means no privileges in MySQL.

3) Using MySQL REVOKE to revoke PROXY privilege example

First, grant the PROXY privilege to rfc@localhost user account:

GRANT PROXY 
ON root 
TO rfc@localhost;Code language: SQL (Structured Query Language) (sql)

Second, show the granted privileges of rfc@localhost:

SHOW GRANTS FOR rfc@localhost;Code language: SQL (Structured Query Language) (sql)

Third, revoke the PROXY privilege from the rfc@localhost:

REVOKE PROXY 
ON root 
FROM rfc@localhost;Code language: SQL (Structured Query Language) (sql)

Finally, show the granted privileges of rfc@lcoalhost to verify the action:

SHOW GRANTS FOR rfc@localhost;Code language: SQL (Structured Query Language) (sql)

When the MySQL REVOKE command takes effect

The effect of REVOKE statement depends on the privilege level:

Global level

The changes take effect when the user account connects to the MySQL Server in the subsequent sessions. The changes do not apply to all currently connected users.

Database level

The changes take effect after the next USE statement.

Table and column levels

The changes take effect on all subsequent queries.

Summary

• Use the MySQL REVOKE statement to revoke privileges from user accounts.