Gotcha when restoring database with restrictive access !

Starting from DB2 V9.1, IBM added a parameter on the CREATE DATABASE command called RESTRICTIVE.   When this parameter is specified nobody other than the userid creating the database gets any implicit access to the database.  The database is automatically in a ‘locked down’ state.  This is great for businesses that don’t like giving access to their databases by default. As a production dba, I love it. Only the users that needs to access the database can be granted any access. Very controlled environment. I am sure most of the dbas out there would agree with me.

Restrictive doesn’t effect the SYSADMIN, DBADMIN, SECADMIN in anyway as long as they are in the right group. You can perform any functions within the database, within the same instance, until you decide to restore the database onto a different instance.  When you restore it to a different instance db2 does not automatically give the new instance owner or the userid/group the dbadmin privelege.  This means, you might lose admin privileges against that database. I am not sure whether its by design or a bug ?  I have opened a PMR with IBM to confirm.

What you can do is, login with a userid that has dbadmin or secadmin privilege on the original database and then give the new instance or new dbadmin user or group dbadmin group.

About rpillai

I am a technology enthusiasts and love to work with databases and other technology. Learning new things everyday and don't think the path ever ends ...
This entry was posted in database, db2, how to and tagged , , , , , . Bookmark the permalink.