Skip to content
View All / Sathy’s SECADM Secrets

Sathy’s SECADM Secrets

In the recent post – SECADM Musings, Iqbal discussed the RESTORE scenario in 9.7 where the privileges will be ‘lost’ when restoring the database to a different instance and a work around for the situation. There are other ways to tackle the same issue and here are two of them.

 

a) Consider that a database SAMPLE from db2inst1 was restored to db2inst2

 

On db2inst1, catalog the database

 

db2 catalog tcpip node node1 remote inst2host server inst2_port
db2 catalog database sample as samp1 at node node1

 

On db2inst2 instance, set the AUTHENTICATION type to CLIENT.

 

db2 update dbm cfg using authentication client

 

You will now be able to connect to the SAMPLE database on db2inst2 from db2inst1 without providing a password. GRANT the privileges as desired.

db2 connect to samp1
db2 grant SECADM, DBADM, DATAACCESS, ACCESSCTRL on database to user db2inst2

Now all is set.

Don’t forget to uncatalog the db and node on db2inst1 and to change the AUTHENTICATION back to the original value (usually SERVER) on db2inst2.

 

b) From DB2 9.7 FP2, a registry variable DB2_RESTORE_GRANT_ADMIN_AUTHORITIES is provided. Set this to ON.

 

db2set DB2_RESTORE_GRANT_ADMIN_AUTHORITIES=ON

 

When set, the restore operation(actually, the first connection after the RESTORE) automatically grants SECADM, DBADM, DATAACCESS, and ACCESSCTRL privileges on the database to the user doing the RESTORE.This works for the classic RESTORE, Flash copy restores as well as ACS Restores. When DB2_WORKLOAD is set to SAP, this registry variable is automatically set.

 

Planning to give this option a try???

Before you do, take note of the following known APARs.(DB2 9.7 FP3a, at the time of writing)

(I) IC73579 : If the restoring user (db2inst2 in our case) already has a database level privilege on the database, the automatic grant fails. Use another technique to grant privileges.

(II) IC75235 When a database backup is done immediately following the RESTORE, the backup command fails. To avoid the error, connect to the database and then disconnect before doing a backup.

 

(III) IC71864: In HADR environment, TAKEOVER may fail if the restore was done into a non existing database to setup the standby database.

 

Be particularly careful in a SAP environment. Instead of unsetting only this variable before doing a restore, unset the DB2_WORKLOAD command, complete the restore tasks and s et DB2_WORKLOAD variable back to SAP.

 

Follow Sathyaram on twitter @sathyaram_s  https://twitter.com/sathyaram_s