MySQL REVOKE – Revoke privileges from the user

In this tutorial, we will learn what is a REVOKE statement and how to use it for revoking the privilege(s) from the user accounts of the MySQL database. So, let’s get started!

Also read: MySQL GRANT Privileges

Introduction to MySQL REVOKE

The REVOKE statement is used to revoke single or multiple privileges from the user account.

Apart from the privileges, we can revoke the roles from the user account or other roles, and proxies from the user or a role.

Also, we can revoke the privileges from multiple user accounts using a single statement.

Syntax of MySQL REVOKE

There are many forms of the REVOKE statement. Let’s see them one by one with the syntaxes.

Revoke One or More Privileges

The following syntax is used to revoke one or more privileges from the user account(s).

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

Here, you can specify multiple privileges to revoke from the user accounts using comma-separated values.

The object_type specifies whether it is a table, function or procedure. On the other hand, the privilege_level specifies one of the following- *, *.*, db_name.*, db_name.table_name, table_name, db_name.routine_name.

You can also specify multiple usernames that you want to revoke the privilege from.

Revoke All Privileges

The syntax of revoking all privileges is pretty straightforward.

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

Here, you just need to write the ALL to revoke all privileges from the user account.

The GRANT OPTION statement enables you to grant to or revoke from other users those privileges that you yourself possess.

MySQL REVOKE Examples

Before proceeding to examples, let’s create a user account and grant it some privileges such as CREATE, INSERT, UPDATE AND DELETE.

CREATE USER jack@localhost IDENTIFIED BY 'sparrow';Code language: SQL (Structured Query Language) (sql)

We have created a user called ‘jack’ and assigned the password to it.

Let’s now grant some privileges to it.

GRANT CREATE,INSERT,UPDATE,DELETE
ON journaldev.* TO jack@localhost;Code language: SQL (Structured Query Language) (sql)

Let’s display the privileges of the user ‘jack’-

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

Revoke some privileges from the user

Here, we will revoke the insert and update privileges of the user ‘jack’ using the REVOKE statement.

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

As you can see, the INSERT and UPDATE privileges have been removed for the user ‘jack’.

Revoke all privileges from the user

Now we will remove all privileges of the user ‘jack’. Note that, ‘all’ represents every privilege the user has on all the database tables present in the DBMS.

Therefore, you don’t need to mention the privilege_level while writing a query for REVOKE.

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

As you can see, all privileges of the user ‘jack’ have been revoked.

When the REVOKE command in MySQL affect

The REVOKE statement affects depending on the privilege level:

At the global level

When the user account connects to the MySQL Server in subsequent sessions, the modifications take effect. The modifications do not affect all users who are presently connected.

At the database level

After the following USE statement, the modifications take effect.

At the table and column Levels 

All subsequent queries will be affected by the modifications.

Conclusion

In this tutorial, we have learned how to revoke privileges from the user account in MySQL DBMS. If you want to get more information about MySQL roles, click here. Also, we have created a precise guide on MySQL user creation, display, and delete.

Reference

MySQL official documentation on the REVOKE statement.