Authorization errors after restoring a database to V9.7 or higher instance
Why do I get authorization errors after restroing a database to a V9.7 or higher instance even though I am SYSADM?
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
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.
db2set -all | grep -i db2_restore_grant
Once you set this variable, SECADM, DBADM, DATAACCESS, and ACCESSCTRL authorities are granted to the user that issues the restore operation.
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