Function

For the specified tables or views. REVOKE removes privileges from a user, a list of users or from PUBLIC.

 

Syntax



 

table_specification	A table or view for which the revocation is to be performed. Specify the table or view name only once. See Table Specification.
privilege_specification	A list of one or more privileges to be revoked. See Privilege Specification.
grantee_specification	A user, a list of users, or PUBLIC, for which the revocation is to be executed. Specify the  user only once. See Grantee Specification.

Description

REVOKE revokes the specified privileges from a user, a list of users or from PUBLIC for the specified table or view. Do not specify the user identifier or the table or view identifier more than once.

Unsuccessful execution of the REVOKE ALL PRIVILEGES statement results in response code 0, even though the ANSI Standard prescribes a Warning.

For details about what privileges are possible, what they mean and the constraints on them, see Privilege Specification.

LIMITATIONS

General rules:

• If a revoke from PUBLIC is specified then only those privileges that have been granted to PUBLIC will be revoked.

• You can not revoke privileges from yourself.

• The keyword RESTRICT only affects the current user plus constraints.

• For the privilege UPDATE, a revocation of the table privilege causes an implicit revocation of all column privileges for the specified table. If only the column privilege is revoked, an existing table privilege remains unaltered.

• REVOKE CASCADE is not supported yet. If the revokee has granted the privilege to a third grantee, the privilege cannot be revoked from the revokee unless he has revoked it from the third grantee. Trying to revoke these privileges will fail and result in an error condition.

Authority to revoke privileges:

• The revoker is the owner of the table or view.

• The revoker gave the privileges that are to be revoked.

Revoking privileges from Views:

• Revoking privileges from a base table which would affect any view that relies upon that table will fail and result in an error conditions. To revoke these privileges, the view must be dropped first.

• The execution of the REVOKE statement is an atomic action that is closed by an implicit COMMIT and can, therefore, not be rolled back.

Caution: This statement is not subject to transaction logic. An implicit COMMIT will be performed after successful execution of this statement. If an error is detected during execution of this statement, an implicit ROLLBACK will be performed. Therefore, before executing this statement, it is strongly recommended to complete any open transaction containing INSERT, UPDATE and/or DELETE statements by issuing an explicit COMMIT or ROLLBACK statement.

 

ANSI Specifics

You must specify either CASCADE or RESTRICT.

CONNX Specifics

• It is optional (and has no effect) to specify the keyword TABLE in ON TABLE table specification.

• If neither CASCADE nor RESTRICT is specified, then RESTRICT is the default action.

• The CASCADE functionality is not yet implemented.

Examples

Simple revocation:

Tim has given Peter ALL privileges on table CRUISE. Tim then decides to revoke the DELETE privilege from Peter.

 

REVOKE DELETE ON CRUISE FROM 'PETER';
REVOKE DELETE ON CRUISE FROM 'PETER' RESTRICT; [ANSI-specific method]

 

This has the effect of removing the DELETE privilege from Peter, but will still leave him with the SELECT, INSERT and UPDATE privileges for this table.

Simple revocation (no cascading):

Tim has given Peter ALL privileges including the "WITH GRANT OPTION" on table CRUISE. Peter then gives Anne the privileges to SELECT and DELETE on table CRUISE. Tim then decides to revoke the DELETE privilege from Peter.

Tim:

 

REVOKE DELETE ON CRUISE FROM 'PETER';

 

Tim:

 

REVOKE DELETE ON CRUISE FROM 'PETER' RESTRICT; [ANSI-specific method]

 

This will fail and result in the error message that there are still dependent privileges. First, Peter has to revoke the privileges SELECT and DELETE from Anne:

 

Peter:

 

REVOKE SELECT, DELETE ON CRUISE FROM 'ANNE';

 

Peter:

 

REVOKE SELECT, DELETE ON CRUISE FROM 'ANNE' RESTRICT;[ANSI-specific method]

 

After that, Tim can revoke the DELETE privilege from Peter.

Assume that Tim has also given Peter the UPDATE table privilege. Now he wants to revoke the UPDATE privilege on column xx from Peter.

 

Tim:

 

REVOKE UPDATE ( XX ) ON CRUISE FROM 'PETER';

 

The result will be that the UPDATE table privilege still exists: only the column privilege for column xx is destroyed. If Peter then tries to grant the UPDATE privilege to Gary, this will have the effect that Gary also gets UPDATE column privileges for all columns of table CRUISE with the exception of column xx. That means Gary is allowed to update all columns in CRUISE except xx.