REVOKE (system privileges)

This form of the REVOKE statement revokes system privileges.

Start of change

Syntax

Read syntax diagram
           .-,----------------------------------------------------.                  
           V                                                      |  .-ON SYSTEM-.   
>>-REVOKE----+-Start of changeACCESSCTRLEnd of change-----------+-+--+-----------+-->
             +-ARCHIVE------------------------------------------+                    
             +-BINDADD------------------------------------------+                    
             +-BINDAGENT----------------------------------------+                    
             +-BSDS---------------------------------------------+                    
             +-CREATEALIAS--------------------------------------+                    
             +-CREATEDBA----------------------------------------+                    
             +-CREATEDBC----------------------------------------+                    
             +-CREATESG-----------------------------------------+                    
             +-CREATETMTAB--------------------------------------+                    
             +-Start of changeCREATE_SECURE_OBJECTEnd of change-+                    
             +-Start of changeDATAACCESSEnd of change-----------+                    
             +-Start of changeDBADMEnd of change----------------+                    
             +-DEBUGSESSION-------------------------------------+                    
             +-DISPLAY------------------------------------------+                    
             +-Start of changeEXPLAINEnd of change--------------+                    
             +-MONITOR1-----------------------------------------+                    
             +-MONITOR2-----------------------------------------+                    
             +-RECOVER------------------------------------------+                    
             +-Start of changeSQLADMEnd of change---------------+                    
             +-STOPALL------------------------------------------+                    
             +-STOSPACE-----------------------------------------+                    
             +-SYSADM-------------------------------------------+                    
             +-SYSCTRL------------------------------------------+                    
             +-SYSOPR-------------------------------------------+                    
             '-TRACE--------------------------------------------'                    

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

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

>--+--------------------------------------------+--------------><
   +-INCLUDING DEPENDENT PRIVILEGES-------------+   
   |                                    (1) (2) |   
   '-NOT INCLUDING DEPENDENT PRIVILEGES---------'   
Notes:
  1. INCLUDING DEPENDENT PRIVILEGES must not be specified when ACCESSCTRL, DATAACCESS, or DBADM is specified.
  2. NOT INCLUDING DEPENDENT PRIVILEGES must be specified when ACCESSCTRL, DATAACCESS, or DBADM is specified
End of change

Description

Start of changeACCESSCTRLEnd of change
Start of changeRevokes the ACCESSCTRL authority, but does not revoke any privileges that are dependent on it.End of change
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.
Start of changeCREATE_SECURE_OBJECTEnd of change
Start of changeRevokes the privilege to create a secure object.End of change
Start of changeDATAACCESSEnd of change
Start of change 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.

End of change
Start of changeDBADMEnd of change
Start of change 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.End of change
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.
Start of changeEXPLAINEnd of change
Start of changeRevokes 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.

End of change
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.
Start of changeSQLADMEnd of change
Start of changeRevokes 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.
End of change
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.
Start of changeON SYSTEMEnd of change
Start of changeIdentifies that the system privilege is revoked for the entire DB2 subsystem.End of change
FROM
Refer to REVOKE for a description of the FROM clause.
BY
Refer to REVOKE for a description of the BY clause.
Start of changeINCLUDING DEPENDENT PRIVILEGES or NOT INCLUDING DEPENDENT PRIVILEGESEnd of change
Start of changeSpecifies 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.

End of change

Examples

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;
Start of changeExample 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;
End of change
Start of changeExample 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;
End of change
Start of changeExample 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;
End of change