Tuesday, September 27, 2011

Su casa es mi casa: Restore in DB2 and obtaining SECADM and other privileges

One of the changes from DB2 9.5 to DB2 9.7 was the enhanced security, including extended abilities for SECADM and less or changed for SYSADM and DBADM. The idea was to introduce more security and prevent data theft. However, as we have learned during life, all good comes with some drawbacks, and so it is for security as well. It reduces what an administrator can do and cuts down on flexibility (remember how taking a flight was more than a decade ago?).

A common problem is with taking backups of a system and try to use them, e.g., for testing, on a different system. Users like SECADM need to be recreated in order to make things work on the system using the restored database. And that's why a "shortcut" was introduced in DB2 9.7 FP2. If the DB2 registry variable DB2_RESTORE_GRANT_ADMIN_AUTHORITIES is set to ON, then SECADM, DBADM, DATAACCESS, and ACCESSCTRL authorities are granted to the user performing the RESTORE DATABASE, typically one of the system administrators. Different methods of restore are supported.