REVOKE (database privileges)

This form of the REVOKE statement revokes database privileges.

Syntax


           .-,-------------.               .-,-------------.   
           V               |               V               |   
>>-REVOKE----+-DBADM-----+-+--ON DATABASE----database-name-+---->
             +-DBCTRL----+                                     
             +-DBMAINT---+                                     
             +-CREATETAB-+                                     
             +-CREATETS--+                                     
             +-DISPLAYDB-+                                     
             +-DROP------+                                     
             +-IMAGCOPY--+                                     
             +-LOAD------+                                     
             +-RECOVERDB-+                                     
             +-REORG-----+                                     
             +-REPAIR----+                                     
             +-STARTDB---+                                     
             +-STATS-----+                                     
             '-STOPDB----'                                     

         .-,----------------------.   
         V                        |   
>--FROM----+-authorization-name-+-+----------------------------->
           +-ROLE--role-name----+     
           '-PUBLIC-------------'     

>--+------------------------------------+----------------------->
   |       .-,----------------------.   |   
   |       V                        |   |   
   '-BY--+---+-authorization-name-+-+-+-'   
         |   '-ROLE--role-name----'   |     
         '-ALL------------------------'     

>--+------------------------------------+----------------------><
   +-INCLUDING DEPENDENT PRIVILEGES-----+   
   '-NOT INCLUDING DEPENDENT PRIVILEGES-'

Description

Each keyword listed revokes the privilege described, but only as it applies to or within the databases named in the statement.

DBADM

Revokes the database administrator authority.

DBCTRL

Revokes the database control authority.

DBMAINT

Revokes the database maintenance authority.

CREATETAB

Revokes the privilege to create new tables. If CREATETAB privilege is revoked from DSNDB04, tables cannot be created in implicitly created databases. For a work file database, you cannot revoke the privilege from PUBLIC. When a work file 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.

CREATETS

Revokes the privilege to create new table spaces.

DISPLAYDB

Revokes the privilege to issue the DISPLAY DATABASE command.

DROP

Revokes the privilege to issue the DROP or ALTER statements in the specified databases.

IMAGCOPY

Revokes the privilege to run the COPY, MERGECOPY, and QUIESCE utilities against table spaces of the specified databases, and to run the MODIFY RECOVERY utility.

LOAD

Revokes the privilege to use the LOAD utility to load tables.

RECOVERDB

Revokes the privilege to use the RECOVER and REPORT utilities to recover table spaces and indexes.

REORG

Revokes the privilege to use the REORG utility to reorganize table spaces and indexes.

REPAIR

Revokes the privilege to use the REPAIR and DIAGNOSE utilities.

STARTDB

Revokes the privilege to issue the START DATABASE command.

STATS

Revokes the privilege to use the RUNSTATS utility to update statistics, and the CHECK utility to test whether indexes are consistent with the data they index, and the MODIFY STATISTICS utility to delete unwanted statistics history records from the corresponding catalog tables.

STOPDB

Revokes the privilege to issue the STOP DATABASE command.

ON DATABASE database-name,...

Identifies databases on which you are revoking the privileges. For each database you identify, you (or the indicated grantors) must have granted at least one of the specified privileges on that database to all identified users (including PUBLIC, if specified). The same database must not be identified more than once.

FROM

Refer to REVOKE for a description of the FROM clause.

BY

Refer to REVOKE for a description of the BY clause.

INCLUDING DEPENDENT PRIVILEGES or NOT INCLUDING DEPENDENT PRIVILEGES

Specifies whether revoking a privilege or an authority from an authorization ID or a role also results in revoking the grants that were made by that user. The default value is based on the authority that is being revoked and the REVOKE_DEP_PRIVILEGES system parameter:

When ACCESSCTRL, DATAACCESS, or system DBADM authority is revoked, NOT INCLUDING DEPENDENT PRIVILEGES is assumed and the clause must be specified on the REVOKE statement.
When the REVOKE_DEP_PRIVILEGES system parameter is set to NO, NOT INCLUDING DEPENDENT PRIVILEGES is assumed and an error is returned if the statement includes INCLUDING DEPENDENT PRIVILEGES.
Otherwise, INCLUDING DEPENDENT PRIVILEGES is assumed and the clause must be specified on the REVOKE statement.

INCLUDING DEPENDENT PRIVILEGES

Specifies that revoking a privilege or an authority from an authorization ID or a role also results in revoking dependent privileges. This means that any grants that were made by the user will continue to be revoked, until all grants in the chain have been revoked.

INCLUDING DEPENDENT PRIVILEGES cannot be specified if the system parameter REVOKE_DEP_PRIVILEGES is set to NO, which enforces the behavior to not include the dependent privileges.

NOT INCLUDING DEPENDENT PRIVILEGES

Specifies that revoking a privilege or an authority from an authorization ID or a role does not cause the grants that were made by the user to be revoked. However, for the revoked privileges, all implications of the privilege being revoked are applied. For example, if the revoked privileges were required to bind a package successfully, that package would continue to be invalidated as a result of the package owner losing these privileges. An object might be dropped if a privilege is revoked that was used to create the object.

NOT INCLUDING DEPENDENT PRIVILEGES must be specified when ACCESSCTRL, DATAACCESS, or system DBADM authority is revoked.

NOT INCLUDING DEPENDENT PRIVILEGES cannot be specified if the system parameter REVOKE_DEP_PRIVILEGES is set toYES, which enforces the behavior to include dependent privileges in the revoke.

Examples

Example 1: Revoke drop privileges on database DSN8D10A from user PEREZ.

   REVOKE DROP
     ON DATABASE DSN8D10A
     FROM PEREZ;

Example 2: Revoke repair privileges on database DSN8D10A from all local users. (Grants to specific users will not be affected.)

   REVOKE REPAIR
     ON DATABASE DSN8D10A
     FROM PUBLIC;

Example 3: Revoke authority to create new tables and load tables in database DSN8D10A from users WALKER, PIANKA, and FUJIMOTO.

   REVOKE CREATETAB,LOAD
     ON DATABASE DSN8D10A
     FROM WALKER,PIANKA,FUJIMOTO;

Example 4: Revoke load privileges on database DSN8D10A from role ROLE1:

   REVOKE LOAD
     ON DATABASE DSN8D10A
     FROM ROLE ROLE1;