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

• This effectively doubles the number of tables in the entire database.
• The logging may be performed either by program code or database procedures/triggers.
• It may be difficult to turn logging on or off for individual tables.
• It is usually only possible to record one set of values, those that were written to the database, which means that you must look elsewhere to find the original values.
• If each audit table only contains the fields that were changed there is a potential problem with a null value. Was it changed to null? Was it changed at all?
• If the structure of any application database table is changed then the audit table must also be changed to keep in line.
• Due to the potentially large number of audit tables it is difficult to have a single online enquiry which is capable of showing the contents of those tables. The usual solution is therefore to have a separate screen to show the contents of each audit table.

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

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`)
);

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`)
);

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`)
);

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 contents of the old_value and new_value fields depends on how the database was changed:

• Insert - old_value will be empty, new_value will be full
• Update - both fields will only contain those values which have actually changed.
• Delete - old_value will be full, new_value will be empty.

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`)
);

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:

• I wanted a way to turn logging ON or OFF for individual tables, so I created a class variable called $audit_logging (boolean, default value = YES) within the generic table class.
• This variable is set by default to ON when the table details are imported into the Data Dictionary. This setting can be modified as desired before the dictionary details are exported to PHP.
• Each user transaction utilises a standard controller script which communicates with a database table class through a series of generic methods such as insertRecord(), updateRecord() and deleteRecord(). These in turn pass their data to a corresponding method in the DML class to actually update the physical database. All I had to do here was to include the contents of the $audit_logging variable in the list of passed values.
• Within the relevant methods of the DML class I added calls to my newly created AUDIT_TBL class immediately after each database update. These are detailed below.

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:

• Search Audit Details
• List Audit Details
• List Audit Details for an Object
• List Logon Errors

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

• Only four extra audit tables are required for any number of application tables in any number of databases.
• All logging is performed within the standard infrastructure code - there is no need for any additional code in any application components, nor any database procedures or triggers.
• Logging for individual tables can be turned on or off via a simple switch inside the table's definition within the Data Dictionary.
• For each change both the 'old' and 'new' values are recorded.
• Fields which have not changed are not shown.
• There is no potential confusion with null values.
• The structure of any application database table can be changed at will without requiring any corresponding changes to the structure of the tables in the audit database.
• As there are only four audit tables in a strict hierarchy their contents can easily be viewed online via a single screen, and filtered via a variety of selection criteria.
• As well as being a useful tool in a production environment in order to see who changed what, and when, it is also a useful tool in a development environment as it can instantly show how the database was changed after each user transaction.

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

Back to TOP.