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
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 (
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
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
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?