Audit strategies

Question

I am trying to decide on the best audit logging method in my application. The main reason for the log is to report the sequence of events (changes).

I have a hierarchy of objects, I need to create reports when something changes in any part of this hierarchy on the last date.

I think I have three options:

• Have a log for each table and therefore matching the hierarchy of objects, then creating a view for the report.
• Smooth the hierarchy and de-normalize the table, simplifying reporting - a simple select statement.
• You have one log table and there is a record for each change, which makes the report more complex, but more flexible for changes.

I am currently leaning towards option 1.

Answer 1

An audit trail is basically a chronological list of events that occurred, who performed these events, and what events were.

I think a flat look would be better since it can be easily ordered and requested. Therefore, I am more inclined to your option number 2 / number 3.

Include things like transaction type, time, user ID, description of what has changed, and other information related to you related to your product.

Over time, you can also add things to your product, and you do not need to constantly change your audit log module.

Answer 2

I need to talk to this question, even if it is old.

It is usually a bad idea to have only one audit table, since you will create problems with locking in the database, since everything falls into this table. Use separate audit tables for each table.

It is also a bad idea for an application to audit. The audit must be performed at the database level or you risk losing some of the information. Data is not changed only from applications in most databases; no one is going to change the prices of all their products in one of the user interface when you need a 10% increase for all 10,000,000 of them. An audit should record all changes, not just some of them. This should be done in a trigger in most databases (SQL Server 2008 has a built-in audit function). Some of the worst possible changes (employees who commit fraud or want to maliciously destroy data) also often come from places other than the application, especially if you allow table-level access to users (which you should not do in any financial database or one that contains personal information). Auditing from the application will not catch this. Developers often forget that when protecting their data, external sources are not the only threat.

Answer 3

If this is for audit purposes, I would use only true append-only, not the table / tables in the same db.

You offer it for change history purposes - in this case I would restructure your / db application to record actual events in the first place, and not just for the current state.

Answer 4

I would go with (2) and (3): create one table for all audit records.

A flat look is good if additional flattening does not affect performance.

Answer 5

You can study the structure of AOP to help with this. This will allow you to introduce logging functions at the beginning or end of any / all methods. If you go down this road, it can help determine what makes sense for storing log data.