REVOKE Statement (revoke_statement)

The REVOKE statement (revoke_statement) revokes privileges.

Syntax

<revoke_statement> ::= REVOKE <priv_spec>,... FROM <grantee>,... [<cascade_option>]
| REVOKE <
schema_priv_spec> FROM <grantee>,...
| REVOKE EXECUTE ON <
dbproc_name> FROM <grantee>,...
| REVOKE EXECUTE ON <
dbfunction_name> FROM <grantee>,...
| REVOKE SELECT ON <
sequence_name> FROM <grantee>,... [<cascade_option>]

<grantee> ::= <user_name> | <usergroup_name> | <role_name> | PUBLIC

Examples

SQL Tutorial, Database Users and Their Privileges, Roles

Explanation

The owner of a table or schema can revoke the privileges granted for this table or schema for any database user.

If a database user is not the owner of the table or schema, he may only revoke the privileges he has granted.

If the SELECT privilege was granted for a table without specifying any column names, REVOKE SELECT (<column_name>,...)can be used to revoke the SELECT privilege for the specified columns. The SELECT privilege for table columns that have not been specified remains unchanged. The same is true for the UPDATE, REFERENCES, and SELUPD privileges.

<grantee>

The owner of an object for which privileges are granted is not allowed to be contained in the grantee list. A database user in the grantee list must not denote a member of a user group.

PUBLIC

The listed privileges are revoked for all database users, both for current ones and for any created later.

The REVOKE statement can cascade, that is, revoking a privilege from one user can result in this privilege being revoked from other users who have received the privilege from the user in question.

Let U1, U2, and U3 be users.
U1 grants U2 the privilege set P WITH GRANT OPTION.
U1 grants U3 the privilege set P' (P'<=P).

If U1 revokes the privilege set P'' (P''<=P) from user U2, the privilege set (P'*P'') is revoked implicitly from user U3.

?     Whenever the SELECT privilege is revoked from the owner of a view table for a column that does not occur in the table_expression of the view definition (CREATE VIEW statement) as a selected column, the column defined by select_column is dropped from the view table.
If this view table is used in the FROM clause of another view table, the described procedure is applied recursively to this view table.

?     If the SELECT privilege is revoked from the owner of a view table for a column or table occurring in the table_expression of the view definition, the view table is dropped, along with all view tables, privileges, and synonyms that are dependent on this view table, if no CASCADE option or the cascade option CASCADE is specified. The REVOKE statement will fail if the CASCADE option RESTRICT is specified.

REVOKE <schema_priv_spec>

REVOKE CREATEIN ON is used to revoke from the user identified by grantee authorization to create database objects in the specified schema. Database objects that the user specified in grantee has already created in the specified schema are not revoked.

REVOKE DROPIN ON is used to revoke from the user identified by grantee authorization to drop database objects from the specified schema.

REVOKE EXECUTE ON

If REVOKE EXECUTE is specified, the authorization to execute the database procedure or database function is revoked from the user identified by grantee. The authorization for execution can only be revoked by the owner of the database procedure or database function.

REVOKE SELECT ON

If REVOKE SELECT is specified, the authorization to use the specified sequence is revoked from the user identified by grantee. This REVOKE statement can also cascade.

See also:

Privileges: Overview