In DB2 Version 9.7, a user who holds SYSADM authority no longer has implicit DBADM authority and therefore has limited capabilities compared to those available in Version 9.5.
Example authorization errors are SQL1092N, SQL0551N and SQL0552N:
- authorization-ID does not have the authority to perform the requested command or operation.
- authorization-ID does not have the required authorization or privilege to perform operation operation on object object-name.
- authorization-ID does not have the privilege to perform operation operation.
Prior to DB2 Version 9.7, a user with SYSADM authority had implicit DBADM authority. Common configurations have the instance owner account as a member of the SYSADM group. Applications that are run under the instance owner account had unlimited authority on the database.
In DB2 Version 9.7, a user with SYSADM authority no longer has implicit DBADM authority. As a result, any applications that are run under the instance owner account might experience authorization errors when trying to perform operations that are no longer within the scope of a SYSADM.
For customers that are upgrading their database, this is not a problem as the UPGRADE DATABASE command and the RESTORE DATABASE command (for a downlevel database) automatically grants DBADM authority to the SYSADM group to preserve old behavior. However, this is not the case for any new Version 9.7 database. A newly created database does not have DBADM authority automatically granted to the SYSADM group because this is the preferred secure setup.
Resolving The Problem
The resolution to the errors is to verify the authorities for the userid.
To get the list of authorities held by the instance owner authorization ID, perform the following steps:
- Use the SYSPROC.AUTH_GET_INSTANCE_AUTHID() scalar function to determine the instance owner authorization ID.
1 record(s) selected.
- Get the list of authorities for this authorization ID.
SELECT * FROM
TABLE (SYSPROC.AUTH_LIST_AUTHORITIES_FOR_AUTHID ('BOB', 'U'))AS T
ORDER BY AUTHORITY
- (Optional) Grant any missing authorities.
GRANT DBADM ON DATABASE TO USER BOB
In the case where BOB is the only SECADM, it is not as straight forward for BOB to obtain DBADM authority. The process is complicated by the rule that a user cannot grant itself a privilege or an authority (SQL0554N). Hence, even though BOB has the SECADM authority to grant DBADM, BOB is not able to grant itself the DBADM authority. You must grant SECADM authority to another user (ex. JOE) who can then grant DBADM to BOB. Remember to revoke SECADM from JOE when done.
16 June 2018