In part 1 of this series I talked about turning your data audit trail into an interface feature. The primary purpose of an audit trail, though, is to allow you to see a clear picture of every change that has occurred with your data. While the history table solution that I offer is extremely easy to implement, it has the drawback of being contained inside your database. That means your data history is only as secure as your database.

If you write a web application that interacts with a database, then the application connects to the database as a particular database user. (In the code you will user a login and password to connect to the database.) Depending on what kind of access rights that user account has been given, your web application will be able to change and add records to certain tables. Some tables, your web application may not even have the correct permission to read.

Let’s be honest, sometimes bugs are going to appear in code that goes live and is manipulating live data. In fact that’s a big reason keeping an audit trail is so important! It would be a shame to have a bug that inadvertently altered or deleted information from your history table. The good news is there’s no time at which a record in the history table should be altered or deleted. You can easily prevent a whole category of unpleasant mistakes by making sure that the login your web application uses to access the database does not have the necessary rights to run an update or delete statement on any history tables. Select and insert statements are no problem, but disallowing updates and deletes removes a dangerous loophole by which your audit trail could become corrupted.

filed under: Web Development