REVOKE (System 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 system privileges. In every case, the system privileges chosen are revoked only for those AuthID(s) specified in the FROM clause. One or more of the privilege option keywords defined below (separated with commas) may be specified after REVOKE, allowing multiple system privileges to be revoked in one REVOKE statement.

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

REVOKE 
        [CREATEALIAS[, ]] 
        [CREATEDBA[, ]] 
        [CREATEDBC[, ]] 
        [CREATETMTAB[, ]] 
        [CREATESG[, ]] 
        [SYSADM[, ]] 
        [SYSCTRL[, ]]
    FROM AuthID[,...][PUBLIC]
    BY {AuthID[,...] | ALL}

Parameters:

CREATEALIAS Privilege to use the CREATE ALIAS statement.
CREATEDBA Privilege to issue the CREATE DATABASE statement and acquire DBADM authority over those databases.
CREATEDBC Privilege to issue the CREATE DATABASE statement and acquire DBCTRL authority over those databases.
CREATETMTAB Privilege to issue the CREATE GLOBAL TEMPORARY TABLE statement.
CREATESG Privilege to create new storage groups.
SYSADM Revokes system administrator authority for the location.
SYSCTRL Revokes the system control authority for the location.
Note:

The ARCHIVE, BINDADD, BINDAGENT, BSDS, DISPLAY, MONITOR1, MONITOR2, RECOVER, STOPALL, STOSPACE, SYSOPR, and TRACE system privileges control specific authorities for running mainframe utilities (for example, CHECK, RUNSTATS and QUIESCE). The XDB Server does not functionally support these privileges. However, to maintain maximum compatibility with mainframe DB2 operations, The XDB Server syntactically checks these privileges (if encountered) and updates the appropriate catalog tables.

AuthID Identifies a user authorization ID for which the specified privilege(s) are being revoked. The PUBLIC keyword option can be specified along with (or in place of) an AuthID (or a list of AuthIDs), revoking privileges from each valid AuthID at the current location.

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.

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.

DISPLAY

Revokes the privilege to use the following commands:

  • The DISPLAY ARCHIVE command for archive log information
  • The DISPLAY BUFFERPOOL command for the status of buffer pools
  • The DISPLAY DATABASE command for the status of all databases
  • The DISPLAY FUNCTION SPECIFIC command for statistics about accessed external user-defined functions
  • The DISPLAY LOCATION command for statistics about threads with a distributed relationship
  • The DISPLAY PROCEDURE command for statistics about accessed stored procedures
  • The DISPLAY THREAD command for information on active threads with in DB2
  • The DISPLAY TRACE command for a list of active traces