Two fundamental auditing questions are

  1. Who is privileged to access what objects?

  2. Who has accessed the data?

Answers to the first question are found in the DB2 catalog, which is a primary audit trail for the DB2 subsystem. Most of the catalog tables describe the DB2 objects, such as tables, views, table spaces, packages, and plans. Several other tablesthose with the characters AUTH in their nameshold records of every grant of a privilege or authority on different types of object. Every record of a grant contains the name of the object, the ID that received the privilege, the ID that granted it, the time of the grant, and other information. You can retrieve data from catalog tables by writing SQL queries.

The audit trace is another primary audit trail for DB2. The trace can record changes in authorization IDs for a security audit and changes that are made to the structure of data, such as dropping a table, or data values, such as updating or inserting records, for an audit of data access. The trace also allows you to audit access attempts by unauthorized IDs, the results of GRANT and REVOKE statements, the mapping of Kerberos security tickets to RACF IDs, and other activities of interest to auditors.

The DB2 audit trace can tell who has accessed data. When started, the audit trace creates records of actions of certain types and sends them to a named destination, usually SMF.

Some of the types of information that can be obtained by the audit trace records are

  • The ID that initiated the activity

  • The location of the ID that initiated the activity, that is, whether the access was initiated from a remote location

  • The type of activity and the time the activity occurred

  • The DB2 objects that were affected

  • Whether access was denied

  • Who owns a particular plan and package


The trace can also determine which primary ID is responsible for the action of a secondary ID, when that information might not appear in the catalog.

Whether it is from a remote location or the local DB2, the request can be audited. The authorization ID on a trace record for a remote request is the ID that is the final result of any outbound translation (on remote system), inbound translation (on local system), or activity of an authorization exit routine; that is, it is the same ID to which you have granted access privileges for your data. Requests from your location to a remote DB2 are audited only if an audit trace is active at the remote location. The output from the trace appears only in the records at that location where the data resides.

Trace Details

The audit trace does not record everything. The changed data is recorded in the DB2 log. If an agent or a transaction accesses a table more than once in a single unit of recovery, only the first access is recorded, and then only if the audit trace is started for the appropriate class of events.

Some utilities are not audited. The first access of a table by LOAD is audited, but access by COPY, RECOVER, and REPAIR is not. Access by standalone utilities, such as DSN1CHKR and DSN1PRNT, is not audited. For more information on these utilities, refer to Chapter 7.


Everything comes at a cost. Auditing does have some overhead and can produce more data than necessary.

When the trace is started, the events to audit are chosen by giving one or more numbers to identify classes of events. Table 3-5 shows the available classes and the events they include. For additional information on trace classes, refer to Chapter 18.

Table 3-5. Classes Available for Audit and Events Included


Events Traced


Access attempts that DB2 denies because of inadequate authorization. This class is the default.


Explicit GRANT and REVOKE statements and their results. The class does not include implicit grants and revokes.


CREATE, ALTER, and DROP operations affecting audited tables and their results. The class includes the dropping of a table caused by DROP TABLESPACE or DROP DATABASE and the creation of a table with AUDIT CHANGES or AUDIT ALL. ALTER TABLE statements are audited only when they change the AUDIT option for the table.


Changes to audited tables. Only the first attempt to change a table, within a unit of recovery, is recorded. (If the agent or the transaction issues more than one COMMIT statement, the number of audit records increases accordingly.) The changed data is not recorded; only the attempt to make a change is. If the change is not successful and is rolled back, the audit record remains; it is not deleted. This class includes access by the LOAD utility. Accesses that are caused by attempted deletions from a parent table to a dependent table are also audited. The audit record is written even if the delete rule is RESTRICT, which prevents the deletion from the parent table. The audit record is also written when the rule is CASCADE or SET NULL, which can result in deletions cascading to the dependent table.


All read accesses to tables that are identified as AUDIT ALL. As in class 4, only the first access within a DB2 unit of recovery is recorded, and references to a parent table are audited.


The bind of static and dynamic SQL statements of the following types:

  • INSERT, UPDATE, DELETE, CREATE VIEW, and LOCK TABLE statements for audited tables. Except for the values of host variables, the entire SQL statement is contained in the audit record.

  • SELECT statements to tables that are identified as AUDIT ALL. Except for the values of host variables, the entire SQL statement is contained in the audit record.


Assignment or change of an authorization ID, through an exit routine (default or user written) or a SET CURRENT SQLID statement, through either an outbound or an inbound authorization ID translation or because the ID is being mapped to an RACF ID from a Kerberos security ticket.


The start of a utility job and the end of each phase of the utility.


Various types of records that are written to IFCID 0146 by the IFI WRITE function.

Auditing Specific IDs

The audit trace can be started for a particular plan name, a particular primary authorization ID, or a combination of both. Having audit traces on at all times can be useful for tracking the activity of IDs with SYSADM authority, for example, because they have complete access to every table. If you have a network of DB2 subsystems, you might need to trace multiple authorization IDs for those users whose primary authorization ID may be translated several times.

Starting/Stopping the Trace

In order to start the audit trace, execute the following command:

 -START TRACE (AUDIT)CLASS (4,6) DEST (GTF)LOCATION (*) COMMENT ('Trace data changes; include text of dynamic DML statements.') 

This example starts a trace that audits data changes and includes the text of any dynamic SQL. In order to stop this trace, simply issue the following command:


This command simply stops the last trace started. If more than one trace is executing, the DISPLAY TRACE command can be using to identify a particular trace by a number. For more information on DB2 commands, refer to Chapter 2.


The audit trace can be set up to start automatically when DB2 is started. This can be controlled by options on the panel DSNTIPN when DB2 is installed. This option allows you to set AUDIT TRACE to NO, YES, or a list of audit trace classes.

Auditing a Table

In order for the audit trace to be effective, you must first choose whether to audit the activity on a table, specifying an option of the CREATE and ALTER statements. The following example shows how to indicate that you wish to audit changes:


The options are AUDIT CHANGES|ALL|NONE. The default is NONE, or no auditing.In order to turn auditing off at the table level, you simply do an ALTER with AUDIT NONE.


You cannot audit auxiliary tables or catalog tables.

DB2 for z. OS Version 8 DBA Certification Guide
DB2 for z/OS Version 8 DBA Certification Guide
ISBN: 0131491202
EAN: 2147483647
Year: 2003
Pages: 175
Authors: Susan Lawson © 2008-2017.
If you may any questions please contact us: