Audit Trails with Journals
In addition to using journals for recovery, you can use journals to produce audit trails of changes to a database. You must be the DBA for the database or have the security privilege to perform an audit on a database.
Audit your database periodically to verify that your journals are correct.
Tools for Auditing a Database
The audit database operation is performed using the auditdb command. For complete details, see the Command Reference Guide.
In VDBA, this operation is performed using the Audit Database dialog, invoked by the Operations Audit menu command. For the detailed steps for performing this procedure, see the Procedures section of online help.
Understanding the Audit Operation
The auditdb command lets you produce a listing or file of changes made to journaled tables after the last checkpoint. This listing may not include all changes that have been made after the last checkpoint for the following reasons:
• Auditdb does not exclusively lock the database, other users can complete a transaction while the audit is running.
• If other users are using the database when you perform an audit, a completed transaction may not have been moved to the journal files.
The audit database operation scans journal files twice. A prescan is performed to filter out undesired information (for example, aborted transaction data). The second scan outputs journal records of interest. To improve program performance, the -e option (Before edit control value in VDBA) terminates both scans when an End Transaction record is found that has a time later than that specified.
The -inconsistent option lets you view journals that the database has marked as inconsistent.
Note: The audit database operation can still fail if core catalogs are inconsistent.
The -wait option makes the audit wait until journals are current. “Current” in this context means either of the following:
• No further archiving is required on the database.
• The archiver has copied all log file information up to the log file end-of-file when the audit database request was initiated.
Note: If a large amount of unarchived information remains in the log file when this request is initiated, a significant delay in processing can occur.
How to Load an Audit Trail as a Table
To make querying the audit data easier, you can create an audit trail as a file in your current directory and load the file into a table in your database.
To do this, follow these steps:
1. When you create the audit trail, use the -file flag to create an audit trail file in the current directory.
Note: You must have first specified at least one table.
In the following example, auditdb extracts a record of the changes to the employee table from the journal for the demodb database. It places the changes in the current directory in a file named empaudit.trl.
auditdb -table=employee -file=empaudit.trl demodb
2. To copy the file into a database table, create a table to hold the audit trail data.
When creating the table, include the audit trail and employee table columns shown below. Enter the audit trail columns before the table’s columns, in the order shown. If you do not, the copy operation can fail when you try to copy the audit trail data into the table.
Note: A table created to contain audit trail data must define the date column as ingresdate data type.
In the following example, a table named empaudit is created to hold the data from the empaudit.trl file:
create table empaudit
(date ingresdate not null with default,
username char(32) not null with default,
operation char(8) not null with default,
tranid1 integer not null with default,
tranid2 integer not null with default,
table_id1 integer not null with default,
table_id2 integer not null with default,
name varchar(20),
age integer,
salary money,
dname varchar(10),
manager varchar(20));
The last five columns are from the employee table.
3. Use the COPY statement to load the new table with the data from the file from Step 2.
In the following example, the data in the empaudit.trl file is copied to the empaudit table:
Windows:
copy empaudit() from 'C:\users\joe\empaudit.trl';
UNIX:
copy empaudit() from '/usr/joe/empaudit.trl';
VMS:
copy empaudit() from '[usr.joe]empaudit.trl';
The table created from the audit trail (in this example, the empaudit table) contains:
• A row for each row added to the employee table
• A row for each row removed
• Two rows for each update: one showing the row before the update and the other showing the row after the update