Show TOC

Background documentationREVOKE Statement (revoke_statement)

 

The REVOKE statement (revoke_statement) revokes privileges.

Structure

Syntax Syntax

  1. <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>,...
    [<cascade_option>]

<grantee> ::=
  <user_name>
| <usergroup_name>
| <role_name>
| PUBLIC
End of the code.
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.

<cascade_option>

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

Example Example

U1, U2 and U3 are database 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 database user U2, the privilege set (P'*P'') is revoked implicitly from database user U3.

End of the example.

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 grant the user identified by grantee authorization to create database objects in the specified schema. Database objects that the database user specified in grantee has already created in the specified schema are not revoked.

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

REVOKE EXECUTE ON

REVOKE EXECUTE revokes the authorization to execute the database procedure or database function from the database 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

REVOKE SELECT revokes the authorization use the specified sequence from the database user identified by grantee. This REVOKE statement can also cascade.

More Information

Privileges: Overview