• If ACCESSCTRL, DATAACCESS or system DBADM is being revoked, there’s no cascade. The default is always NOT INCLUDING DEPENDENT PRIVILEGES and must be specified on the REVOKE SQL statement.
  • When the subsystem parameter REVOKE_DEP_PRIVILEGES is set to NO, the default will always be NOT INCLUDING DEPENDENT PRIVILEGES.

The SQL REVOKE has two new clauses to support whether revocation of privileges from an authid will remove the privileges granted by that authid:

  • INCLUDING DEPENDENT PRIVILEGES: Specifying this clause causes the cascade revoke behavior found in previous versions of DB2. If an authorization or privilege is revoked from an authid or a role, any dependent authorizations or privileges are also revoked. This clause can’t be specified if subsystem parameter REVOKE_DEP_PRIVILEGES has been set to NO.
  • NOT INCLUDING DEPENDENT PRIVILEGES: If this clause is specified, cascade revoke is prevented. This clause can’t be specified if subsystem parameter REVOKE_DEP_PRIVILEGES has been set to YES.

Other System Privileges

The ACCESSCTRL system authority lets the holder explicitly grant most privileges to any authid using the SQL GRANT statement. Having this authority also lets you revoke most explicitly granted privileges using the BY clause. Also, the authid with ACCESSSTRL can implicitly read all catalog tables and update  all updatable catalog tables with the exception of  SYSIBM.SYSAUDITPOLICIES. SYSIBM.SYSAUDITPOLICIES contains audit policies and security enhancement new to DB2 10. Because of the potential sensitively of an audit policy, only SECADM can implicitly update this table.

The DATAACCESS system authority lets the authid or role access data; specifically read and update all user tables, views, and Materialized Query Tables (MQTs) in the DB2 subsystem. Execution of all plans, packages, and procedures is included along with the ability to run RECOVERDB, REORG, REPAIR, and LOAD. DATAACCESS also lets the authid issue ALTER and TERM UTILITY commands and access catalog tables.

System DBADM is another new DB2 10 system authority granted on all databases in the entire DB2 subsystem, allowing database administrative tasks to be performed on the objects in those databases without allowing access to the data. Be careful when passing out this authority. Depending on options chosen, this authority can be powerful.  

An authid or role with system DBADM authority can issue SQL CREATE, ALTER, or DROP statements to manage most objects in the DB2 subsystem, along with issuing SQL statements such as EXPLAIN, LABEL, PREPARE, and RENAME. This doesn’t include security objects. It can issue most DB2 commands. The DB2 10 Administration Guide (SC19-2968) provides a complete list of the commands allowed and other privileges included in system DBADM.

System DBADM can alter and terminate the execution of utility job steps and run many of the DB2 utilities. The utilities most noticeably missing include LOAD, REORG, and UNLOAD.

When granting system DBADM to an authid, WITH ACCESSCTRL and WITH DATAACCESS can be optionally specified, giving the system DBADM the additional privileges associated with those system privileges. For example, specifying WITH ACCESSCTRL would allow the system DBADM to run the LOAD, REORG and UNLOAD utilities, along with giving data access for all tables, views, and MQTs in the subsystem. This privilege is also affected by the DSNZPARM keyword SEPARATE_SECURITY. System DBADM can set the owner to a BIND or REBIND to any ID if SEPARATE_SECURITY is equal to NO.

System DBADM authority is new in DB2 10 and shouldn’t be confused with DBADM authority, which has existed for many years. System DBADM is granted for the entire DB2 subsystem and allows management of all non-security-related objects. DBADM is granted over a database and allows management of only that specific database the privilege is granted for.

ACCESSCTRL, DATAACCESS, and system DBADM have four things in common:

4 Pages