Audit Trail Package

• example: ecommerce admin directory /admin/ecommerce/
• data model: examples in /doc/sql/ecommerce.sql
• procedures: /tcl/ad-audit-trail.tcl
• Audit site: /admin/ecommerce/audit-tables.tcl
• Display table: /admin/ecommerce/audit-table.tcl
• Display record: /admin/ecommerce/audit.tcl

The Big Picture

Steps for Auditing a Table

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 .tcl script that deletes rows from an audited table. It should call ad_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.

  
  ns_db dml $db "begin transaction"
  ns_db dml $db "delete from ec_products where product_id=$product_id"
  ad_audit_delete_row $db [list $product_id] [list product_id] ec_products_audit
  ns_db dml $db "end transaction"
  

• Insert a call to ad_audit_trail in an admin page to show the changes made to a key. Insert a call to ad_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

• 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.

Returns an audit trail across an entire table, (multiple keys).
• db Database handle.
• main_table_name Table that holds the main record. If sent an empty string as main_table_name, ad_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.

Returns an audit trail of a single key in a table.
• 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, ad_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.

Creates a row in the audit table to log when, who, and from what IP address a row was deleted.
• 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