Appendix L: Audit Logging

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. The method used in this software allows for changes to specified database tables to the logged, and for the contents of the log to be viewed using an online transaction.

Only two extra database tables are required to hold the audit log details of all other tables. 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.

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

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

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.

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.

Run time settings

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

In screen MNU_0070U you must turn on Session Logging (in order to have session numbers assigned) and Audit Logging (otherwise the Audit Object will not write anything).
In screen MNU_0120M you must add the entity and model name to the list of entities, and set the Logging? switch on. This list enables the logging of individual entities to be turned on or off at will.

Viewing the Audit Log

There are two ways of viewing the contents of the Audit Log:-

From screen MNU_0090L you can select a session, then press the Audit Trail button to activate screen MNU_0110L which will then display all the log entries for that session.
From a menu you can select MNU_0115S and enter whatever selection criteria you require. When you press the FIND button this will activate screen MNU_0115L which will then display all the log entries that match the selection criteria.

http://www.tonymarston.net