Micro Focus Enterprise Developer 3.0 for Eclipse > Programming > Mainframe Programming > SQL Option for DB2 > Reference > SQL Reference > SQL Statements > REVOKE (Database Privileges)

REVOKE (Database 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.

Restriction: This topic applies to Windows environments only.

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 specified databases. One or more of the privilege option keywords defined below (separated with commas) may be specified after REVOKE, allowing multiple privileges to be revoked in one statement. In every case, the privileges chosen are revoked only from the logical database(s) named in the REVOKE command.

REVOKE 
        [DBADM[, ]] 
        [DBCTRL[, ]] 
        [DBMAINT[, ]] 
        [CREATETAB[, ]] 
        [CREATETS[, ]] 
        [DROP[, ]] 
        [STARTDB[, ]] 
        [STATS[, ]] 
        [STOPDB[, ]]
    ON DATABASE database-name[,...]
    FROM AuthID[,...][PUBLIC]
    [BY AuthID[,...][ALL]]

REVOKE 
        [DBADM[, ]] 
        [DBCTRL[, ]] 
        [DBMAINT[, ]] 
        [CREATETAB[, ]] 
        [CREATETS[, ]] 
        [DROP[, ]] 
        [STARTDB[, ]] 
        [STATS[, ]] 
        [STOPDB[, ]]
    ON DATABASE database-name[,...]
    FROM AuthID[,...][PUBLIC]
    [BY AuthID[,...][ALL]]

Parameters:

DBADM	Database administrator authority.
DBCTRL	Database control authority.
DBMAINT	Database maintenance authority.
CREATETAB	Privilege to create new tables.
CREATETS	Privilege to create new table spaces.
DROP	Privilege to issue the DROP or ALTER DATABASE statements for the designated databases.
STARTDB	Privilege to issue the START DATABASE command.
STOPDB	Privilege to issue the STOP DATABASE command.

Note:

The DISPLAYDB, IMAGCOPY, LOAD, RECOVERDB, REORG, REPAIR, and STATS database grant/revoke privileges are specific to the DB2 mainframe environment, and 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.

`database-name`	Identifies a database on which privileges are to be revoked. Each database named must exist at the current location. The XDB Server does not support cascading revoke.
`AuthID`	Identifies a user authorization ID from 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 the specified privileges from all valid AuthIDs 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.

For a TEMP database, you cannot revoke the privilege from PUBLIC. When a TEMP database is created, PUBLIC implicitly receives the CREATETAB privilege (without GRANT authority); this privilege is not recorded in the DB2 catalog, and it cannot be revoked.

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.

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.

Example:

The following example revokes DBADM authority for a database named PICTURES from the individual(s) with AuthID usera.

REVOKE DBADM
    ON DATABASE pictures
    FROM usera