Results 1 to 6 of 6
  1. #1
    Join Date
    Jan 2004
    Posts
    1

    Question Unanswered: How to change authority SYSADM to DBADM

    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.

  2. #2
    Join Date
    Feb 2002
    Location
    Germany
    Posts
    141

    Re: How to change authority SYSADM to DBADM

    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,
    Rodney Krick

  3. #3
    Join Date
    Feb 2004
    Posts
    25

    Re: How to change authority SYSADM to DBADM

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

  4. #4
    Join Date
    Aug 2001
    Location
    UK
    Posts
    4,650

    Re: How to change authority SYSADM to DBADM

    Please can you share the information ?

    Thanks

    Sathyaram

    Originally posted by crisscross
    Thank you RKrick! I went to the link you provided and got the information I needed!
    Visit the new-look IDUG Website , register to gain access to the excellent content.

  5. #5
    Join Date
    Feb 2004
    Posts
    25

    Re: How to change authority SYSADM to DBADM

    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.

  6. #6
    Join Date
    Aug 2001
    Location
    UK
    Posts
    4,650

    Re: How to change authority SYSADM to DBADM

    Thanks for your time

    Sathyaram
    Visit the new-look IDUG Website , register to gain access to the excellent content.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •