How to revoke DBADM and clean up any related objects from DB2 LUW

Question

This question has started because of us taking copies of production backups and restoring them into lower environments (with scrambled data of course) for developers to practice and/or debug against.

We have IDs that are assigned DBADM,SECADM,DATAACCESS, and ACCESSCTRL (mainly the instance owner). When we restore into a lower environment, we end up needing to log on as the original instance owner and grant the same above authorities (DBADM, SECADM, DATAACCESS, and ACCESSCTRL) to the new target instance owner.

I figured it was not a good idea to leave the original ID in the database, so I attempted to revoke its privileges. Not long after my package rebinds started failing, as there are apparently packages tied to that original ID. Not knowing what they contained and if I could/should delete them or not (even though I'm thinking they are harmless to remove???) I ended up restoring the privileges to the original instance owner and just leaving it there.

That has always bothered me. An ID with the powers of DBADM,SECADM,DATAACCESS, and ACCESSCTRL shouldn't just be lying around. To me that is a security hole. To me the ID should be revoked and any other cleanup that needs to be performed should be executed to keep the database safe. The same would be true if say I would quit the company or my fellow DBAs would. We would want to revoke IDs and clean up and/or transfer ownership of objects to remove any security holes.

The problem is, I don't know what all needs to be cleaned up. I have done a bit of poking around on Google and IBM's documentation, and I can find nothing to suggest the steps that should normally be taken when removing such a high user from the system. Or even a user with BINDADD authority?

What do you all remove/revoke and in what order? Is there a way this can be scripted/automated? How do you know which packages you can remove? Are there other things that need to be moved/transferred?

Anyone else encounter this? Thoughts? Experiences?

Answer 1

There are several aspects that you might want to consider.

Firstly, you can assign proper authorities to the target instance owner during the restore automatically by using the DB2_RESTORE_GRANT_ADMIN_AUTHORITIES registry variable. If it's set to ON prior to the restore, the user performing the restore will get those authorities.

Secondly, most of the binding/compiling problems come from the fact that only the object owner, that is, the authorisation ID that created the object in the first place, is given permissions to perform certain operations on it. If you run DDL scripts in production as the instance owner, then this would be the owner ID of all objects after the restore, no matter the DB2_RESTORE_GRANT_ADMIN_AUTHORITIES setting. You could, of course, use the TRANSFER OWNERSHIP statement to change that, but in real life, with tens of thousands of objects in the database, this may not be practical.

To avoid the "secondly" part, in many organisations folks use a special "builder" authorisation ID, separate from the instance owner, to run DDL scripts. This ID will not be given any unnecessary privileges, can be tightly controlled, and, when existing in all environments, should be able to perform all necessary tasks without adding security exposure.