REVOKE statement (database privileges)

This form of the REVOKE statement revokes database privileges.

Syntax for REVOKE (database privileges)

Read syntax diagramSkip visual syntax diagramREVOKE,DBADMDBCTRLDBMAINTCREATETABCREATETSDISPLAYDBDROPIMAGCOPYLOADRECOVERDBREORGREPAIRSTARTDBSTATSSTOPDBON DATABASE,database-nameFROM,authorization-nameROLErole-namePUBLICBY,authorization-nameROLErole-nameALLINCLUDING DEPENDENT PRIVILEGESNOT INCLUDING DEPENDENT PRIVILEGES

Description for REVOKE (database privileges)

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 statement for a description of the FROM clause.
BY
Refer to REVOKE statement 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.

Notes for REVOKE (database privileges)

For considerations that apply to all REVOKE statement forms, see Notes for REVOKE.

Examples for REVOKE (database privileges)

Example 1: Revoke drop privileges on database DSN8D13A from user PEREZ. 
   REVOKE DROP
     ON DATABASE DSN8D13A
     FROM PEREZ;
Example 2: Revoke repair privileges on database DSN8D13A from all local users. (Grants to specific users will not be affected.) 
   REVOKE REPAIR
     ON DATABASE DSN8D13A
     FROM PUBLIC;
Example 3: Revoke authority to create new tables and load tables in database DSN8D13A from users WALKER, PIANKA, and FUJIMOTO. 
   REVOKE CREATETAB,LOAD
     ON DATABASE DSN8D13A
     FROM WALKER,PIANKA,FUJIMOTO;
Example 4: Revoke load privileges on database DSN8D13A from role ROLE1: 
   REVOKE LOAD
     ON DATABASE DSN8D13A
     FROM ROLE ROLE1;