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:-
Only two additional database tables are required for audit logging purposes. These are AUDIT_HDR and AUDIT_DTL, as described below:
| 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. |
| 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:
The contents of the BEFOREDATA and AFTERDATA lists depends on how the database was changed:
|
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 |
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.
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.
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.
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.
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.
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. |
Before Audit Logging will actually take place the following settings must be altered in the Menu database:
In this screen the following values must be set:-
In this screen the following details must be entered:-
There are two screens for viewing the contents of the Audit Log, each being accessed via a different route:-
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:-
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.
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:-
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.
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.
Tony Marston
20th February 2002
mailto:tony@tonymarston.net
mailto:TonyMarston@hotmail.com
http://www.tonymarston.net