DB2 Audit Policy not producing EXECUTE data

auditdb2db2-luw

I'm trying to set up an Audit facility on a DB2 AWSE V10.5 FP5 database

running on Red Hat Linux Server 6.8.

The definition for the policy is :

ALTER
AUDIT POLICY Failures 
    CATEGORIES 
        Audit STATUS Failure, 
        Checking STATUS Failure, 
        Context STATUS Failure, 
        ObjMaint STATUS Failure, 
        SecMaint STATUS Failure, 
        **execute with data STATUS Failure**,
        Validate STATUS Failure 
    ERROR TYPE NORMAL ;

This is producing data for all the categories, except EXECUTE.

My test script includes statements that fail with SQL0204, SQL0206, etc. but no data is extracted into the execute.del file in the audit output.

Anyone have an idea what I'm missing?

Best Answer

A note in the manual explains why these statements do not make it into the audit log:

The preparation of a statement is not considered part of the execution. Most authorization checks are performed at prepare time (for example, SELECT privilege). This means that statements that fail during prepare due to authorization errors do not generate EXECUTE events.

SQL0204N and SQL0206N, both of which complain about invalid object names, are raised during the statement parse phase, that is, before execution starts. Errors raised after execution starts, such as SQL0911N (lock timeout), will be audited.

Related Solutions

Data – Changes Audit Trail

There are many requirements for capturing changed data. I list only three common ones below.

Audits (HIPAA, SAS70, etc.)...one may need to audit SQL Server logins as well as attribute data changes to specific users
EDI...some insurance carriers require one to send to them current and previous data when a policy is altered, say, due to a life event. One may need to send one's previous coverage together with the new coverage. This is a good candidate for using SQL Server Change Data Capture (CDC).
ETL for DWs...require data change capture in order to add new data to facts and dimensions as well as process updates to slowly changing dimensions. Usually, something like SQL Server's Change Tracking would be right for this, since it is a "lighter" tool than CDC.

Db2 – Applying audit policy on multiple auditing subject or object

Whilst the audit policy created by CREATE AUDIT POLICY is database-wide, it is applied to individual objects or roles by the AUDIT statement. For example, to audit statement executions (successful or otherwise) against a particular table:

CREATE AUDIT POLICY allexec CATEGORIES EXECUTE STATUS BOTH;
COMMIT;
AUDIT TABLE mytable USING POLICY allexec;
COMMIT;

To audit statements executed by a particular role:

AUDIT ROLE somerole USING POLICY allexec;
COMMIT;

The reason for all the COMMITs is that, as the docs note, "[a]n AUDIT-exclusive SQL statement must be followed by a COMMIT or ROLLBACK statement...". See audit policies for more information.

Best Answer

Related Solutions

Data – Changes Audit Trail

Db2 – Applying audit policy on multiple auditing subject or object

Related Question