IBM Support

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

Technote (FAQ)


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.

Related information

System environment variables
Database administration authority (DBADM)
Authorization errors

Document information

More support for: DB2 for Linux, UNIX and Windows
Database Objects/Config - Authorization/Privilege

Software version: 9.1, 9.5, 9.7, 10.1, 10.5, 11.1

Operating system(s): AIX, HP-UX, Linux, Solaris, Windows

Software edition: Advanced Enterprise Server, Enterprise Server, Express, Express-C, Workgroup Server

Reference #: 1568865

Modified date: 15 September 2016