Greetings,
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.

Any help is greatly appreciated.

Daryl,

this topic was discussed many times on the DB2 ListServer.
Go to http://www.idugdb2-l.org/archives/db2-l.html

and search for "REVOKE SYSADM".

HTH,

Thank you RKrick! I went to the link you provided and got the information I needed!

Please can you share the information ?

Thanks

Sathyaram

I'd be happy to share what I found!

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.

Thanks for your time

Sathyaram