Auditing Db2 catalog activity

Catalog Activity report. Using this report is the most convenient way to obtain information about Db2 security changes. This report can list:

• DDL statements that change Db2 objects
• Db2 objects that were changed
• Date, time, and RBA/LRSN value when the changes were made
• Authorization IDs and correlation IDs associated with the changes

For example, to generate a Catalog Activity report that lists security changes, you would define a time frame that covers your period of interest, define your output as a Catalog Activity report, and define a filter that selects only the changes that affect security. For example:

DDL with comments (verbose DDL). A MIGRATE DDL file reproduces data definition language (DDL) activity that affects Db2 catalog tables. Log Master can include comments in the DDL file that provide URID-related information about each change, including the user ID, correlation ID, and plan name that made the change. This technique provides more detail than the Catalog Activity report.

For example, to audit Db2 catalog activity related to security, you can select only DDL statements that grant or revoke security. Define a time frame that covers your period of interest, define your output as a MIGRATE DDL file, and define a filter to select only grant and revoke activity in the Db2 catalog. For example:

Command with comments (verbose command). A MIGRATE command file reproduces bind, free, or rebind command activity that affects Db2 plans and packages. Log Master can include comments in the command file that provide URID-related information about each change, including the user ID, correlation ID, and plan name that made the change. This technique provides more detail than the Catalog Activity report.

For example, to audit Db2 command activity on a plan or package, you can generate only command output. Define a time frame that covers your period of interest and define your output as a MIGRATE command file.

Audit or Detail reports on Db2 catalog tables. To examine Db2 catalog activity, you can generate an Audit report or a Detail report on changes to specific Db2 catalog tables. (To determine the Db2 catalog tables that relate to a given type of Db2 catalog activity, refer to IBM’s Db2 technical documentation.)

For example, to generate an Audit or Detail report that lists security changes, define a time frame that covers your period of interest, then define your output as an Audit or Detail report. Define a filter to select the multiple catalog tables that relate to security (such as SYSIBM.SYSTABAUTH, SYSIBM.SYSCOLAUTH and so forth). Many changes to these tables relate to plan activity (BIND, REBIND, or FREE actions) and other processes that you might not be interested in. Define a filter to include all desired tables, and to exclude the unrelated activity from the report. You might need to add several filter predicates to select only the type of activity that you need. For example: