REVOKE statement (system privileges)

This form of the REVOKE statement revokes system privileges.

Syntax for REVOKE (system privileges)

Notes:

¹ INCLUDING DEPENDENT PRIVILEGES must not be specified when ACCESSCTRL, DATAACCESS, or DBADM is specified.
² NOT INCLUDING DEPENDENT PRIVILEGES must be specified when ACCESSCTRL, DATAACCESS, or DBADM is specified

Description for REVOKE (system privileges)

ACCESSCTRL

Revokes the ACCESSCTRL authority, but does not revoke any privileges that are dependent on it.

ARCHIVE

Revokes the privilege to use the ARCHIVE LOG command.

BINDADD

Revokes the privilege to create plans and packages using the BIND subcommand with the ADD option.

BINDAGENT

Revokes the privilege to issue the BIND, FREE PACKAGE, or REBIND subcommands for plans and packages and the DROP PACKAGE statement on behalf of the grantor. The privilege also allows the holder to copy and replace plans and packages on behalf of the grantor.

A revoke of this privilege does not cascade.

BSDS

Revokes the privilege to issue the RECOVER BSDS command.

CREATEALIAS

Revokes the privilege to use the CREATE ALIAS statement.

CREATEDBA

Revokes the privilege to issue the CREATE DATABASE statement and acquire DBADM authority over those databases.

CREATEDBC

Revokes the privilege to issue the CREATE DATABASE statement and acquire DBCTRL authority over those databases.

CREATESG

Revokes the privilege to create new storage groups.

CREATETMTAB

Revokes the privilege to use the CREATE GLOBAL TEMPORARY TABLE statement.

CREATE_SECURE_OBJECT

Revokes the privilege to create a secure object.

DATAACCESS

Revokes the DATAACCESS authority, but does not revoke any privileges that are dependent on it. Revoking DATAACCESS can result in authorization cache entries (plan, package, routine, and dynamic statement) being updated if they were dependent on it. The RESTRICT semantics on objects prevents the DATAACCESS authority from being revoked if the revokee owns an object that was created with dependencies on the authority to be revoked.

Revoking DATAACCESS is similar to revoking the individual privileges that DATAACCESS includes. For example, if a view was created based on the view owner having the SELECT privilege as acquired through the DATAACCESS authority, revoking DATAACCESS would be the equivalent of revoking the SELECT privilege and the view would be dropped.

DBADM

Revokes the DBADM authority from the user. If this user was also granted DATACCESS or ACCESSCTRL authority along with DBADM authority, DATACCESS or ACCESSCTRL would not be revoked.

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

DEBUGSESSION

Revokes the privilege to create a debug session, which prevents client application debugging of native SQL or Java™ procedures that are executed within the session.

EXPLAIN

Revokes the privilege to issue the following:

The EXPLAIN statement with the following options:
- PLAN
- ALL
The PREPARE statement
The DESCRIBE TABLE statement
The ability to explain dynamic SQL statements that are executing with the special register CURRENT EXPLAIN MODE = EXPLAIN
The BIND options EXPLAIN(ONLY) and SQLERROR(CHECK)
EXPLAIN(ONLY) allows to explain the statements. SQLERROR(CHECK) performs all syntax and semantic checks on the SQL statements being bound.

MONITOR1

Revokes the privilege to obtain IFC data classified as serviceability data, statistics, accounting, and other performance data that does not contain potentially secure data.

MONITOR2

Revokes the privilege to obtain IFC data classified as containing potentially sensitive data such as SQL statement text and audit data. (Having the MONITOR2 privilege also implies having MONITOR1 privileges, however, revoking the MONITOR2 privilege does not cause the revoke of an explicitly granted MONITOR1 privilege.)

RECOVER

Revokes the privilege to issue the RECOVER INDOUBT command.

SQLADM

Revokes the privilege to issue the following:

The DESCRIBE TABLE statement
The EXPLAIN statement with the following options:
- PLAN
- ALL
- STMTCACHE ALL
- STMTID
- STMTTOKEN
- MONITORED STMTS
The PREPARE statement
The ability to explain dynamic SQL statements that are executing with the special register CURRENT EXPLAIN MODE = EXPLAIN
The BIND options EXPLAIN(ONLY) and SQLERROR(CHECK)
EXPLAIN(ONLY) allows to explain the statements. SQLERROR(CHECK) performs all syntax and semantic checks on the SQL statements being bound.
The START command
The STOP command
The DISPLAY PROFILE command
The ability to execute the RUNSTATS utility and the MODIFY STATISTICS utility in any database
MONITOR2 privilege, which allows users to obtain IFC data that is classified as containing potentially sensitive data, such as SQL statement text and audit data, as well as IFC data that is classified as serviceability data, statistics, accounting, and other performance data.

STOPALL

Revokes the privilege to use the STOP DB2 command.

STOSPACE

Revokes the privilege to use the STOSPACE utility.

SYSADM

Revokes the system administrator authority.

SYSCTRL

Revokes the system control authority.

SYSOPR

Revokes the system operator authority.

TRACE

Revokes the privilege to use the MODIFY TRACE, START TRACE, and STOP TRACE commands.

ON SYSTEM

Identifies that the system privilege is revoked for the entire Db2 subsystem.

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 (system privileges)

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

Examples for REVOKE (system privileges)

Example 1: Revoke DISPLAY privileges from user LUTZ.

   REVOKE DISPLAY
     FROM LUTZ;

Example 2: Revoke BSDS and RECOVER privileges from users PARKER and SETRIGHT.

   REVOKE BSDS,RECOVER
     FROM PARKER,SETRIGHT;

Example 3: Revoke TRACE privileges previously granted to all local users. (Grants to specific users will not be affected.)

   REVOKE TRACE
     FROM PUBLIC;

Example 4: Revoke ARCHIVE privileges from role ROLE1:

   REVOKE ARCHIVE
     FROM ROLE ROLE1;

Example 5: SECADM Mary revokes the privilege to create a secure object from Steve that was granted by another SECADM.

   REVOKE CREATE_SECURE_OBJECT
		 FROM STEVE BY MARY;

Example 6: Revoke system DBADM from the role, ADMINROLE. This only revokes system DBADM authority from the role. If DATAACCESS and ACCESSCTRL authorities were granted during GRANT DBADM, those authorities are not revoked.

   REVOKE DBADM ON SYSTEM 
		FROM ROLE ADMINROLE 
		NOT INCLUDING DEPENDENT PRIVILEGES;

Example 7: Revoke system DBADM, DATAACCESS, and ACCESSCTRL authorities from the role, ADMINROLE.

   REVOKE DBADM, DATAACCESS, ACCESSCTRL ON SYSTEM 
		FROM ROLE ADMINROLE 
		NOT INCLUDING DEPENDENT PRIVILEGES;