Projects:Audit Trail/Technical Documentation

DB Structure

Tables

The following additions are needed:

• AD_Table.IsFullyAudited column: Determines whether the table is audited
• AD_Column.isExcludeAudit: Excludes a auditory of column in an audited table.
• AD_Audit_Trail table. To maintain the whole track, a new record will be created in this table for each column is modified in the audited table. With the following info:
  • Table and record id.
  • Version of the record. Incremental number, which will make easier to group changes as well as order modifications.
  • Action, author and time.
  • Place the action was done from (window, process...)
  • Old value, new value.
• AD_Context_Info. Temporary session table that will have the information about the user and session that started the action
• AD_Session_Usage_Audit. A table that maintains a track of all actions performed in a session (Window navigation, Process execution...)
• AD_Systm_Info.isUsageAuditEnabled: Determines whether usage audit is active.

Triggers

Generated trigger per each audited table will populate the audit trail table.

These triggers will be executed whenever a modification (insert, update or delete) happens in any of the audited table. The actions the trigger will do are:

• Calculate the new revision number for the record.
• Read from temporary session table user, session and invocation place.
• Insert a new row in the audit trail table per each of the columns modified in the audited table.

Triggers will be named starting by AU_. AU will be register as part of the core DBPrefixes and DBSM will not take elements starting by this dbprefix as part of the actual model.

Generation process

These triggers will be automatically generated. The process for generating triggers should be executed whenever occurs one of the following events:

• A table is set to be fully audited.
• A table is set not to be fully audited anymore.
• A fully audited table has a modification on its structure.

Depending on how long takes this process to finish, it could be invoked each time a compilation is done (in case the process is fast). If not it would be manually callable from menu, and it would be automatically called after database creation and update.

Tracking session actions

In order to be able to save the action the modification was done from (process, window, etc...), it would be necessary to keep track of session actions.

This would be done in org.openbravo.base.secureApp.HttpSecureAppServlet.service method for general servlets. There are two possible approach for this:

• Keep just the last action, this action would be saved in the session table whenever a connection is obtained and would be used by the trigger reading from that table.
• Persist this information in a table (AD_Session_Track) maintaining in this way a track of all actions performed by a session. If this approach is followed, this information could be queried by the triggers, or it could also be stored in the session table to improve the triggers performance.

Because of the way org.openbravo.base.secureApp.HttpSecureAppServlet.service works (obtaining the information from the java class of the servlet that is being executed), it is not possible to determine there standard UI processes. Therefore for these processes a new method to set it manually should be created.

Passing session info to DB

Triggers must know some information (user, session id...) from the context session. This information will be saved in a database session temporary table whenever a connection to database is obtained. This will require to add this capability to all ConnectionProvider implementations, as well as to DAL's connection.