IBM Support

Authorization errors after restoring a database to V9.7 or higher instance

Question & Answer


Question

Why do I get authorization errors after restroing a database to a V9.7 or higher instance even though I am SYSADM?

Cause

Starting DB2 V9.7, SYSADM no longer has implicit DBADM Privileges due to a change in security policies, common SQL errors received are SQL0551N, SQL0552N or SQL3020N

Answer

Explicitly granting the DBADM authority to the user or group should resolve the issue. However, in DB2 V9.7, only the SECADM user can grant the DBADM authority to a user or group. Users creating the database has SECADM authority by default.

Querying the SYSCAT.DBAUTH table can be used to determine which user has DBADM authority:


    db2 " SELECT DISTINCT GRANTEETYPE, GRANTEE, DBADMAUTH from SYSCAT.DBAUTH"

If the user exists on the server, have the user grant the DBADM authority to the instance owner. If not determine which user has SECADM authority by issuing the following command:

    db2 " SELECT DISTINCT GRANTEETYPE, GRANTEE, SECURITYADMAUTH from SYSCAT.DBAUTH"

Then grant the DBADM authority to the instance owner using the following command:

    db2 "GRANT DBADM ON DATABASE TO USER <USERID>"

If the restore is on a different server where there are no DBADM or SECADM then starting DB2 V9.7 FP2 you can set the DB2_RESTORE_GRANT_ADMIN_AUTHORITIES registry variable BEFORE preforming the restore into a new database.

Example:


    db2stop

    db2set DB2_RESTORE_GRANT_ADMIN_AUTHORITIES=ON


    db2set -all | grep -i db2_restore_grant
    [i] DB2_RESTORE_GRANT_ADMIN_AUTHORITIES=ON
    db2start

Once you set this variable,SECADM, DBADM, DATAACCESS, and ACCESSCTRL authorities are granted to the user that issues the restore operation.

[{"Product":{"code":"SSEPGG","label":"Db2 for Linux, UNIX and Windows"},"Business Unit":{"code":"BU058","label":"IBM Infrastructure w\/TPS"},"Component":"Database Objects\/Config - Authorization\/Privilege","Platform":[{"code":"PF002","label":"AIX"},{"code":"PF010","label":"HP-UX"},{"code":"PF016","label":"Linux"},{"code":"PF027","label":"Solaris"},{"code":"PF033","label":"Windows"}],"Version":"9.7;9.5;9.1;10.1;10.5;11.1","Edition":"Advanced Enterprise Server;Enterprise Server;Express;Express-C;Workgroup Server","Line of Business":{"code":"LOB10","label":"Data and AI"}}]

Document Information

Modified date:
16 June 2018

UID

swg21568865