4GL Patterns #10 – Auditability

I have seen this pattern occur several times in my career. Ken Downs has a comprehensive treatment on history tables.

There are several high level ways of describing how versioning/auditability is implemented:

• Versioned rows – history is mixed with current data in a single table. Results in complicated joins, archiving problems, performance trouble
• Naive history tables – a mirror table that adds three additional columns – action (Create, Update or Delete), timestamp, and user
• Delta tables – records table, primary key, old value, new value, action (Create, Update, or Delete), timestamp, and user

Implementation

Web applications tend to share database connections. This restriction means that we cannot implement auditing via database triggers, since the trigger does not have access to the current user. Instead, auditing is implemented at the database abstraction layer.

Oracle has a construct called SYS_CONTEXT (see: TechRepublic AMIS)

Users, Groups and Elevated Privilege

Any non-trivial business applications tend to have functionality beyond simple read/write permissions. Examples of this kind of use-cases can be seen in setuid in unix applications. Any audit-aware applications should take this into account, and log the real user id.

Examples

• Document-type artifacts – for instance, if an organisation was tracking licenses, the address of the licensee should be recorded permanently. If the address changes, then a new row has to be created, so that it maintains a faithful record as to what the original issued license represented.
• Auditing

See Also

Data Dictionaries

You should follow me on twitter here