Setting up selective row-level auditing

Auditing can be configured so that row-level events of only selected tables are recorded in the audit trail. Selective row-level auditing can compact audit records so that they are more manageable and potentially improve database server performance.

Before you begin

You must be a DBSSO to complete this task.

About this task

Procedure

  1. Run the onaudit command with the -R option.
  2. Designate the tables that you want to audit on the row level:
    1. For each existing table that you want to audit at the row level, run the ALTER TABLE statement with the ADD AUDIT clause.
    2. For each new table that you want to audit at the row level, run the CREATE TABLE statement with the WITH AUDIT clause.

Example

The following code examples and descriptions illustrate how to enable selective row-level auditing.

The onaudit -R 1 command enables selective row-level auditing, and the onaudit -c command displays the audit configuration for verification. The audit configuration information indicates that the ADTROWS parameter is correctly set to 1.

$ onaudit -R 1
$ onaudit -c
Onaudit -- Audit Subsystem Configuration Utility

Current audit system configuration:
    ADTMODE    = 1
    ADTERR     = 0
    ADTPATH    = /usr2/support/chunks/IDS1170FC1B1
    ADTSIZE    = 50000
    Audit file = 0
    ADTROWS    = 1

The onaudit -a -u _default -e +DLRW,INRW,RDRW,UPRW command creates the user audit mask _default and sets the granularity to Delete Row, Insert Row, Read Row, and Update Row audit events. The onaudit -o -y command displays the audit mask for verification.


$ onaudit -a -u _default -e +DLRW,INRW,RDRW,UPRW
$ onaudit -o -y
Onaudit -- Audit Subsystem Configuration Utility

_default                                -       DLRW,INRW,RDRW,UPRW

In the following part, the table state is flagged for selective row-level auditing and values are inserted to test whether the action is captured in the audit records.


$ dbaccess stores_demo -
Database selected.
> ALTER TABLE state ADD AUDIT;
Table altered.

> INSERT INTO state VALUES ('FR', 'France');

1 row(s) inserted.

Finally, the onshowaudit command is run to display the audit record. The results indicate that selective row-level auditing is functioning.