Creating an Audit Log with an online viewing facility

Tony Marston - 20th February 2002


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

The method described in this document makes extensive use of Uniface List processing and has the following characteristics:-

Back to TOP.


The Audit Logging tables

Only two additional database tables are required for audit logging purposes. These are AUDIT_HDR and AUDIT_DTL, as described below:

The AUDIT_HDR table (Transaction details)
SESSION_ID (PK) N12 A unique number given to each session as the user passes through the logon screen.
TRAN_SEQ_NO (PK) N4 Transaction Sequence Number. This starts at 1 for each Session.

Each time the database is updated - beginning with a store and ending with a commit - this is treated as a separate Transaction. This may include any number of additions, deletions and updates.

USER_ID C8 User identity.
TRAN_DATE DATE The date the Transaction started.
TRAN_TIME TIME The time the Transaction started.
FORM_NAME VC32 The name of the component from which the user initiated the transaction.
LIBRARY_NAME C16 The name of the Application Library used by FORM_NAME.

The AUDIT_DTL table (Occurrence details)
SESSION_ID (PK) N12 As above
TRAN_SEQ_NO (PK) N4 As above.
ENT_SEQ_NO (PK) N4 Entity Sequence Number. This starts at 1 for each Transaction.

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

ENT_NAME VC32 Entity Name. The name of the database entity being updated.
ENT_MODEL VC32 Model Name. The name of the application model to which the entity belongs.
ENT_PKEY VC255 Primary Key. This is an associative list which shows the field names and values of the occurrence being updated. This is in the format 'field=value;field=value;...'
AUDIT_DATA C* Audit Data. This is an associative list which holds two items, each of which is another associative list:
  • BEFOREDATA - holds the occurrence data as it was read from the database.
  • AFTERDATA - holds the occurrence data as it was written to the database.

The contents of the BEFOREDATA and AFTERDATA lists depends on how the database was changed:

  • Added - BEFOREDATA will be empty, AFTERDATA will be full
  • Updated - both lists will only contain those fields which have actually changed.
  • Deleted - BEFOREDATA will be full, AFTERDATA will be empty.

Back to TOP.


Software changes

Application Model

Before Audit Logging can be performed there are some alterations that need to be made to each entity that is to be included in the audit process:-

Each entity requires the addition of an extra field, which can go at the end.

field name BEFOREDATA
widget type UNIFIELD
data type SPECIAL STRING
field interface C*
characteristics NON-DATABASE

Back to TOP.


Entity Triggers

Certain triggers need to be altered to carry out the audit logging process. This can either be done for all entities in all components, or some entities in some components. The choice is yours.

The <read> trigger
read

if ($status = 0)
   #include STD:AUDIT_BEFORELOOK
endif

This will create an associative list in the BEFOREDATA field containing all the field values which have just been read from the database, but excluding fields of type IMAGE and RAW. Note that if the entity's Field List is not set to 'ALL' then some fields may be excluded by Uniface subsetting.

The <write> trigger
write

if ($status = 0)
   #include STD:AUDIT_AFTERLOOK
endif

This will create an associative list of all the field values which have just been written to the database, but excluding fields of type IMAGE and RAW, and pass it to the Audit Object along with the BEFOREDATA list. Note that if the entity's Field List is not set to 'ALL' then some fields may be excluded by Uniface subsetting. The Audit Object will then compare the two lists and remove any references to a field where the BEFORE and AFTER values are the same, then write the results to the AUDIT_DTL table by means of a store/e command.

In the case of a new database entry the BEFORE list will be empty.

The <delete> trigger
delete

if ($status = 0)
   #include STD:AUDIT_AFTERLOOK
endif

The processing is the same as for the <write> trigger, but in this case the AFTER list will be empty.

Back to TOP.


Proc Code

Certain additional code needs to be inserted before a store and after a commit or rollback.

#include STD:AUDIT_START

This should be performed before the first store in any update cycle. This increments the Transaction Sequence Number and creates a new occurrence of AUDIT_HDR. Note that this will not be written to the database until the first AUDIT_DTL is stored.

#include STD:AUDIT_STOP

This should be performed immediately after the commit or rollback in any update cycle. It clears the contents of the Audit Object, thus forcing the creation of a new transaction the next time around.

Note that AUDIT_START and AUDIT_STOP are automatically included in those procs which contain a store/commit pair (such as OK_PROC and STORE_PROC), so no additional coding is required. Where the store and commit are performed separately, usually because there are several stores, then AUDIT_START must be included before the first store. If the update is terminated with a call to either the standard COMMIT_PROC or ROLLBACK_PROC then these automatically include a call to AUDIT_STOP, otherwise one will have to be included manually.

Back to TOP.


Audit Object

This is a self-contained service which actually writes all entries to the Audit Log. It has the following operations:-

INIT This reads in the data from MNU_0120M and holds it in a list. This means that entity names can be checked in the list without having to access the database each time.
START_AUDIT_LOG This increments the Transaction Sequence Number for the session and creates a new occurrence of AUDIT_HDR. Note that this will not be stored until the first AUDIT_DTL is stored.
WRITE_AUDIT_LOG This checks that the Audit Logging switch has been turned on in the Menu Control Record, and that the entity name has been added to the list in MNU_0120M. Without these two settings no changes to the entity will be logged.

This increments the Entity Sequence Number for the transaction and uses the contents of BEFOREDATA and AFTERDATA to construct a new occurrence of AUDIT_DTL. This is then stored without a commit.

Note that if no AUDIT_HDR occurrence has been set up yet it means that there has been no prior call to START_AUDIT_LOG, so this operation will fail.

STOP_AUDIT_LOG This clears the structure, thus ensuring that the next operation is START_AUDIT_LOG.

Back to TOP.


Run time settings

Before Audit Logging will actually take place the following settings must be altered in the Menu database:

Screen MNU_0070U - Maintain Menu Control data

mnu_0070u.gif

In this screen the following values must be set:-

Back to TOP.


Screen MNU_0120M - Maintain Audit Entity details

mnu_0120m.gif

In this screen the following details must be entered:-

Back to TOP.


Viewing the Audit Log

There are two screens for viewing the contents of the Audit Log, each being accessed via a different route:-

Screen MNU_0110L - List Audit Log for a Session

mnu_0110l.gif

This will list the Audit Log entries for a particular session which is selected in screen MNU_0090L.

The three scroll bars control the following:-

Screen MNU_0090L - List Active Sessions

mnu_0090l.gif

This screen can be accessed from a menu. Select the session that you require then press the Audit Trail button to activate screen MNU_0110L.

Back to TOP.


Screen MNU_0115L - List Audit Log details

mnu_0115l.gif

This will list the Audit Log entries using the selection criteria entered in screen MNU_0115S.

Note that this uses a database view which combines AUDIT_HDR and AUDIT_DTL into a single entity. This allows the selection criteria to include fields from both entities without any problems.

The two scroll bars control the following:-

Screen MNU_0115S - Select Audit Log details

mnu_0115s.gif

This screen can be accessed from a menu. Enter the selection criteria that you require then press the FIND button to activate screen MNU_0115L.

Note that some of the fields allow both a FROM and TO value. This allows the user a specify a range of values more easily.

Back to TOP.


Demonstration Application and sample source code

All the source code (application model, include procs, global procs and components) described in this document are available in my demonstration application which can be downloaded from my Building Blocks page.

Back to TOP.


Tony Marston
20th February 2002

mailto:tony@marston-home.demon.co.uk
mailto:TonyMarston@hotmail.com
http://www.tonymarston.net

Back to TOP.

counter