Unanswered: How to change authority SYSADM to DBADM
We have DB2 version 7 on Z/OS. We have several userids that have SYSADM privileges that need to be revoked from these userids. We want to change these from SYSADM to DBADM authority.
We need to know the "cascading" effect of Revoking SYSADM from these users. This includes if objects created by these userids will be dropped and if authorities will be revoked, binds or grants lost, etc. Also if you know of any other concerns.
Hopefully someone has done this before and has a procedure for doing this type of a change? Ideally, including the SQL to do this.
As a result of Sarbanes-Oxley legislation here in the states, we are required to clean up DB2 authority granted to users. We have 4 obsolete SYSADM IDs, and I was afraid of the cascade effect a simple revoke could cause. Here's what I found on the link:
Revoking SYSADM from Install SYSADM: If you REVOKE SYSADM from the Install SYSADM user id, DB2 will not cascade the revoke. This feature allows you to change the Install SYSADM user id or delete extraneous SYSADM user ids.
To change the Install SYSADM user id:
1. Select the new Install SYSADM user id
2. GRANT it SYSADM authority
3. REVOKE SYSADM authority from the old Install SYSADM user id
4. Update the SYSADM ADMIN field on the DSNTIPB panel or in DSNZPARM to the new Install SYSADM user id
To delete an extraneous SYSADM user id: (this is what I needed!!)
1. Write down the current Install SYSADM
***** here's the beef *****
2. Make the SYSADM user id you wish to delete an Install SYSADM, as
explained in 4
3. REVOKE SYSADM authority from it using another SYSADM user id
4. Change the Install SYSADM user id back to its original value
This solution implies that the DB2 subsystem has to be bounced twice, once to make the target id install sysadm (after reassembling ZPARM) and once to restore the real install sysadm (after reassembling ZPARM...again).
More info is available from the link to the DB2 list server that RKrick provided.