Granting access to the Db2 catalog (required)

ZDT/Db2 is a "full-function" Db2® application, intended to provide access to every Db2 catalog table, including those catalog tables that may contain sensitive information. ZDT/Db2 uses dynamic SQL, issued against the Db2 catalog, as part of its processing. To make ZDT/Db2 available and to ensure correct and optimum operation, you must ensure that each ZDT/Db2 user has SELECT access against the Db2 catalog tables. This is a non-negotiable requirement.

ZDT/Db2 includes an option (Db2 Privileges utility) that enables information in the various *AUTH Db2 catalog tables to be viewed, and possibly changed. If your installation restricts access to the *AUTH tables, you can disable the Db2 Privileges utility entirely, and remove the need to grant SELECT access on the *AUTH tables at install time. You can disable the ZDT/Db2 Privileges utility for some Db2 systems, but not for others as required.

To disable access to the Db2 Privileges utility you need to:
  • Code AUTH_ACCESS=N in the HFM2SSDM macro entry for each Db2 system where no SELECT access to the *AUTH tables is allowed. See the parameter description AUTH_ACCESS for more information.
  • Remove or comment out references to the *AUTH tables in the sample job used to grant SELECT access on the Db2 catalog tables to ZDT/Db2 users. See Sample jobs to grant SELECT access on the Db2 catalog tables for additional information.

You can also further restrict access to some Db2 catalog tables - see Sample jobs to grant SELECT access on the Db2 catalog tables for additional information. ZDT/Db2 is designed to tolerate incomplete access to most Db2 catalog tables, although the functionality of the product is reduced when access to some Db2 catalog tables is reduced. Restricting access to certain key Db2 catalog tables renders the product inoperative - for an indicative list of the key tables and columns see the "minimal subset" sample members described below.

Db2 authorization configuration can only be achieved using Db2, or an external security server (or both). Since these are external products, only general guidance is provided here on the authorization and security issues associated with the use of ZDT/Db2. You must determine the best approach to providing the required level of access for ZDT/Db2 users, based on your installation's unique requirements. You must ensure SELECT access against the Db2 catalog tables is given for all Db2 systems that are accessible to ZDT/Db2 users.

ZDT/Db2 requires the use of DYNAMICRULES(RUN) when the ZDT/Db2 plan is bound. See Binding Db2 (required) for further information. The effect of DYNAMICRULES(RUN) behavior is that ZDT/Db2 uses the run behavior for dynamic SQL statements, as summarized here:
  • Db2 uses the authid of the application process and the SQL authid (the value of the CURRENT SQLID special register) for authorization checking of dynamic SQL statements.
  • Db2 uses the authid of the application process and the SQL authid (the value of the CURRENT SQLID special register) as the implicit qualifier of table, view, index, and alias names.
  • Dynamic SQL statements use the values of application programming options that were specified during installation. The installation option USE FOR DYNAMICRULES has no effect.
  • GRANT, REVOKE, CREATE, ALTER, DROP, and RENAME statements can be executed dynamically.

The important point here is that the authid of the application process is used for authorization checking of dynamic SQL statements, including access to the Db2 catalog tables.

In a simple Db2 installation, the user's TSO logon ID is used as the Db2 authid, so SELECT access on the appropriate Db2 catalog tables needs to be granted to a list of Db2 authids which would be the same IDs as the list of the TSO logon IDs used by ZDT/Db2 users. You can achieve the same result by granting SELECT access on the Db2 catalog tables to PUBLIC.

In a more complex Db2 installation, an external security server may be used to control access to Db2 resources, and a Db2 authorization exit may be used to convert a user TSO logon ID into a primary and one or more secondary Db2 authids. See the Db2 Administration Guide for the appropriate version of Db2 for more information.

When an external security server is used to manage Db2 authorization, and a Db2 authorization exit is available, one approach to granting ZDT/Db2 users SELECT access to Db2 catalog tables is shown here:

When implemented, this approach can be used to ensure:

  1. SELECT access on the Db2 catalog tables is not granted to PUBLIC.
  2. Only the generic Db2 authid need have SELECT access on the Db2 catalog tables.
  3. The external security system can be used to validate and control which users are allowed access to the generic Db2 authid, and hence to ZDT/Db2 functionality.
  4. Individual ZDT/Db2 users may have no direct access - using a Db2 authid that is the same as their TSO logon ID - to the Db2 catalog tables.
  5. Only those users with access to the generic Db2 authid can run the ZDT/Db2 plans.