REVOKE (use privileges)

This form of the REVOKE statement revokes authority to use particular buffer pools, storage groups, or table spaces.

Syntax for REVOKE (use privileges)

Read syntax diagramSkip visual syntax diagramREVOKE USE OFBUFFERPOOL,bpnameALL BUFFERPOOLSSTOGROUP,stogroup-nameTABLESPACE,database-name.table-space-nameFROM,authorization-nameROLErole-namePUBLICBY,authorization-nameROLErole-nameALLINCLUDING DEPENDENT PRIVILEGESNOT INCLUDING DEPENDENT PRIVILEGES

Description for REVOKE (use privileges)

BUFFERPOOL bpname,...
Revokes the privilege to refer to any of the identified buffer pools in a CREATE INDEX, CREATE TABLESPACE, ALTER INDEX, or ALTER TABLESPACE statement. See Naming conventions for more details about bpname.
ALL BUFFERPOOLS
Revokes the privilege to refer to any buffer pool in a CREATE INDEX, CREATE TABLESPACE, ALTER INDEX, or ALTER TABLESPACE statement.
STOGROUP stogroup-name,...
Revokes the privilege to refer to any of the identified storage groups in a CREATE INDEX, CREATE TABLESPACE, ALTER INDEX, or ALTER TABLESPACE statement.
TABLESPACE database-name.table-space-name,...
Revokes the privilege to refer to any of the specified table spaces in a CREATE TABLE statement. The default database-name is DSNDB04.

For table spaces in a work file database you cannot revoke the privilege from PUBLIC. When a table space is created in a work file database, PUBLIC implicitly receives the TABLESPACE privilege (without GRANT authority); this privilege is not recorded in the Db2 catalog, and it cannot be revoked.

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.

Notes for REVOKE (use privileges)

You can revoke privileges for only one type of object with each statement. Thus you can revoke the use of several table spaces with one statement, but not the use of a table space and a storage group.

For each object you name, you (or the indicated grantors) must have granted the USE privilege on that object to all identified users (including PUBLIC, if specified). The same object must not be identified more than once.

Revoking the privilege USE OF ALL BUFFERPOOLS does not cascade to all other privileges that can be granted under that privilege. A user with the privilege USE OF ALL BUFFERPOOLS WITH GRANT OPTION can make two types of grants:

  • GRANT USE OF ALL BUFFERPOOLS TO userid. This privilege is revoked when the original user's privilege is revoked.
  • GRANT USE OF BUFFERPOOL BPn TO userid. This privilege is not revoked when the original user's privilege is revoked.

Examples for REVOKE (use privileges)

Example 1: Revoke authority to use buffer pool BP2 from user MARINO. 
   REVOKE USE OF BUFFERPOOL BP2
     FROM MARINO;
Example 2: Revoke a grant of the USE privilege on the table space DSN8S12D in the database DSN8D12A. The grant is to PUBLIC, that is, to everyone at the local Db2 subsystem. (Grants to specific users are not affected.) 
   REVOKE USE OF TABLESPACE DSN8D12A.DSN8S12D
     FROM PUBLIC;
Example 3: Revoke the authority to use storage group SG1 from role ROLE1: 
   REVOKE USE OF STOGROUP SG1
     FROM ROLE ROLE1;