The Old Joel on Software Forum: Part 4 (of 5)

Audit log design patterns?

Another design post. Can you tell I'm doing design work today?

I need the ability to track changes to the attributes of our business object. The design is classic n-tier, with a business object talking to a data access layer which in turn talks to the database. (We're using C# with SQL 2K). The information that I'm wanting to audit is the old value, the new value, the date and the user that made the change.

I've got a couple of choices, so I'm hoping to get some feedback from some people that have done this before.

1. I can use triggers in the database to record the changes.
2. I can put auditing code in the business object.

The problem I have with #1 is that it gets a bit messy trying to determine the user that made the change. The business object uses several tables to store it's data, but the "LastUpdatedBy" is only stored in a single "header" table. So if an associated table is updated, the trigger would have to look up the LastUpdatedBy from the header table. This assumes that the header table has been updated first.

The other problem I have with #1 is an issue of transactions. I'd like to roll the actual change and the audit into a single transaction. I need to do some research on this one, but if I roll back the transaction in my business object, are the changes that my trigger made going to be rolled back as well? If they aren't, then that presents a problem.

I'm leaning towards #2 since it puts the auditing logic in my business object, but I've got a little voice in my head telling me that there may be a better way.

What methods of you used to implement audit logging?

Mark Hoffman
Monday, April 19, 2004

Use 2 because it can contain a lot of useful context
information. You can also autdit non-database
operations, which would be the norm i would think.
A service operation can map to numerous database
operations so may not be all the useful.

son of parnas
Monday, April 19, 2004

Rolled back transactions roll back trigger changes occurring within that transaction as well.

.
Monday, April 19, 2004

We pushed the context we needed down to the DB so we wouldn't have to write audit code in all our business objects.

Any table that has data we need to audit has the following fields:

Revision (int)
RevisionUserID (int)
RevisionDate (date)
Deleted (bit)

Any stored procedure that makes changes (insert/update/delete) has a RevisionUserID parameter which is automatically added by the data service according to the currently logged in user. On an update or delete, we increment the revision number and update RevisionDate to GetDate(). A trigger writes all the old values to an audit table, including all the previous revision number, date, and user. This gives you a complete history and an option for implementing "undo" if you wanted it.

We wrote a script that generates an audit table and the appropriate audit triggers for any table, so making a given table "auditable" is trivial, and requires no changes at all outside the data tier. It's working really well.

We're also using transactions quite a bit (initiated by the business logic tier usually), and I can vouch for the workability of a trigger solution in that environment.

Ian Olsen
Monday, April 19, 2004

"We pushed the context we needed down to the DB so we wouldn't have to write audit code in all our business objects."

Do you impersonate the calling user through the various tiers? This is the primary issue with auditing in the database and most applications - often there is a service account that is actually performing data access, so data-layer auditing will always see the service account as the acting entity.

Dennis Forbes
Monday, April 19, 2004

"Do you impersonate the calling user through the various tiers?"

Not really. But sometimes. :)

The currently logged in user has a cookie holding his UserID. Any tier, including the data access layer (what I meant by "data service" above) almost always has access to the HttpContext of the current request, and can therefore get access to that cookie directly. So 99% of the time we can just grab the UserID whenever we want it.

We did additionally abstract the cookie reading/writing into our own "session service" so that we could more easily change how and where client state gets stored down the road. It is actually this class that reads the cookie via the HttpContext.

I said "almost always has access" because we do have a few routines that run asynchronously on the ThreadPool, where the HttpContext is no longer available and our session service class doesn't work. UserID is among the state data provided for these routines, which is then passed down the chain to the data access code. If the extremely long-running database requests (for which we create these asynchronous routines) were common, we'd probably have to come up with a better solution. Luckily they're not.

Ian Olsen
Monday, April 19, 2004

Actually now that I think about it, we are in fact accomplishing "impersonation" through each tier.

Each tier knows on whose behalf it is doing work.

When somebody says "impersonation" I start thinking about OS level users and permissions, which we're not using, but the fundamental concept is intact.

Ian Olsen
Monday, April 19, 2004

"When somebody says "impersonation" I start thinking about OS level users and permissions, which we're not using, but the fundamental concept is intact."

Indeed the fundamental concept is correct - at the OS level really it's just passing around a cookie with the object name in the same way.

Of course your impersonation model is broken if any participants in the activity don't honour your custom impersonation system - i.e. if a component accesses a file system object, obviously it is going to authorize versus the service account rather than the originating user. This is the primary problem in most database layers where your logic doesn't have access to external identity values (i.e. in T-SQL) without some serious mungification, like having a "logon" table with SPIDs and usernames.

On the flip side if you impersonate through the entire chain, at the OS level, things like connection pooling don't work. Fine for high value per transaction/high security systems, but not appropriate for ecommerce models.

Dennis Forbes
Monday, April 19, 2004

Triggers work nicely because they cannot be avoided. If someone were to change the data from a different application or from a SQL command line, for example, you'd never know.

You could always turn on “auditing” if SQL Server 2K has that capability.

MR
Monday, April 19, 2004

I would go with option #2 as it is (presumably) a business requirement that you perform the audit function. That is, it is not just something to help with debugging, etc.

Given that it is a business requirement, it belongs in the business "layer", along with all the other business rules.

If you are concerned with people bypassing the audit trail by updating the data in the database directly, then you have bigger problems to worry about.

If you publish your business layer as an effective API to the underlying data, then you should be in a good position to say that no updates should be made directly on the underlying data.

Steve Jones (UK)
Tuesday, April 20, 2004

I'd suggest the trigger route, but with a caveat. Watch the performance. On one DB (SQL 7.0 ca 1999) we built the audits with triggers and suffered serious performance issues. On small databases (our dev and tst platforms) it worked just fine. Once migrated to production, we ran into problems. Keep in mind, we only ran into the issue on SQL v 7.0 -- it may be corrected now with 2000, and we only ran into it on very large (100s of millions rows) on very high transaction volume (500K (ish) transactions per day) databases.

Our solution was to simply pull the code for the audit log from the triggers and place it in the stored proc. This worked for us as all data access for this app was done through the stored procs and nothing had permissions to the base tables. Once the code was in the stored procs, we saw an improvement in performance on the order of 400 to 500% on the number of concurrent transactions we could have hitting the database, and the time per transaction dropped significantly.

None of this may appy to your situation. YMMV

Sgt. Sausage
Tuesday, April 20, 2004

The whole point of a RDBMS is to tie business logic close to the data so you cannot circumvent it, nor do you have to code up silly APIs.

MR
Tuesday, April 20, 2004

MR - come on - you can't be serious - there are lots of situations where it is impossible or extremely difficult for the database layer to enforce business logic.

Triggers are fine for logging of CRUD operations, but as soon as you want to do somehting else like logging print or view operations you have to go up a few levels.

DJ
Tuesday, April 20, 2004

Interesting thread. I had to do this with a database that does not support triggers or stored procedures. Personally I like the idea of using stored procedures. I would imagine one requirement for "update X", "insert X" would be the user making the request and by then perhaps you have to assume they have the privilege not only for the action but for each record in each table. In my case I store the current object, then I make the changes, get the new current object and compare the two for people to browse in the view changes function. But I've gotten some new ideas in this thread. Thanks all.

Me
Tuesday, April 20, 2004

DJ I would like to know of a business rule that can't be described via predicate logic.

MR
Tuesday, April 20, 2004

Lets say the business rule is that all printing of Invoices must be logged. Date/Time, Invoice Number and User

So you have a Invoice table
and a Log table

How would you do that with triggers? You can't - You either have to have a stored procedure or a method in a business layer to log the event. Those have to be specifically called by the application when the print occurs.

Yes all business rules can be probably described with predicate logic, but the information is not always available at the database level or it is extremely inefficient or difficult to push it down to that level.

DJ
Tuesday, April 20, 2004

To be fair, one of your statements was "there are lots of situations where it is impossible or extremely difficult for the database layer to enforce business logic. ".

In the scenario that you indicated, the data source would likely be a stored procedure (indeed in most good designs no tables are accessed directly, and everything is via SPs), and there is no reason why that stored procedure can't audit the event. Of course if you really want hardcore logging you can use a product like Entegra (http://www.lumigent.com/products/entegra/entegra.htm), which can log virtually everything.

Dennis Forbes
Tuesday, April 20, 2004

Say you only want to log it if they send it to the printer and you display it in print-preview first.

Note the SP was already invoked to get the data for the print preview but you don't want it logged yet.

The user presses print - now you want it logged. Can't do it in the SP unless you re-run it and rebuild the report - not very efficient.

DJ
Tuesday, April 20, 2004

In a design sense, I would argue that the auditing of invoice printing is a completely different type of requirement than the one I'm solving with my design.

The trigger-based solution I described solves the problem of auditing (almost) all changes to data, including the date and the responsible user. We can pretty easily map these changes to user-level concepts: e.g. who changed Sally's password? When did Jane add Product Z to the catalog? (And being able to undo/undelete is mostly just gravy, in our case.)

However, auditing user-level activities that don't change any data (specifically, who looked at x, and when?) is a whole different animal. Nevertheless, if you needed to be absolutely thorough about auditing who had SEEN certain data and when, I'd still argue that the data tier (and more specifically, within the RDBMS, if that's where the data lives) is the best place for this. How can you be sure, for example, that a user didn't do a print of a page/screen that isn't really the designated invoice printing screen?

If the requirements don't demand that level of thoroughness, I can understand that, but in most cases I'd argue that the database is the best place for data-related audit code, read or write.

Ian Olsen
Tuesday, April 20, 2004

If you're specifically looking for a record of who pressed "print" and when, then okay, that's going to involve code all the way up to the UI. My impression was that the OP was looking for a more general pattern for application-wide use.

Ian Olsen
Tuesday, April 20, 2004