When working with a database system that handles personal data, a developer has to walk a tightrope line between maintaining a usable audit trail and data privacy. In this article I take a look at techniques to ensure data privacy is observed while still maintaining an audit trail.

The Audit Trail

Any good database software should keep an audit trail of changes made. There are several benefits to a good audit trail:

• Accidental changes can be reversed.
• Managers can identify if the system is being misused – either by accident or maliciously.
• Developers can identify if there is a problem with data capture.
• Reports can be made on how frequently certain types of data changes.

The audit trail should record what changed, before and after values, when it changed, and who changed it.

Data Privacy

Data privacy requires that only the minimal amount of personal data is stored:

• Nothing should be stored that isn’t needed for the operation of the business or by law.
• Nothing should be stored longer than it is required.
• Errors should be corrected and incorrect information removed.

In many cases, unless required by law to keep information longer, personal data should be deleted within 1 month of receiving a request to remove it.

The Problem of Audit Trail and Data Privacy

The Audit Trail and Data Privacy objectives seem to be at odds with each other. One wants to keep a full record of everything, forever. The other wants to remove any data as soon as it is not required. By keeping a full audit trail, the risk is that excessive private data is kept indefinitely, even after deletion of a record the contents of the record could still be found in the Audit Trail. However, by deleting everything as soon as it is no longer needed, and removing all reference to it from the Audit Trail, the Audit Trail becomes useless.

Maintaining The Audit Trail and Data Privacy Together

So the task is to fulfill two opposing requirements. But this is not impossible. An Audit Trail already contains a date stamp to indicate when a change was made. This can be put to work:

• Records in the Audit Trail should have an additional “Anonymised” fields. 
• A daily run batch job can check the Audit Trail for any records that have not been anonymised and are more than 28 days old. For the sake of keeping things simple it is best to treat a month as 28 days.
• Any Audit Trail record found by the batch job should have any private data removed and then the record can be marked as anonymised.

This process means that until the 28 days is up, mistakenly deleted or changed records can still be corrected. After the 28 days is up, no private data is stored, fulfilling the Data Privacy requirements. However, there is still an Audit Trail of what type of changes were made, by who and when. Allowing the Audit Trail to continue to fulfil its other roles.

With this process, it is possible for the two opposing requirements to coexist.

SAMWare UK creates bespoke solutions and websites. Contact us to arrange a chat to discuss your needs and for a free no obligation quote.