Authorization errors due to changes to the SYSADM authority scope

Technote (troubleshooting)


Problem(Abstract)

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.
 

Symptom

Example authorization errors are SQL1092N, SQL0551N and SQL0552N:

SQL1092N

    authorization-ID does not have the authority to perform the requested command or operation.


SQL0551N
    authorization-ID does not have the required authorization or privilege to perform operation operation on object object-name.


SQL0552N
    authorization-ID does not have the privilege to perform operation operation.
     


Cause

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.
 


Environment

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:

  1. Use the SYSPROC.AUTH_GET_INSTANCE_AUTHID() scalar function to determine the instance owner authorization ID.
    For example:

    VALUES SYSPROC.AUTH_GET_INSTANCE_AUTHID()

    1
    -----------------------------------------------------------------
    BOB

    1 record(s) selected.



  2. Get the list of authorities for this authorization ID.
    For example:
    SELECT * FROM
    TABLE (SYSPROC.AUTH_LIST_AUTHORITIES_FOR_AUTHID ('BOB', 'U'))AS T
    ORDER BY AUTHORITY


  3. (Optional) Grant any missing authorities.
    For example:
    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.

Related information

SYSADM authority scope has changed
Security changes summary
AUTH_LIST_AUTHORITIES_FOR_AUTHID table function
SQL1092N
SQL0551N

Rate this page:

(0 users)Average rating

Document information


More support for:

DB2 for Linux, UNIX and Windows
Administration - Authorization (Grant/Revoke privileges/authorities)

Software version:

9.7

Operating system(s):

AIX, HP-UX, Linux, Solaris, Windows

Reference #:

1385801

Modified date:

2013-04-30

Translate my page

Machine Translation

Content navigation