REVOKE (Table or View Privileges)

The REVOKE command cancels database, table or view, or system privileges held by specified AuthIDs. The XDB Server supports three separate REVOKE formats, one each for revoking database, table or view, and system privileges. All relevant objects must be located in the current location. Revoked privileges are recorded in the system catalog for the current location.

The XDB Server syntactically supports the collection, package, and plan classes of GRANT/REVOKE privilege commands. Also syntactically supported are the use class of GRANT/REVOKE privileges, including the USE OF ALL BUFFERPOOLS clause. The XDB Server cannot functionally utilize these types of privileges, although future catalog support is planned. DB2 users should consult their DB2 documentation for information on these four classes of privilege commands.

Invocation

REVOKE is an executable statement that can be issued interactively, embedded in a host language, or dynamically prepared.

Authorization

In most instances, the authority needed to revoke a specific privilege must be identical to the authority that originally issued the privilege -- which means that privileges individually granted by a specific AuthID are generally revoked individually by the same AuthID.

Syntax

This format is used to revoke privileges on tables or views. In every case, the privileges chosen are revoked only from the tables or views named in the ON [TABLE] clause. One or more of the privilege keywords (separated with commas) defined below and/or the optional keyword(s) ALL [PRIVILEGES] may be specified after REVOKE, allowing multiple privileges to be revoked in one REVOKE statement.

Bold text indicates clauses or options that are supported only syntactically.

REVOKE
{       [ALTER[, ]] 
        [DELETE[, ]] 
        [INDEX[, ]] 
        [INSERT[, ]] 
        [SELECT[, ]] 
        [TRIGGER[, ]] 
        [UPDATE] | ALL [PRIVILEGES]
}
    ON [TABLE] {table-name[,...] | view-name[,...]}
    FROM AuthID[,...][PUBLIC [AT ALL LOCATIONS]]
    [BY {AuthID[,...] | ALL}]

Parameters:

ALTER Privilege to use the ALTER TABLE statement (cannot specify a view).
DELETE Privilege to use the DELETE statement.
INDEX Privilege to use the CREATE INDEX statement (cannot specify a view).
INSERT Privilege to use the INSERT statement.
SELECT Revokes the privilege to create a view or read data from the specified table or view. A view or a materialized query table is dropped when the SELECT privilege that was used to create it is revoked, unless the owner of the view or materialized query table was directly granted the SELECT privilege from another source before the view or materialized query table was created.
TRIGGER Revokes the privilege to use the CREATE TRIGGER statement
UPDATE Privilege to use the UPDATE statement on all columns of the tables named in the ON [TABLE] clause. When followed by (column-name[,...]), the update privileges are revoked only from the columns specified in parentheses. Note that (unlike the corresponding GRANT command) the UPDATE privilege can only be revoked from a list of individual column names in XDB mode.
column-name Specifies an individual column in a table from which the UPDATE privilege is being revoked.
table-name or view-name Preceded by the keyword(s) ON [TABLE], identifies a table or view from which privileges are being revoked.
AuthID Identifies a user authorization ID from which the privilege(s) specified are being revoked. The optional PUBLIC [AT ALL LOCATIONS] keyword(s) can be specified along with (or in place of) an AuthID (or a list of AuthIDs). The PUBLIC AT ALL LOCATIONS keyword option revokes the specified table privileges from all users at every location.

ALL [PRIVILEGES]

If the ALL or ALL PRIVILEGES keywords are specified, all table or view privileges (for which the issuer has REVOKE authority) are revoked for the objects identified in the ON [TABLE] clause.

Description

The REVOKE command is used to remove system, database, table, and view privileges from designated AuthID(s). There are three formats for the REVOKE command, depending on the type of privilege being revoked. Database privileges include CREATETAB, DBADM and DROP privileges. Table privileges include ALTER, DELETE, INDEX, INSERT, SELECT, and UPDATE privileges. System privileges that imply other privileges are also characterized as authorities.

Individual grants (or groups of grants) are removed from the catalog tables by executing REVOKE command statements. Each individual revoke (as registered in the catalog tables) involves the removal of one privilege by one revoker from one revokee. The removal of privileges with the REVOKE statement is recorded in the system catalog for the current location. The status of each individual granted or revoked privilege (for each AuthID) is recorded in the catalog tables.

For a declared temporary table, no privileges can be revoked because none can be granted. When a declared temporary table is defined, PUBLIC implicitly receives all table privileges (without GRANT authority) for the table. These privileges are not recorded in the DB2 catalog.

Note: Since the same privilege can be granted to a single grantee AuthID by several different grantor AuthIDs, the grantee AuthID retains the privilege as long as one (or more) of these grants remains recorded in the system catalog.

FROM

Privileges may be revoked from a specific set of users by listing their AuthIDs in the FROM clause of the REVOKE statement. To revoke privileges from all AuthIDs in the current location, use the keyword PUBLIC in the FROM clause. When a privilege is revoked from PUBLIC, the system catalog continues to maintain the list of AuthIDs that were individually granted the privilege.

Examples:

The following example revokes authority for the individuals with the AuthIDs userb and userc to update the PART and SUPPLIER tables.

REVOKE UPDATE
    ON part, supplier
    FROM userb, userc

To revoke privileges from PUBLIC to select, update, delete and insert records in the PART table, use the command:

REVOKE SELECT, UPDATE, DELETE, INSERT
    ON part
    FROM PUBLIC

To revoke the select privilege on all tables from AuthID usera, you might use a command like:

REVOKE SELECT
    ON ALL
    FROM usera

To revoke privileges (XDB mode only) on a subset of the columns in the EMPLOYEE table from AuthID usera, issue the command:

REVOKE UPDATE(address, city, state, zip, phone)
    ON employee
    FROM usera