Ok, on to the intelligence gathering…migrated databases and the new authorities require some investigation.
Remember in the last post, I mentioned DATAACCESS and ACCESSCTRL authority? For your DBADM in a migrated db, those authorities come along for the ride. In other words, your DBADMs will inherit those for your migrated databases. Not that I personally recommend that they stay that way. I’d get the SECADM to revoke them once I got through the migration, but given that folks have job streams out there that depend on having a full contingent of authorities, proceed with caution. I cannot stress enough…don’t just remove authorities or privileges without a fall back plan if something breaks. Please test before and after scenarios…especially if you’re working in production !
So, what is up with the SECADM for a migrated db you ask? Obviously, you must have a SECADM with 9.7, but how that SECADM is determined is different depending on whether the “pre-migrated” database had a SECADM or not. If the “pre-migrated” database had a SECADM, then the db migration will maintain that. The trick is when there is no SECADM to be migrated. Then SECADM automatically goes to the user id doing the migration. The lesson here…CAREFULLY choose the id that does the migration.
SYSADM is a little tricky when migrating. The SYSADM GROUP does get DBADM, DATAACCESS and ACCESSCTRL, which is what I expect. The part I wasn't thinking about is that Group Privileges are not considered when creating MQTs, views, routines, packages, and/or triggers. That means that if you want a SYSADM user to perform those operations, they're going to need DBADM authority granted directly to their user ID.
Looks like our Security Intel work for the night is done. There are many more things to discover, but they can wait until tomorrow.
One final thing…a SHOUT OUT to all my friends who made some AWESOME suggestions about this blog…you know who you are. One of those suggestions was to let you know how often I expect to post here. I plan on 2-3 blog posts a week….the other nights of the week will hopefully involve actual sleep. But, you’ll have to forgive me if sometimes I post every night…there are just days…you know…when the desire to communicate is completely overwhelming.
As always, your emails are welcome
db2locksmith at securedb2.com