Two fundamental auditing questions are
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 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.
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.
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:
-STOP TRACE (AUDIT)CLASS (4,6) DEST (GTF)
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:
CREATE TABLE DB2CERT.TEST_TAKEN (CID SMALLINT NOT NULL .... IN DB2CTDB.CERTTS 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.