Through the course of developing an application the need for some application-level security auditing has been identified. It’s mostly an accountability tool – who edited this record last, we have some questions to ask sort of thing.

I’ve been working it over in my head and I’ve come up with two ways to record audit information:
1) in the table itself
2) separate audit table

In the table itself would be extra columns like this:
Code:
CREATE TABLE bob (
  some_row,
  some_row2,
  ...
  creator_userid,
  create_date,
  edit_userid,
  edit_date,
  delete_flag )
So, we record who created the row (and when) along with the last user who edited the record (I’ll talk about the delete_flag column later).

However, this records no historical information so sort of leaves us out in the cold if we wanted to know what happened to the row two edits ago. Also if I wanted to do some sort of a ‘global’ audit on a particular user to see what they did yesterday my application would have to visit *every* table and pull out audit information where userid = xyz.

A audit table like this:
Code:
CREATE TABLE audit (
  auditid,
  userid,
  date,
  table,
  action,
  command,
  rowid )
would allow me to record what a particular user did at any one moment. A sample row would look like this:
1, ‘mattr’, ’11-27-2001_16:46:22’, ‘user’, ‘update’, ‘update user set password = ‘’ where userid = 123’, ‘0x1234’

So it would tell me that some user updated the table where userid = 123. The rowid is an Oracle-specific thing which points to an exact row; it could easily be a numeric auto_increment field in MySQL for example.

This also allows me to easily grab all audit records by a particular userid. Since this is an audit log keeping a foreign key to any table wouldn’t be a good idea in case a row was deleted (say if I removed user MattR he still made changes to rows).

I think the second method is a little easier since I can easily audit *any* table in the system without heavy modification (if I used a DB abstraction layer I can write that into my UPDATE/DELETE code to automagically write the audit log).

What about historical data? Keeping an audit trail is good but it isn’t terribly fun to look through to easily find what previous values of a particular column were.

So I figure have the audit table in conjunction with a ‘history’ table like this:
Code:
CREATE TABLE history (
  auditid,
  column,
  oldval,
  newval )
So that allows me to audit changes to a particular column.. This would, of course, be handled in the application – any time an update query came it’s way for a particular row it would have to check the old value of the row and see if it was updated. This could easily be an update trigger but I think the application level is fine since this would be used for when users edit a particular row (a particular user account for example).

Of course, I could always append the ‘column’, ‘oldval’, and ‘newval’ columns to the audit tables, however if the user is executing a DELETE statement it wouldn’t really have a column, oldval, or newval values to enter. But since they would be text or varchars they wouldn’t really waste any space either.

If you recall waaaayyyy up top I spoke about the ‘delete_flag’. Well, it’s always better, in my opinion, to logically rather than physically delete items from your application. What if a cracker steals an admin’s password and systematically deletes 1000 rows? What if a user accidentally deletes a row which causes a cascade effect causing 2 hours worth of work to re-enter all the dependencies? Plus, insertions and deletions are the most expensive (comparatively speaking) DML operation you can execute on the system. Indexes have to be updated, rows have to be shuffled around, etc.. A simple UPDATE bob SET delete_flag = 1 WHERE rowid = xyz is very easy (and very fast) to execute. Then, after a pre-determined amount of time, you can go ahead and run some clean-up operations which delete the rows and then defrag your tables (as an example). It also leaves time for users to go ‘holy crap!’ and un-delete the row. Or if you prefer, only admins would have the power to un-delete – to the user it would be just like the item was removed from the database.

Thoughts? Suggestions? What do you use for auditing, historical information, and delete-safe?

Thanks,