Today we have a guest editorial as Steve is out of the office. This editorial was originally published on Feb 27, 2018.

I was recently involved with a design of a configuration system. Pretty early on in the design we realized it would be a good idea to have an audit system. The system would need to keep track of any changes that were made to the configuration tables and allow for the ability to back out to any of the previous changes. It also keeps track of who changed what and when they changed it.

This new configuration application has its own schema. All database access is done through stored procedures. There is no inline SQL code in the application. The audit tables match the actual tables except for a few extra audit columns. In the stored procs that do the insert, update or delete, there are audit procedures that are called before the action is done and audit data that is saved to the audit tables.

The configuration application has an admin screen where you can view the audit data and if you have elevated rights you can click a revert button that will restore one of the previous configurations that is selected. This allows the users of the system to maintain it without IT intervention. No more late night calls asking for some configuration to be restored because it was changed by mistake.

There are a number of other ways that one can audit a system in a production environment. Some audit systems keep track of changes that have been made, but don’t really help you back out those changes. I have seen some audit systems that are trigger based, but they tend not to have a lot of visibility to the end user. I am sure there are a number of other options when it comes to audit systems. I know SQL Server 2017 has a server audit and a database audit. You can see some details here: https://docs.microsoft.com/en-us/sql/relational-databases/security/auditing/create-a-server-audit-and-database-audit-specification I have never tried it, but it is another option to consider.

If audit systems are beneficial, why don’t we use them all over the place, in every application? Well, there can be a lot of work that needs to be done anytime a change needs to be made. Something as simple as adding a column to a table now has to be done in at least two places. So, the work is effectively doubled. Also, if inline SQL is allowed, it can quickly become impossible to keep an audit system working without a lot of extra work to ensure the inline SQL isn’t breaking the audit or vice versa.

What is your experience with an audit system? Have you found a certain audit pattern to work better than others? Share your experience and let us know if your audit system was a good idea or a mess to maintain?