Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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. And 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?

share|improve this question

1 Answer 1

up vote 1 down vote accepted

There are several aspects that you might want to consider.

Firstly, you can assign proper authorities to the target instance owner during the backup 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.

share|improve this answer
    
Good idea on the builder ID. Hadn't thought of that. In your case do you still remove those packages? (We don't actually create our own packages, so all we ever bind are the ones that come with DB2.) –  Chris Aldrich Jul 26 '13 at 12:40
    
Remove system packages? I don't think it's a good idea. –  mustaccio Jul 26 '13 at 13:22
    
Can't they just get rebound with the new instance owner? I guess I'm looking for more than just technical answers. I am looking for process as well. –  Chris Aldrich Jul 26 '13 at 13:42
    
Of course the new instance owner can rebind them, however it will still use the original binder ID for authorisation checks and name resolution. –  mustaccio Jul 26 '13 at 13:53
    
Doesn't that cause issues though when your LDAP in a lower environment may not have that ID? –  Chris Aldrich Jul 26 '13 at 15:15

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.