Start of change

Revoking authorities without affecting certain dependent objects

If you revoke administrative authorities, such as the SYSADM authority, from an authorization ID that has installation SYSADM authority, you can avoid certain side effects.

About this task

When you revoke an authority from an authorization ID that does not have installation SYSADM authority, the following actions occur:

  • The REVOKE statement drops dependent views and materialized query tables that were created by using the revoked authority.
  • If the revoked authority was used to create a package, that package is invalidated.

Procedure

To avoid dropping dependent views or materialized query tables, or invalidating dependent packages when you revoke an authority from an authorization ID, follow this procedure.

  1. Determine which IDs currently hold installation SYSADM authority.
    Those IDs are the values of the SYSADM and SYSADM2 subsystem parameters. During this procedure, you will temporarily replace one of those values.
  2. Give installation SYSADM authority to the ID from which you want to revoke the authority. To do that, complete the following steps:
    1. Run the Db2 installation CLIST in UPDATE mode. In PROTECTION PANEL 2 (DSNTIPP1), replace the value of one of the following fields with the name of the authorization ID that has the authority that you want to revoke:
    2. After the installation CLIST completes, run job DSNTIJUZ to produce a new subsystem parameter load module.
    3. Issue the -SET SYSPARM command to load the subsystem parameter load module that you created in step 2.b

      Updating SYSADM or SYSADM2 requires installation SYSADM authority or SECADM authority. For example, if you are replacing the SYSADM subsystem parameter value, you need to run the -SET SYSPARM command using the authorization ID to which subsystem parameter SYSADM2 is set.

  3. Issue the REVOKE statement to revoke the authority from the authorization ID to which you assigned installation SYSADM authority in step 2.
    Because the authorization ID currently holds installation SYSADM authority, the cascading effects do not occur for the dependent views, materialized query tables, and packages that were created by using the revoked authority.
  4. Change the value of subsystem parameter SYSADM or SYSADM2 back to the value that it had in step 1.
End of change