SQL in a Nutshell, 2nd Edition

Name

REVOKE Statement

Synopsis

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
DB2	Supported, with variations
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 but their own privileges on the object are still intact. (This clause is valid to revoke a privilege, but not to revoke a role.) Refer to the GRANT Statement section for more details.

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 ...