Sample jobs to grant SELECT access on the Db2® catalog tables

These jobs are provided in FMN.SFMNSAM1 to grant SELECT access on the Db2® catalog tables to PUBLIC:

  1. FMN2GSC0 (Db2® V10)
  2. FMN2GSC1 (Db2® V11)
  3. FMN2GSC2 (Db2® V12)
  4. FMN2GVW0 (Db2® V10)
  5. FMN2GVW1 (Db2® V11)
  6. FMN2GVW2 (Db2® V12)
  7. FMN2GV20 (Db2® V10)
  8. FMN2GV21 (Db2® V11)
  9. FMN2GV22 (Db2® V12)

You should review the GRANT statements in these members whenever you install a later version of Db2®. New versions of Db2® may include new or updated catalog tables, and FM/Db2 may require access to these new or updated tables.

Sample jobs FMN2GSCn grant SELECT access on the Db2® catalog tables to PUBLIC. This is the simplest method of ensuring FM/Db2 users have access to the Db2® catalog tables. It is also the least secure.

Sample jobs FMN2GVWn
  • Create views on the Db2® catalog tables with a different owner, but the same name as the Db2® catalog tables.
  • Grant access on the newly created views to PUBLIC.

If you intend to use these views, you must set the CATOWNER parameter in the FMN2POPI to match the value of the owner specified in these sample jobs.

For information about FMN2POPI, see FMN2POPI. For information about changing the options in FMN2POPT, see Changing the default options.

Sample jobs FMN2GV2n
  • Create views on the Db2® catalog tables with a different owner, but the same name as the Db2® catalog tables.
  • These views include all the Db2® catalog tables normally accessed by FM/Db2. Some Db2® catalog tables are not referenced and therefore no views for these Db2® catalog tables are defined.
  • Not every column of the referenced Db2® catalog tables is included in the views. The columns that are included are required for the correct functioning of FM/Db2. Omitting additional columns may impact the functioning of FM/Db2, in some cases rendering the product unusable.
  • Grant access on the newly created views to PUBLIC.

If you intend to use these views, you must set the CATOWNER parameter in the FMN2POPI to match the value of the owner specified in these sample jobs.

For information about FMN2POPI, see FMN2POPI. For information about changing the options in FMN2POPT, see Changing the default options.

Note:
  1. If you create views of the Db2® catalog tables and change the CATOWNER parameter in the FMN2POPI macro, the following example outlines how FM/Db2 attempts to access the Db2® catalog. For this example, it is assumed that the owner of the views (CATOWNER value) is SYSIBMV.
    • FM/Db2 generates an SQL statement like SELECT * FROM SYSIBMV.SYSTABLES.
    • FM/Db2 attempts to run the SQL statement.
    • If this attempt fails, FM/Db2 does NOT attempt to access the Db2® catalog using, for example, SELECT * FROM SYSIBM.SYSTABLES.
    • In summary, FM/Db2 attempts to access the Db2® catalog once only, using the specified CATOWNER value as the owner for the relevant catalog tables.
  2. If you do not want to grant SELECT access against the Db2® catalog tables or views to all users (that is, to PUBLIC), you can customize the GRANT statements in the sample jobs to list individual user IDs.
  3. In situations where you do not want to expose all the information in the Db2® catalog tables, you can define views (a "minimal subset") that refer only to the Db2® catalog tables, and columns within those tables, that are needed by FM/Db2. Sample jobs that do this are provided in FMN.SFMNSAM1:
    • FMN2GV20 for Db2® version 10 subsystems.
    • FMN2GV21 for Db2® version 11 subsystems.
    • FMN2GV22 for Db2® version 12 subsystems.
  4. The view definitions in these sample jobs refer only to the columns needed by FM/Db2, and not all catalog tables are included. There are consequences to using views that include only some columns of the actual Db2® catalog table. For example, option 3.4 of FM/Db2 provides an option to show all columns of certain catalog tables. The columns shown will reflect the columns of the view and not necessarily all columns of the catalog table, reducing the usefulness of the product.
  5. If your installation security standards prevent access to the SYSIBM.*AUTH tables, you can omit these views, or create the views, and then not grant access. This has the effect of disabling the Object Privileges utility (Option 3.5).
  6. You can define different views for different Db2® subsystems. For example, you could define the views in FMN2GVW0 against a development system, but the views in FMN2GV20 against a production system. You must ensure, however, that the owner (CATOWNER value) used is consistent across all Db2® subsystems accessed by FM/Db2.
  7. When you define views on the Db2® catalog table for use by FM/Db2, you cannot change the object name of the catalog table; you can only change the owner. For example, defining a view SYSIBMV.SYSTABLES on the table SYSIBM.SYSTABLES is valid. However, defining a view SYSIBM.TABLES on the table SYSIBM.SYSTABLES is invalid. You cannot rename the columns of the catalog tables in the views you define for use by FM/Db2. The owner (CATOWNER value) of the views you create must be the same for every view.