5.7. Table auditing

From a medico-legal point of view it is important to track who changed what data when. GnuMed uses database level facilities to achieve that goal. Advantages are that SQL query parsing is left to the database engine, that the backend provides uniform auditing across any variety of client, and that auditing data is moved locally which provides for reasonable speeds. Drawbacks are that audit trail data cannot be transferred remotely right away particularly conveniently and that this scheme is not foolproof against manipulation.

5.7.1. INSERT

On INSERT the committer and the current timestamp is added to the table data. Row versioning is initialized to 0. No data is transferred to the corresponding audit trail table.

5.7.2. UPDATE, DELETE

On any of those two operations the original row version, data, committer, timestamp and table OID are logged in the audit trail table. The current timestamp, committer and action (DELETE or UPDATE) are added. For updates, the row version in the original table is incremented and the timestamp and committer are updated to the current values.

5.7.3. SELECT

Currently SELECTs are not logged. While some legislation may require this it does not make sense technically, will slow down the database considerably and is better left to middleware or frontend. The main argument against SELECT logging at the database level is that the intended purpose of it would be to enable later investigation of unauthorized disclosure. However, a logged SELECT in no way proves any disclosure to any specific target. A missing SELECT does not contribute any useful evidence against disclosure either. Unauthorized disclosure is largely a social-control/policy issue. There's no such thing as "unauthorized access". If access occurred it was authorized (barring any software bugs). Such security breaches must be prevented by other means.

5.7.4. How to add an audit trail to a table

Any table that is supposed to be audited must be marked for auditing by calling the plpgsql function "add_table_for_audit(table_name)". During bootstrapping audit trail tables are auto-generated for all the tables marked for auditing. It is also possible to manually create audit trail tables in which case they need not contain all the columns in the respective audited table (but they must not have any extra columns). In case of manually setting up audit trail tables make sure they inherit from the root table "audit_trail".

During bootstrapping all marked tables that have a corresponding audit trail table are prepared for auditing. The necessary functions and triggers are automatically generated and inserted into the database. The audit trail tables will have the name of the audited table prefixed with "log_".

Please read the source in server/bootstrap/gmAuditSchemaGenerator.py and server/bootstrap/boostrap*.py to learn how to manually create audit trails.

5.7.5. Audit trail handling

Over time the audit trail tables may grow quite large. They may be dumped and cleaned out regularly. The dump file should be notarized digitally and backed up. Preferably an unsigned online copy of the audit trail tables should be moved to another server. It is useful to have the complete revision history online for reference but this is not speed-critical.