Rapid Application Development toolkit for building Administrative Web Applications

Creating an Audit Log with an online viewing facility

By Tony Marston

24th August 2004
Amended 15th October 2015

As of 10th April 2006 the software discussed in this article can be downloaded from www.radicore.org

Introduction
Database Design
- AUDIT_SSN table
- AUDIT_TRN table
- AUDIT_TBL table
- AUDIT_FLD table
- AUDIT_LOGON_ERRORS table
Implementation
- Changes to my DML class
- New AUDIT_SSN class
- New AUDIT_TRN class
- New AUDIT_TBL class
- New AUDIT_FLD class
Viewing the Audit Log
- Enter search criteria
- List Audit Log details
- List Audit Log details for an Object
- List Logon Errors
Conclusion
Amendment History

Introduction

It is sometimes necessary to keep track of what changes were made to the database, and by whom. This is known as Audit Logging or an Audit Trail. I have seen several different ways of implementing Audit Logging, but each method tends to have its own set of advantages and disadvantages.

A common method I have seen in the past requires that each application table to be audited has its own separate audit table. This is a duplicate of the original, but with extra fields such as Date Changed, Time Changed and Who Changed. This method has the following characteristics:-

I was never happy with this method as it required a great deal of effort and the online enquiry was clumsy. As soon as I had the opportunity I decided to design a far more elegant method. My first implementation was in a language called UNIFACE, and I have successfully converted this design to PHP and MySQL.

Back to TOP.


Database Design

Before designing a solution it is first necessary to analyse the problem and identify the requirements. When somebody updates the database we have the following 'things', entities' or 'objects':

User A system may be accessible my many users, so it is important to be able to identify updates by user.
Session A session covers the time period between a user logging on and logging off the system. Note that some systems allow a user to have more than one session active at the same time.
Transaction Within a session a user may process a transaction, also known as 'task' or 'unit of work'. This is the same as a database transaction which covers all updates between a 'start' and a 'commit'.
Database Table Within a transaction any number of database tables may be modified.
Database Field Within a database table any number of fields may be modified.

As well as being able to store this information, the design should allow for any details to be viewed in a single screen. These details should make it easy to see exactly what information has changed, preferably showing both the original and newest values. Impossible? Only to those of limited ability.

The design I produced has only four database tables, as described below.

AUDIT_SSN (audit session)

This has the following structure in MySQL:

CREATE TABLE IF NOT EXISTS `audit_ssn` (
  `session_id` bigint(20) unsigned NOT NULL auto_increment,
  `user_id` varchar(16) NOT NULL default 'UNKNOWN',
  `ssn_datetime` datetime NOT NULL default '2000-01-01 00:00:00',
  PRIMARY KEY  (`session_id`)
);
The AUDIT_SSN table
SESSION_ID A unique number given to each session as the first set of details are logged.
USER_ID User identity. This links to the USER table in my Role Based Access Control database.
SSN_DATETIME The date and time the AUDIT_SSN record was created.

AUDIT_TRN (audit transaction)

This has the following structure in MySQL:

CREATE TABLE IF NOT EXISTS `audit_trn` (
  `session_id` bigint(20) unsigned NOT NULL default '0',
  `tran_seq_no` smallint(6) unsigned NOT NULL default '0',
  `trn_datetime` datetime NOT NULL default '2000-01-01 00:00:00',
  `task_id` varchar(40) NOT NULL default '',
  PRIMARY KEY  (`session_id`,`tran_seq_no`)
);
The AUDIT_TRN table
SESSION_ID As above
TRAN_SEQ_NO Transaction Sequence Number. This starts at 1 for each Session.

Each time the database is updated - when the user presses the SUBMIT button which initiates a start transaction and ends with a commit - this is treated as a separate database transaction. This may include any number of database additions, deletions and updates.

TRN_DATETIME The date and time the Transaction started.
TASK_ID The name of the component from which the user initiated the transaction. This links to the TASK table in my Role Based Access Control database.

AUDIT_TBL (audit table)

This has the following structure in MySQL:

CREATE TABLE IF NOT EXISTS `audit_tbl` (
  `session_id` bigint(20) unsigned NOT NULL default '0',
  `tran_seq_no` smallint(6) unsigned NOT NULL default '0',
  `table_seq_no` smallint(6) unsigned NOT NULL default '0',
  `base_name` varchar(64) NOT NULL default '',
  `table_name` varchar(64) NOT NULL default '',
  `pkey` varchar(255) NOT NULL default '',
  PRIMARY KEY  (`session_id`,`tran_seq_no`,`table_seq_no`),
  KEY `pkey` (`pkey`)
);
The AUDIT_TBL table
SESSION_ID As above
TRAN_SEQ_NO As above.
TABLE_SEQ_NO Table Sequence Number. This starts at 1 for each Transaction.

There may be changes to several occurrences of the same table, so each occurrence is given its own sequence number.

BASE_NAME Database Name. An application may have more than one database, and it is possible for the same table name to exist in more than one database.
TABLE_NAME Table Name. The name of the database table being updated.
PKEY Primary Key. The primary key of the database record, shown in the format of the WHERE clause of an sql SELECT statement, as in field='value' AND field='value'.

AUDIT_FLD (audit field)

This has the following structure in MySQL:

CREATE TABLE IF NOT EXISTS `audit_fld` (
  `session_id` bigint(20) unsigned NOT NULL default '0',
  `tran_seq_no` smallint(6) unsigned NOT NULL default '0',
  `table_seq_no` smallint(6) unsigned NOT NULL default '0',
  `field_id` varchar(255) NOT NULL default '',
  `old_value` text,
  `new_value` text,
  PRIMARY KEY  (`session_id`,`tran_seq_no`,`table_seq_no`,`field_id`),
  KEY `field_id` (`field_id`)
);
The AUDIT_FLD table
SESSION_ID As above
TRAN_SEQ_NO As above.
TABLE_SEQ_NO As above.
FIELD_ID Field (column) name.
OLD_VALUE The value in this field before the database update.
NEW_VALUE The value in this field after the database update.

The contents of the old_value and new_value fields depends on how the database was changed:

AUDIT_LOGON_ERRORS

This table is only used for recording instances of failed logon attempts.

This has the following structure in MySQL:

CREATE TABLE `audit_logon_errors` (
  `id` int(11) NOT NULL auto_increment,
  `timestamp` datetime NOT NULL default '0000-00-00 00:00:00',
  `ip_address` varchar(16) NOT NULL default '0.0.0.0',
  `user_id` varchar(16) NOT NULL default '',
  `user_password` varchar(16) NOT NULL default '',
  PRIMARY KEY  (`id`)
);
The AUDIT_LOGON_ERRORS table
ID Technical primary key
TIMESTAMP Date and time of the error.
IP_ADDRESS IP address of the request which generated the error.
USER_ID Part of the input which generated the error.
USER_PASSWORD Part of the input which generated the error.

Back to TOP.


Implementation

The next step was to find a way to integrate this design within my existing development infrastructure. What I needed was a way to implement this facility without having to modify a multitude of scripts. Fortunately my modular design, where each component has a specific function, made it easy. Every database table is accessed through its own database table class, and each of these classes is implemented as a subclass of a generic table class which communicates with the database through a separate DML class. This enabled me to implement audit logging with the following steps:

Changes to my DML class

Within the class constructor I include the definition for the AUDIT_TBL class, as follows:

    require_once 'classes/audit_tbl.class.inc';

Other methods were modified as follows:

    $this->query = "INSERT INTO $tablename SET ....";
    ....
    if ($this->audit_logging) {
        $auditobj = RDCsingleton::getInstance('audit_tbl');
        // add record details to audit database
        $auditobj->auditInsert($dbname, $tablename, $pkey, $fieldarray);
        $this->errors = array_merge($auditobj->getErrors(), $this->errors);
    } // if
    $this->query = "UPDATE $tablename SET ....";
    ....
    if ($this->audit_logging) {
        $auditobj = RDCsingleton::getInstance('audit_tbl');
        // add record details to audit database
        $auditobj->auditUpdate($dbname, $tablename, $where, $fieldarray, $oldarray);
        $this->errors = array_merge($auditobj->getErrors(), $this->errors);
    } // if
    $this->query = "DELETE FROM $tablename WHERE $where";
    ....
    if ($this->audit_logging) {
        $auditobj = RDCsingleton::getInstance('audit_tbl');
        // add record details to audit database
        $auditobj->auditDelete($dbname, $tablename, $where, $fieldarray);
        $this->errors = array_merge($auditobj->getErrors(), $this->errors);
    } // if

New AUDIT_SSN class

The only custom code in this class is as follows:

    function _cm_getInitialData ($fieldarray)
    // Perform custom processing for the getInitialData method.
    {
        if (!isset($fieldarray['user_id'])) {
            $fieldarray['user_id']      = $_SESSION['logon_user_id'];
            $fieldarray['ssn_datetime'] = getTimeStamp();
        } // if

        return $fieldarray;

    } // _cm_getInitialData

New AUDIT_TRN class

The only custom code in this class is as follows:

    function _cm_getInitialData ($fieldarray)
    // Perform custom processing for the getInitialData method.
    {
        if (!isset($fieldarray['tran_seq_no'])) {
            $session_id = $fieldarray['session_id'];
            // obtain the next value for tran_seq_no
            $select = "SELECT max(tran_seq_no) 
                       FROM $this->tablename 
                       WHERE session_id='$session_id'";
            $count = $this->getCount($select);
            $fieldarray['tran_seq_no']  = $count + 1;
            // fill in other data
            $fieldarray['task_id']      = $GLOBALS['task_id'];
            $fieldarray['trn_datetime'] = getTimeStamp();
        } // if

        return $fieldarray;

    } // _cm_getInitialData

New AUDIT_TBL class

When the DML object processes a change to the database it communicates with this class in order to have that change logged in the audit database. It accesses one of the following methods:

  function auditInsert ($dbname, $tablename, $fieldspec, $where, $newarray) 
  // add a record to the audit trail for an INSERT.
  {
    $oldarray = array();
    
    // use the general-purpose method
    $this->auditWrite($dbname, $tablename, $fieldspec, $where, $newarray, $oldarray);
    
    return;
    
  } // auditInsert
  function auditUpdate ($dbname, $tablename, $fieldspec, $where, $newarray, $oldarray) 
  // add a record to the audit trail for an UPDATE.
  {
    // use the general-purpose method
    $this->auditWrite($dbname, $tablename, $fieldspec, $where, $newarray, $oldarray);
    
    return;
    
  } // auditUpdate
  function auditDelete ($dbname, $tablename, $fieldspec, $where, $oldarray) 
  // add a record to the audit trail for a DELETE.
  {
    $newarray = array();
    
    // use the general-purpose method
    $this->auditWrite($dbname, $tablename, $fieldspec, $where, $newarray, $oldarray);
    
    return;
    
  } // auditDelete

This is the function that actually writes the details out of each database table change to the audit log. Note that only fields which have actually been changed are output - it is not necessary to log fields which have not changed.

function auditWrite ($dbname, $tablename, $fieldspec, $where, $newarray, $oldarray) 
  // add a record to the audit trail for an INSERT, UPDATE or DELETE.
  {
    $this->errors = array();
    
    if (!isset($_SESSION['session_number'])) {
        // first time only, get details from audit_ssn
        require_once 'audit_ssn.class.inc';
        $ssn_obj = RDCsingleton::getInstance('audit_ssn');
        $ssn_data = $ssn_obj->insertRecord(array());
        if ($ssn_obj->errors) {
            $this->errors = $ssn_obj->getErrors();
            return;
        } // if
        $_SESSION['session_number'] = $ssn_data['session_id'];
    } else {
        $ssn_data['session_id'] = $_SESSION['session_number'];
    } // if
    
    if (empty($this->trn_array)) {
        // first time only, get details from audit_trn
        require_once 'audit_trn.class.inc';
        $trn_obj = RDCsingleton::getInstance('audit_trn');
        $this->trn_array = $trn_obj->insertRecord($ssn_data);
        if ($trn_obj->errors) {
            $this->errors = $trn_obj->getErrors();
            return;
        } // if
    } // if
    
    $fieldarray  = $this->trn_array;
    $session_id  = $fieldarray['session_id'];
    $tran_seq_no = $fieldarray['tran_seq_no'];
    
    // obtain the next value for table_seq_no
    $select = "SELECT max(table_seq_no) FROM $this->tablename "
             ."WHERE session_id='$session_id' AND tran_seq_no=$tran_seq_no";
    $count = $this->getCount($select);
    $fieldarray['table_seq_no'] = $count + 1;
    
    $fieldarray['base_name']  = $dbname;
    $fieldarray['table_name'] = $tablename;
    
    $pkey_string = trim($where, '( )');
    $fieldarray['pkey'] = addslashes($pkey_string);

    // add this record to the database
    $fieldarray = $this->_dml_insertRecord ($fieldarray);
    if ($this->errors) {
        return;
    } // if
    
    foreach ($fieldspec as $field => $spec) {
        if (isset($spec['noaudit'])) {
            // 'no audit logging' switch is set, so disguise this field's value
            if (isset($oldarray[$field])) {
                $oldarray[$field] = '**********';
            } // if
            if (isset($newarray[$field])) {
                $newarray[$field] = '**********';
            } // if
        } // if
    } // foreach
    
    if (!empty($newarray)) {
        // look for new fields with empty/null values
        foreach ($newarray as $item => $value) {
            if (empty($value)) {
                if (!array_key_exists($item, $oldarray)) {
                    // value does not exist in $oldarray, so remove from $newarray
                    unset ($newarray[$item]);
                } // if
            } else {
                // remove slashes (escape characters) from $newarray
                $newarray[$item] = stripslashes($newarray[$item]);
            } // if
        } // foreach 
        // remove entry from $oldarray which does not exist in $newarray
        foreach ($oldarray as $item => $value) {
            if (!array_key_exists($item, $newarray)) {
                unset ($oldarray[$item]);
            } // if
        } // foreach
    } // if
    
    $table_seq_no = $fieldarray['table_seq_no'];
    $fieldarray   = array();
    $ix           = 0;

    foreach ($oldarray as $field_id => $old_value) {
        $ix++;
        $fieldarray[$ix]['session_id']   = $session_id;
        $fieldarray[$ix]['tran_seq_no']  = $tran_seq_no;
        $fieldarray[$ix]['table_seq_no'] = $table_seq_no;
        $fieldarray[$ix]['field_id']     = $field_id;
        $fieldarray[$ix]['old_value']    = $old_value;
        if (isset($newarray[$field_id])) {
            $fieldarray[$ix]['new_value'] = $newarray[$field_id];
            // remove matched entry from $newarray
            unset($newarray[$field_id]);
        } else {
            $fieldarray[$ix]['new_value'] = '';
        } // if
    } // foreach
    
    // process any unmatched details remaining in $newarray
    foreach ($newarray as $field_id => $new_value) {
        $ix++;
        $fieldarray[$ix]['session_id']   = $session_id;
        $fieldarray[$ix]['tran_seq_no']  = $tran_seq_no;
        $fieldarray[$ix]['table_seq_no'] = $table_seq_no;
        $fieldarray[$ix]['field_id']     = $field_id;
        $fieldarray[$ix]['old_value']    = '';
        $fieldarray[$ix]['new_value']    = $new_value;
    } // foreach

    // add all these records to the database
    require_once 'audit_fld.class.inc';
    $fld_obj = RDCsingleton::getInstance('audit_fld');

    $fieldarray = $fld_obj->insertMultiple($fieldarray);
    $this->errors = $fld_obj->getErrors();
        
    // switch from AUDIT back to original database name
    $this->selectDB($dbname);
            
    return;
    
  } // auditWrite

New AUDIT_FLD class

This does not require any custom code to augment what was created by the dictionary export function.

Back to TOP.


Viewing the Audit Log

As there are only a small number of tables to hold the audit log details the contents can be viewed with a few simple screens:

There is also a task Generate SQL which will write an SQL query based on the selected audit log entry to a file. This will enable the database change to be duplicated in another copy of the database.

Back to TOP.


Conclusion

As you can see this is a simple yet flexible design which has the following characteristics:-

The only disadvantage to this implementation is that it can only log those changes which are performed through the application. Any changes done through other means, such as directly via SQL or through other applications, will not be logged. When you weigh up the cost of providing such comprehensive logging - the number of audit tables, the number of database triggers, the number of enquiry screens - you just have to ask the question "is it worth it?"

Back to TOP.


Amendment History

15th Oct 2015 Updated AUDIT_SSN table to replace date and time columns with a single ssn_datetime column.
Updated AUDIT_TRN table to replace date and time columns with a single trn_datetime column.
30th Jun 2007 Added a new task Generate SQL which will write an SQL query based on the selected audit log entry to a file. This will enable the database change to be duplicated in another copy of the database.
10th Mar 2006 Added a new database table AUDIT_LOGON_ERRORS and a new screen List Logon Errors.
2nd Jan 2006 Replaced database tables AUDIT_HDR and AUDIT_DTL with the more normalised AUDIT_SSN, AUDIT_TRN, AUDIT_TBL and AUDIT_FLD.
21st Jun 2005 Added a screenshot for List Audit Log details for an Object.
17th Jun 2005 Amended Implementation section to include reference to the Data Dictionary.

Back to TOP.

counter