More DBADM magic in DB2 9.7

Previously (below version 9.7) granting DBADM granted all the database authorities explicitly - specifically these ones

BINDADD
CONNECT
CREATETAB
CREATE_EXTERNAL_ROUTINE
CREATE_NOT_FENCED_ROUTINE
IMPLICIT_SCHEMA
QUIESCE_CONNECT
LOAD

And when you revoked DBADM, these guys would still linger on! In 9.7 this changes - granting DBADM still gives you all these authorities ..but implicitly. You will not see them in the system catalog SYSCAT.DBAUTH view being granted. Nevertheless you will have them. And, naturally, when you revoke DBADM, they will be gone along with DBADM - just the way it should have always been..

DBADM and ACCESSCTRL

Continuing along the same lines as the previous post about DBADM and DATAACCESS, there is much more that was taken away from DBADM in DB2 9.7.

1.  ability to GRANT/REVOKE *any* database authority (except SECADM, which it never had)

2. ability to GRANT/REVOKE *any* privilege on *any* database object

This again is a significant step towards achieving separation of duties between the 'security administrator' and the 'database administrator'. A new authority 'Access Control authority' (ACCESSCTRL) was introduced to hold these privileges. By default, when DBADM is granted, ACCESSCTRL is also granted, but one can now choose to not do so by using

GRANT DBADM WITHOUT ACCESSCTRL

One thing to keep in mind is when DBADM is revoked, DATAACCESS and ACCESSCTRL (if granted by default) are NOT automatically revoked.

Whats with DBADM in DB2 9.7

DB2 seems to have taken the 'separation of duties' principal to heart. This is really good from a security point of view. Starting with 9.7, the database administrator authority (DBADM), by itself, no longer has implicit access to DB2 data! It means, admins can no longer *see* sensitive data (this credit card nos..) stored in the database.
Another authority called DATAACCESS was introduced which does have implicit access to data. When granting someone DBADM, the default is still to grant DATAACCESS, but this can be changed using

GRANT DBADM WITHOUT DATAACCESS

 

DB2 9.7 SECADM

I mentioned in the previous post that the 'security administrator' authority (SYSADM) no longer has ability to grant/revoke DBADM and SECADM. Only SECADM now has the ability to grant DBADM and SECADM. In fact, in 9.7 *only* SECADM can grant/revoke *all* database authorities.

A new 'access control' authority (ACCESSCTRL) is introduced in 9.7 which grants one the ability to grant/revoke *all* database authorities and *all* privileges on *all* objects! This new authority is implicitly included in the SECADM authority.