The EXECUTE category for auditing SQL statements
Use the EXECUTE category to accurately track the SQL statements that are issued by a user. In Version 9.5 and earlier releases, you had to use the CONTEXT category to find this information.
As part of a comprehensive security policy, a company can require the ability to retroactively go back a set number of years and analyze the effects of any particular request against certain tables in their database. To do this, a company must institute a policy of archiving their weekly backups and associated log files such that they can reconstitute the database for any chosen moment in time. Also required, is sufficient database audit information captured about every request made against the database to allow, at any future time, the replay and analysis of any request against the relevant, restored database. This requirement can cover both static and dynamic SQL statements.
This EXECUTE category captures the SQL statement text as well as the compilation environment and other values that are needed to replay the statement at a later date. For example, replaying the statement can show you exactly which rows a SELECT statement returned. In order to re-run a statement, the database tables must first be restored to their state when the statement was issued.
Statement Value Index, Statement Value Type and Statement Value Data fields may be repeated for a given execute record. For the report format generated by the extraction, each record lists multiple values. For the delimited file format, multiple rows are used. The first row has an event type of STATEMENT and no values. Following rows have an event type of DATA, with one row for each data value associated with the SQL statement. You can use the event correlator and application ID fields to link STATEMENT and DATA rows together. The columns Statement Text, Statement Isolation Level, and Compilation Environment Description are not present in the DATA events.
The statement text and input data values that are audited are converted into the database code page when they are stored on disk (all audited fields are stored in the database code page). No error is returned if the code page of the input data is not compatible with the database code page; the unconverted data will be logged instead. Because each database has it's own audit log, databases having different code pages does not cause a problem.
ROLLBACK and COMMIT are audited when executed by the application, and also when issued implicitly as part of another command, such as BIND.
After an EXECUTE event has been audited due to access to an audited table, all statements that affect which other statements are executed within a unit of work, are audited. These statements are COMMIT, ROLLBACK, ROLLBACK TO SAVEPOINT and SAVEPOINT.
Savepoint ID field
|INSERT INTO T1 VALUES (3)||1|
|INSERT INTO T1 VALUES (5)||2|
|INSERT INTO T1 VALUES (6)||3|
|ROLLBACK TO SAVEPOINT A||2|
WITH DATA option
Not all input values are audited when you specify the WITH DATA option. LOB, LONG, XML and structured type parameters appear as NULL.
Date, time, and timestamp fields are recorded in ISO format.
If WITH DATA is specified in one policy, but WITHOUT DATA is specified in another policy associated with objects involved in the execution of the SQL statement, then WITH DATA takes precedence and data is audited for that particular statement. For example, if the audit policy associated with a user specifies WITHOUT DATA, but the policy associated with a table specifies WITH DATA, when that user accesses that table, the input data used for the statement is audited.
You are not able to determine which rows were modified on a positioned-update or positioned-delete statement. Only the execution of the underlying SELECT statement is logged, not the individual FETCH. It is not possible from the EXECUTE record to determine which row the cursor is on when the statement is issued. When replaying the statement at a later time, it is only possible to issue the SELECT statement to see what range of rows may have been affected.
Example of replaying past activities
Consider in this example that as part of their comprehensive security policy, a company requires that they retain the ability to retroactively go back up to seven years to analyze the effects of any particular request against certain tables in their database. To do this, they institute a policy of archiving their weekly backups and associated log files such that they can reconstitute the database for any chosen moment in time. They require that the database audit capture sufficient information about every request made against the database to allow the replay and analysis of any request against the relevant, restored database. This requirement covers both static and dynamic SQL statements.
This example shows the audit policy that must be in place at the time the SQL statement is issued, and the steps to archive the audit logs and later to extract and analyze them.
- Create an audit policy that audits the EXECUTE category and apply
this policy to the database:
CREATE AUDIT POLICY STATEMENTS CATEGORIES EXECUTE WITH DATA STATUS BOTH ERROR TYPE AUDIT COMMIT AUDIT DATABASE USING POLICY STATEMENTS COMMIT
- Regularly archive the audit log to create an archive copy.
The following statement should be run by the security administrator, or a user to whom they grant EXECUTE privilege for the SYSPROC.AUDIT_ARCHIVE stored procedure, on a regular basis, for example, once a week or once a day, depending on the amount of data logged. These archived files can be kept for whatever period is required. The AUDIT_ARCHIVE procedure is called with two input parameters: the path to the archive directory and -2, to indicate that the archive should be run on all members:
CALL SYSPROC.AUDIT_ARCHIVE( '/auditarchive', -2 )
- The security administrator, or a user to whom
they grant EXECUTE privilege for the SYSPROC.AUDIT_LIST_LOGS table
function, uses AUDIT_LIST_LOGS to examine all of the available audit
logs from April 2006, to determine which logs may contain the necessary
SELECT FILE FROM TABLE(SYSPROC.AUDIT_LIST_LOGS('/auditarchive')) AS T WHERE FILE LIKE 'db2audit.dbname.log.0.200604%' FILE -------------------------------------- ... db2audit.dbname.log.0.20060418235612 db2audit.dbname.log.0.20060419234937 db2audit.dbname.log.0.20060420235128
- From this output, the security administrator observes that the necessary logs should be in one
file: db2audit.dbname.log.20060419234937. The timestamp shows this file was
archived at the end of the day for the day the auditors want to see.
The security administrator, or a user to whom they grant EXECUTE privilege for the SYSPROC.AUDIT_DELIM_EXTRACT stored procedure, uses this filename as input to AUDIT_DELIM_EXTRACT to extract the audit data into delimited files. The audit data in these files can be loaded into Db2® database tables, where it can be analyzed to find the particular statement the auditors are interested in. Even though the auditors are only interested in a single SQL statement, multiple statements from the unit of work may need to be examined in case they have any impact on the statement of interest.
- In order to replay the statement, the security administrator must
take the following actions:
- Determine the exact statement to be issued from the audit record.
- Determine the user who issued the statement from the audit record.
- Re-create the exact permissions of the user at the time they issued the statement, including any LBAC protection.
- Reproduce the compilation environment, by using the compilation environment column in the audit record in combination with the SET COMPILATION ENVIRONMENT statement.
- Restore the database to its exact state at the time the statement was issued.