Centralized Auditing (Design Page)

Background

Most OpenMRS tables include auditing attributes:

• changed_by
• date_changed
• voided_by
• date_voided
• void_reason

These have limitations and require that tables contain audit history. We also end up supporting similar audit capabilities in several places within the API. We would like to centralize these auditing functions within a central audit service.

The "creator" and "date_created" columns can be left on the original tables.  Moving them to a centralized table can be discussed at a later point.

The audit information is currently placed on objects as they are saved automatically by the RequiredDataAdvice/SaveHandlers and/or the AuditableInterceptor hibernate class.

Design

A single 'audit' table will hold the values above along with a "blob" type of column and a "type" column.  The blob is serialized xml.  The type is the class that is being modified.

We should start with one object to try this on before implementing system wide.  It has been suggested that something like person_address would be suitable.

Design Goals

1. Remove the redundant columns in almost every table for creator/dateCreated etc.
2. Keep a record of what actually changed from one version to the next (e.g., an xml blob).
3. Keep all the history for an object instead of just the most recent change.
4. Avoid tight binding to specific domain objects; more specifically, have a strategy for easily handling additional types in the future.

Road-map

1. Create a table (audit_log), corresponding class (AuditLog) and hibernate mapping.

2. Replace AuditableInterceptor to handle updates and deletes. On an update/delete, the AuditableInterceptor will make an appropriate entry in audit_log.

3. The audit_log table has a blob field which stores a XML file which contains the previous and current state of the modified object.

4. Create an AuditService, which will allow the administrator to view entries in the audit_log.

Assigned Developer

Ankur Gupta, Niranjan Kulkarni, Rohit Manohar,

Interested Parties and Mentors

Ben Wolfe, Jeremy Keiper, Burke Mamlin

Further Discussion

On May 30, 2013, during Q&A with Wyclif, we reviewed the functionality of his auditlog module and the possibility of using it instead of our current method of maintaining audit log information in each record.  We identified the discussion points below.  Because we expect tomorrow's meeting will be preoccupied with GSOC, we welcome discussion here.  The main goal for tomorrow's meeting will be to identify potential show-stoppers, to address comments and questions raised during discussion, and to decide whether it would be a good investment of resources to run a performance test of the new proposal.

1.  What is the purpose of auditing?  What do we expect to gain by changing?

Auditlog records who changed data by maintaining a table of date-time, user, table, field, old value, new value.  It needs some modification to include newly added and deleted records.  Field values are serialized.  It might be possible to record only old value or only new value with same effect.

a. Improve performance by eliminating 3 indexes per table

b. Be able to find out who made a particular change

c. Meet FDA requirements for clinical trial data??

d. Meet HIPAA requirements for auditing access??

e. Not replace native DB transaction logs or backup procedures

2.  What is  the architecture of auditlog and how would it integrate with existing code/modules?

It is a Hibernate interceptor, as are (1) core audit logging; (2) synch module; (3) event module.  Since many important events are DB updates, there is a lot of common functionality that could be factored out and help us move toward an event-oriented model.  Some possibilities:

a. hibernate interceptor generates events and event module publishes them to which centralized auditing and synch subscribe;

b. hibernate interceptor includes centralized auditing which raises events to event module for others to subscribe.

For performance reasons, we should consider giving auditlog its own connection to the DB (not through Hibernate?), which may be a different DB than the OpenMRS DB.

3. Are there DB changes which would not be logged?

Anything that did not go through Hibernate.  Anything that is not an OpenMRSObject.

a. Core SQL method -- uses Hibernate, but do they trigger the interceptor?

b. Liquibase -- can it be configured to use Hibernate? what about structure changes? do we need to keep table version?

c. Direct manipulation through MySQL or other DB session -- at startup, compare each record update time with last logged change??

d. Initial startup -- it would take days to copy the initial contents of the DB, probably a show-stopper

4. How could we test centralized auditing?

Install an MDS package under both scenarios and compare times

a. Modify auditlog to reflect discussions.

b. Write liquibase change sets to remove indexes and not-nulls from audit fields (big task, could be helped by automation)

c. Modify Hibernate XML to remove relationships from audit fields

d. Disable the hibernate interceptor that updates audit fields

e. Locate uses of audit fields -- duplicate checks? voiding an encounter? -- and make sure they won't break if fields are empty

Estimated time: 1 week