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.
REVOKE is an executable statement that can be issued interactively, embedded in a host language, or dynamically prepared.
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.
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}]
| 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. |
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.
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.
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.
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