DBADM authority is an administrative authority for a specific database. The database administrator possesses the privileges required to create objects and issue database commands. In addition, users with DBADM authority have SELECT privilege on the system catalog tables and views, and can execute all system-defined DB2® routines, except audit routines.
DBADM authority can only be granted or revoked by the security administrator (who holds SECADM authority) and can be granted to a user, a group, or a role. PUBLIC cannot obtain the DBADM authority either directly or indirectly.
SQLADM authority and WLMADM authority are subsets of the DBADM authority. WLMADM authority has the additional ability to grant the USAGE privilege on workloads.
The security administrator can specify whether a database administrator can access data within the database. DATAACCESS authority is the authority that allows access to data within a specific database. The security administrator can use the WITH DATAACCESS option of the GRANT DBADM ON DATABASE statement to provide a database administrator with this ability. If neither the WITH DATAACCCESS or WITHOUT DATAACCCESS options are specified, by default DATAACCESS authority is granted.
To grant database administrator authority without DATAACCESS authority, use GRANT DBADM WITHOUT DATAACCESS in your SQL statement.
The security administrator can specify whether a database administrator can grant and revoke privileges within the database. ACCESSCTRL authority is the authority that allows a user to grant and revoke privileges and non-administrative authorities within a specific database. The security administrator can use the WITH ACCESSCTRL option of the GRANT DBADM ON DATABASE statement to provide a database administrator with this ability. If neither the WITH ACCCESSCTRL or WITHOUT ACCCESSCTRL options are specified, by default ACCESSCTRL authority is granted.
To grant database administrator authority without ACCESSCTRL authority, use GRANT DBADM WITHOUT ACCESSCTRL in your SQL statement.
GRANT DBADM ON DATABASE TO user1
By default, DATAACCESS and ACCESSCTRL authority are also granted to user1.
REVOKE DBADM ON DATABASE FROM user1
Now user1 no longer holds DBADM authority, but still has both DATAACCESS and ACCESSCTRL authority.
REVOKE ACCESSCTRL, DATAACCESS ON DATABASE FROM user1
To revoke DBADM authority in a more narrow way, a security administrator can also use the DB2AUTH registry variable. The DBADM_NO_SEQ_XSR_AUTH option disables DBADM authority from having usage privilege on sequences and XSR objects, and grants those privileges to DATAACCESS authority instead. The NO_GROUP_SEQ_AUTH option disables group consideration for DBADM authority when issuing static DML statements involving sequences.
In Version 9.7, the DB2 authorization model has been updated to clearly separate the duties of the system administrator, the database administrator, and the security administrator. As part of this enhancement, the abilities given by the DBADM authority have changed. In releases prior to Version 9.7, DBADM authority automatically included the ability to access data and to grant and revoke privileges for a database. In Version 9.7, these abilities are given by the new authorities, DATAACCESS and ACCESSCTRL, respectively, as explained earlier.
In Version 9.7, these authorities are now part of DBADM authority. When DBADM authority is revoked in Version 9.7, these authorities are lost.
However, if a user held DBADM authority when you upgraded to Version 9.7, these authorities are not lost if DBADM authority is revoked. Revoking DBADM authority in Version 9.7 causes a user to lose these authorities only if they acquired them through holding DBADM authority that was granted in Version 9.7.