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.
Related Information
Was this topic helpful?
Document Information
Modified date:
16 June 2018
UID
swg21568865