Revoking Authorization

Description

You can revoke privileges using the REVOKE statement. The privileges granted to a user can be revoked anytime. If more than one privilege are granted to a user, all or part of the privileges can be revoked. In addition, if privileges on multiple tables are granted to more than one user using one GRANT statement, the privileges can be selectively revoked for specific users and tables.

If the privilege (WITH GRANT OPTION) is revoked from the grantor, the privilege granted to the grantee by that grantor is also revoked.

Syntax

REVOKE operation [ { , operation }_ ] ON table_name [ { , class_name }_ ]

FROM user [ { , user }_ ] [ ; ]

• operation : Indicates an operation that can be used when granting privileges. (See Syntax in Granting Privileges for details)
• table_name : Specifies the name of the table or virtual table to be granted.
• user : Specifies the name of the user (group) to be granted.

Example 1

The following is an example in which SELECT, INSERT, UPDATE and DELETE privileges for the nation and athlete tables are granted to Fred and John.

GRANT SELECT, INSERT, UPDATE, DELETE ON nation, athlete TO Fred, John;

Example 2

The following is an example in which the REVOKE statement is used to allow John only the SELECT privilege while maintaining all the privileges for Fred granted in Example 1. If John granted the privileges to another user, the grantee is also allowed to use the SELECT privilege only.

REVOKE INSERT, UPDATE, DELETE ON nation, athlete FROM John;

Example 3

The following is an example in which the REVOKE statement is used to revoke all privileges granted to Fred in Example 1. If the statement is executed, Fred is not be allowed to perform any operation on the nation and athlete tables.

REVOKE ALL PRIVILEGES ON nation, athlete FROM Fred;