Audit Trail Package

example: ecommerce admin directory <%= [ec_url] %>admin/
data model: examples in /doc/sql/ecommerce.sql
procedures: /tcl/ad-audit-trail
Audit site: <%= [ec_url] %>admin/audit-tables
Display table: <%= [ec_url] %>admin/audit-table
Display record: <%= [ec_url] %>admin/audit

The Big Picture

When you have more than one person updating information in a table, you want to record all the values of a row over time. This package gives you (1) a standard way of naming tables and triggers in Oracle, (2) two Tcl procedures (ec_audit_trail and ec_audit_trail_for_table) that helps you display the old values of a row, including highlighting particular changed columns, (3) a Tcl procedure (ec_audit_delete_row) that simplifies the logging of a deleted row, and (4) an example user interface ( audit-tables, audit-table, audit) to retrieve and display audit histories.

Steps for Auditing a Table

We record old information in a separate audit table (see the triggers chapter of SQL for Web Nerds for more explanation of this idea).

We distinguish between the on-line transaction processing (OLTP) tables that are used in the minute-by-minute operation of the server and the audit tables.

Here are the steps to add audit trails:

Decide which OLTP tables need auditing. Three fields must be added to each OLTP table to save information about who was making changes, what IP address they were using, and the date they made the changes.

create table ec_products (
        product_id              integer not null primary key,
        product_name            varchar(200),
        one_line_description    varchar(400),

        ...

        -- the user ID and IP address of the last modifier of the product
        last_modified           date not null,
        last_modifying_user     not null references users,
        modified_ip_address     varchar(20) not null
);

Create one audit table for each OLTP table that is being audited. By convention, this table should be named by adding an "_audit" suffix to the OLTP table name. The audit table has all the columns of the main table, with the same data types but no integrity constraints. Also add a flag to indicate that an audit entry is for a deleted row in the OLTP table.
```
create table ec_products_audit as
   select * from ec_products where 1 = 0;

alter table ec_products_audit add (
        delete_p    char(1) default('f') check (delete_p in ('t','f'))
);
```

Add one update trigger for each OLTP table.

create or replace trigger ec_products_audit_tr
before update or delete on ec_products
for each row
begin
        insert into ec_products_audit (
        product_id, product_name, 
        one_line_description, 

        ...

        last_modified,
        last_modifying_user, modified_ip_address
        ) values (
        :old.product_id, :old.product_name, 
        :old.one_line_description, 

        ...

        :old.last_modified,
        :old.last_modifying_user, :old.modified_ip_address
        );
end;
/
show errors

Note that it is not possible to automatically populate the audit table on deletion because we need the IP address of the deleting user.

Change any script that deletes rows from an audited table. It should call ec_audit_delete_row with args key list, column name list, and audit_table_name. This procedure calls ad_get_user_id and ns_conn peeraddr and records the user_id and IP address of the user deleting the row.
```
db_transaction {
db_dml unused "delete from ec_products where product_id=$product_id"
ec_audit_delete_row [list $product_id] [list product_id] ec_products_audit
}
```
Insert a call to ec_audit_trail in an admin page to show the changes made to a key. Insert a call to ec_audit_trail_for_table to show the changes made to an entire table over a specified period of time.
optionally define two views to provide "user friendly" audits. Look at the ticket tracker data model tables ticket_pretty and ticket_pretty_audit for an example. This has the benefit of decoding the meaningless integer ID's and highlighting potential data integrity violations.

Reference

Audit columns:

last_modified The date the row was last changed.
last_modifying_user The ID of the user who last changed the row.
modified_ip_address The IP Address the change request came from.
delete_p The true/false tag that indicates the audit table entry is recording information on the user who deleted a row.

Arguments for ec_audit_trail_for_table

db Database handle.
main_table_name Table that holds the main record. If sent an empty string as main_table_name, ec_audit_trail assumes that the audit_table_name has all current records.
audit_table_name Table that holds the audit records.
id_column Column name of the primary key in audit_table_name and main_table_name.
start_date (optional) ANSI standard time to begin viewing records.
end_date (optional) ANSI standard time to stop viewing records.
audit_url (optional) URL of a tcl page that would display the full audit history of an record. Form variables for that page: id id_column main_table_name and audit_table_name.
restore_url (optional) (future improvement) URL of a tcl page that would restore a given record to the main table. Form variables for the page: id id_column main_table_name audit_table_name and rowid.

Arguments for ec_audit_trail

db Database handle.
id_list List of ids representing the unique record you are processing.
audit_table_name Table that holds the audit records.
main_table_name Table that holds the main record. If sent an empty string as main_table_name, ec_audit_trail assumes that the audit_table_name has all current records.
id_column_list Column names of the unique key in audit_table_name and main_table_name.
columns_not_reported (optional) Tcl list of column names in audit_table_name and main_table that you don't want displayed.
start_date (optional) ANSI standard time to begin viewing records.
end_date (optional) ANSI standard time to stop viewing records.
restore_url (optional) (future improvement)URL of a tcl page that would restore a given record to the main table. Form variables for the page: id id_column main_table_name audit_table_name and rowid.

Arguments for ec_audit_delete_row

db Database handle.
id_list Tcl list of the ids specifying the unique record you are processing. (Or the list of ID's in the case of a map table.)
id_column_list Tcl list of the column names of the unique key in audit_table_name.
audit_table_name Table that holds the audit records.

Future Improvements

The ec_audit_trail and ec_audit_trail_for_table procedures could be extended to restore previous values. The restore_url would be a pointer to a script that could restore an old row to the main table. The script would need to query the data dictionary for the columns of the audit and main tables. It might also require the user to confirm if a current record would be overwritten by the restore option.