SQL REVOKE

The REVOKE command is used to take away database privileges from a user. It is the opposite of the GRANT command.

It is worth noting that some lightweight databases, such as SQLite, do not implement REVOKE (and GRANT) commands. This is because such databases are intended to be an embedded database engine and hence user-level privileges are meaningless.

Syntax

The syntax for REVOKE is as follows:

The available "Privilege_Name" will vary depending on the relational database. Typical privileges include the following:

• SELECT
• INSERT
• DELETE
• UPDATE

In many databases there is a privilege called "ALL." To remove all privileges from a user, it is tempting to use the "ALL" privilege type. However because "ALL" doesn't always cover all the privileges (it is database-dependent), it is best to first check what privileges that user has and revoke each of them. Such information is typically stored in a system table (each database has its own set of system tables), so please consult your database documentation for this information.

The links below show the types of privileges available for each of the databases:

• Oracle
• SQL Server
• MySQL
• PostgreSQL
• DB2

Examples

Example 1: Revoke one privilege from a user

To remove the SELECT privilege on the "Sales" table from user tanner1, we type in the following:

Example 2: Revoke one privilege from all users

To remove the DELETE privilege on the "Sales" table from all users, we use the following:

Note that some databases such as MySQL have a different way to identify all users. So please consult with your database documentation if public does not work.