Index: openacs-4/packages/ecommerce/www/doc/audit.adp =================================================================== RCS file: /usr/local/cvsroot/openacs-4/packages/ecommerce/www/doc/audit.adp,v diff -u -r1.1 -r1.2 --- openacs-4/packages/ecommerce/www/doc/audit.adp 20 Apr 2001 20:51:14 -0000 1.1 +++ openacs-4/packages/ecommerce/www/doc/audit.adp 26 May 2002 04:36:50 -0000 1.2 @@ -1,215 +1,290 @@ - -Audit Trail Package + - +

The Big Picture

-

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.

-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

-

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 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:

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

    Reference

    -
    -
    
-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
-}
-
    -
    +

    Audit columns:

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

    ec_audit_trail_for_table

    -

    Reference

    +
    +

    Returns an audit trail across an entire table, (multiple + keys).

    -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. -
      +
    • db Database handle.
      • -Arguments for -ec_audit_trail_for_table +
    • 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.
        • -Returns an audit trail across an entire table, (multiple keys). -

        +

      • id_column Column name of the primary key in + audit_table_name and main_table_name.
        • -
      • 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. +
      • start_date (optional) ANSI standard time to begin + viewing records.
        • -
      +
    • end_date (optional) ANSI standard time to stop viewing + records.
      • -Arguments for -ec_audit_trail +
    • 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, 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. +
    - +

    ec_audit_trail

    -Arguments for ec_audit_delete_row +
    -
      +

      Returns an audit trail of a single key in a table.

      + +
        -Creates a row in the audit table to log when, who, and from what IP address a row was deleted. -

        +

      • db Database handle.
        • -
      • 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. +
      • 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.
      • -

      Future Improvements

      +
    • id_column_list Column names of the unique key in + audit_table_name and main_table_name.
      • -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. +
    • 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. + +
    + +
    + +

    ec_audit_delete_row

    + +
    + +

    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

    + +

    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.