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.
|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.
|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.
|data type||SPECIAL STRING|
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.
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.
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.
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 on the Menu database:
There are two ways of viewing the contents of the Audit Log:-