Using the Catalog Synchronization Utility › Implementation Considerations › How Does Synchronization Affect DB2? › Resource Ownership › Views

Views

To create a view, a user must have one or more DB2 privileges, such as SELECT or SYSADM. If you REVOKE the privilege that was used to create a view, that view is dropped unless:

• Another GRANT statement remains that supplies the necessary privilege
• This second GRANT statement was permitted to the user before the view was created.

By default, CADB2SY2 will not generate revokes that will cause views to be dropped. The VIEW ACTION(WARN) control statement causes the CADB2SGS report to list the revoked privilege and all views that are dropped if the revoke is generated and executed. If you specify the VIEW ACTION(DROP) or VIEW ACTION(WARN,DROP) control statement, the REVOKE is generated. When CADB2SY3 processes it, the views are then dropped and must be rebuilt.

By default, CADB2SY2 does not generate revokes for the SYSADM, SYSCTRL, system DBADM, and DBADM administrative authorities. This is due to the number of catalog queries required to determine which, if any, views are dropped if the revoke statement was generated and executed. For example, user USER01 has SYSADM authority and grants DBADM authority on database DBS1 to USER02 with the grant option. USER02 then grants USER03 DBADM authority on database DBS1 and USER03 grants USER04 the SELECT privilege on table PAY.TABLE1 in database DBS1. USER04 then creates view USER04.PAYTABLE1 using base table PAY.TABLE1. Because of DB2 rules and the cascade revoke, revoking SYSADM from USER01 causes DB2 to drop the view USER04.PAYTABLE1.

To instruct the catalog synchronization utility to generate REVOKEs for administrative authorities that do not result in dropping a VIEW or VIEWs, specify:

VIEW ACTION(REVOKEADMIN)

To:

• Generate REVOKEs for administrative authorities that do not result in the dropping of VIEWs
• List REVOKEs that were not generated because the REVOKE would result in DB2 dropping views
• List the VIEWs that would be dropped if the REVOKE was generated and executed

Specify:

VIEW ACTION(REVOKEADMIN,WARN)

To generate REVOKEs for all administrative authorities, regardless of the impact on VIEWs, specify:

VIEW ACTION(REVOKEADMIN,DROP)

To generate REVOKEs for all administrative authorities and to get a list of VIEWs that are affected, specify:

VIEW ACTION(REVOKEADMIN,WARN,DROP)