Granting access to the Db2® catalog (required)

FM/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. FM/Db2 uses dynamic SQL, issued against the Db2® catalog, as part of its processing. To make FM/Db2 available and to ensure correct and optimum operation, you must ensure that each FM/Db2 user has SELECT access against the Db2® catalog tables. This is a non-negotiable requirement.

FM/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 FM/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 FMN2SSDM 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 FM/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. FM/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 FM/Db2. You must determine the best approach to providing the required level of access for FM/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 FM/Db2 users.

FM/Db2 requires the use of DYNAMICRULES(RUN) when the FM/Db2 plan is bound. See Binding Db2 (required) for further information. The effect of DYNAMICRULES(RUN) behavior is that FM/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 FM/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 FM/Db2 users SELECT access to Db2® catalog tables is shown here:

  • Determine an otherwise unused, generic Db2® authid for use by FM/Db2 users. For example, USER. You can select any Db2® authid as required.
  • Use the FM/Db2 sample jobs (see Sample jobs to grant SELECT access on the Db2 catalog tables) to grant SELECT access on the Db2® catalog tables to the selected Db2® authid. You must modify the sample jobs to achieve this.
  • Configure the security software and Db2® authorization exit to:
    1. Validate that the user has authority to use the USER Db2® authid.
    2. Convert the user's TSO logon ID into Db2® authid USER during the connection to Db2®. This means that authorization checking for dynamic SQL statements issued by FM/Db2 are made using Db2® authid USER, rather than the user TSO logon ID.
  • As an additional security measure, grant EXECUTE on the FM/Db2 plans to the generic Db2® authid, rather than PUBLIC.

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 FM/Db2 functionality.
  4. Individual FM/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 FM/Db2 plans.