What is the SQL REVOKE statement? How does it work?

In SQL, the REVOKE statement is used to take back/remove (or “revoke”) one or more privileges/permissions from a particular database user. Here’s what the syntax for the REVOKE statement looks like:

Syntax for the REVOKE statement

REVOKE privilege [, privilege ... ]  /* can add more privileges here too */
  [ON database object ] /*object can be tables, views, etc..  */
  FROM grantee [, grantee ...] ; /*user, PUBLIC, or role*/

Let’s go over some things that you should know if you plan on using the REVOKE statement.

SQL REVOKE privileges

When revoking privileges, the list of privileges can only be either all system privileges or all object privileges. In other words, you can not revoke both system and object privileges in the same SQL REVOKE statement – you will need to use two different SQL REVOKE statements in order to do this. You can read about the differences between system and object privileges here: System and object privileges.

SQL REVOKE ON

The ON clause is only used to revoke object privileges – not system privileges. This clause specifies which object privileges (as in which table privileges, view privileges, etc..) are being revoked.

The SQL REVOKE GRANTEE list

It should be clear from the syntax of the REVOKE statement shown above that you can specify more than one database user or role that should receive the privilege(s) being revoked. In this context, GRANTEE’s refer to the users from whom the privileges are being revoked.

SQL REVOKE Example

Here’s an example of what an actual SQL REVOKE statement would look like:

REVOKE SELECT ON SOME_TABLE TO SOME_USER;

In the example above, the REVOKE statement is used to revoke the privilege of being able to select from SOME_TABLE from the SOME_USER user.