DB2 Version 9.7 for Linux, UNIX, and Windows

Database administration authority (DBADM)

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.

Holding the DBADM authority for a database allows a user to perform these actions on that database:

SQLADM authority and WLMADM authority are subsets of the DBADM authority. WLMADM authority has the additional ability to grant the USAGE privilege on workloads.

Granting DATAACCESS authority with DBADM authority

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.

Granting ACCESSCTRL authority with DBADM authority

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.

Revoking DBADM authority

If a security administrator has granted DBADM authority that includes DATAACCESS or ACCESSCTRL authority, to revoke these authorities, the security administrator must explicitly revoke DATAACCESS or ACCESSCTRL authority. For example, if the security administrator grants DBADM authority to a user:
GRANT DBADM ON DATABASE TO user1

By default, DATAACCESS and ACCESSCTRL authority are also granted to user1.

Later, the security administrator revokes DBADM authority from user1:
REVOKE DBADM ON DATABASE FROM user1

Now user1 no longer holds DBADM authority, but still has both DATAACCESS and ACCESSCTRL authority.

To revoke these remaining authorities, the security administrator needs to revoke them explicitly:
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.

Differences for DBADM authority in prior releases

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.

Also, in releases prior to Version 9.7, granting DBADM authority automatically granted the following authorities too:
  • BINDADD
  • CONNECT
  • CREATETAB
  • CREATE_EXTERNAL_ROUTINE
  • CREATE_NOT_FENCED_ROUTINE
  • IMPLICIT_SCHEMA
  • QUIESCE_CONNECT
  • LOAD
Prior to Version 9.7, when DBADM authority was revoked these authorities were not revoked.

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.