SQL in a Nutshell, 3rd Edition

Name

REVOKE Statement

The REVOKE statement takes two main forms. The first form of the statement removes specific statement permissions from a user, group, or role. The second form of the statement removes access permissions to specific database objects or resources.

Platform	Command
MySQL	Supported, with variations
Oracle	Supported, with variations
PostgreSQL	Supported, with variations
SQL Server	Supported, with variations

SQL2003 Syntax

The SQL2003 syntax for REVOKE takes this general form:

REVOKE { [special_options] | {privilege[, ...] | role[, ...]} }
ON database_object_name
FROM grantee_name[, ...]
[GRANTED BY {CURRENT_USER | CURRENT_ROLE}]
{CASCADE | RESTRICT}

Keywords

special_options

Allows the use of one of three optional special_options:

GRANT OPTION FOR

Undoes the WITH GRANT OPTION privilege assigned to a user, meaning that the user can no longer grant privileges to other users on the object. That user’s own privileges on the object remain intact. (This clause is valid to revoke a privilege, but not to revoke a role.)

HIERARCHY OPTION FOR

Undoes the WITH HIERARCHY OPTION privilege that allows a user to SELECT not only from the named table, but also all of its subtables. (This clause is valid to revoke a privilege, but not to revoke a role.)

ADMIN OPTION FOR

Undoes the ability to grant a role to other users. (This clause is valid to revoke a privilege, but not to revoke a role.)

privilege

Revokes privileges for a variety of statements, which may all be combined in any order:

ALL PRIVILEGES

Revokes ...