Database auditing - comments please

[nick name]

Attached you'll find my first go at a database audit module for web2py. It will log every change to a table. The crud versioning system is nice, but it only versions stuff that happens on crud. If you insert/modify records not through crud, you'd need to manually update it -- the attached code will do that automatically for you.

The app I'm developing needs a searchable, browseable audit of all changes to database, for which simple SQL logging is not sufficient.

WARNING: only very lightly tested code, probably doing everything the wrong way. Use at your own risk.

To use: place audit.py in your models directory; then, after defining a table with

mytable = db.define_table('mytable', ....)

call:

with_audit(mytable)

It will create a table called 'audit_mytable', which has in addition to all your fields, 4 more: oid (original id), time, user, and action (insert/update/delete)

any modification to the original table will result in a record being added; inserted records are copied after insertion; updated records are copied after update; deleted records are copied before delete.

KNOWN ISSUES:
* audit table is placed on the same adapter as the audited table - I want to allow the audit to be on a different db/dal/adapter (needs distributed transaction to work properly!)
* old id is always integer for lack of a better type; it should be a "weak" reference type
* reference types are still reference type, so when deleting linked records, "on delete cascade" will remove audit records, this is bad! other integrity constraints (notnull, validators etc.) are not copied to audit table -- only references.
* action type is int, no way to specify efficient char(1) in web2py
* audit happens within same transaction as original update, so you commit or rollback both -- I think that's expected behaviour.
* On first use, it patches the adapter *class*. This means that, unlike usual web2py debugging, if you edit audit.py, you'll have to restart web2py. Writing it now, I can't think of a good reason why I did that rather than patch the adapter *instance* -- but that's the case for the attached code.

A better solution would probably be to add pre/post hooks to insert/update/delete in BaseAdapter, but for now the audit.py approach seems to work on 1.95 - 1.97

[nick name]

Newer version attached, has been tested in one specific app which brought up some issues with tables that have references to them (these have been fixed).

Now patches instance instead of class (so web2py's normal reloading works equally well)

Massimo / Jonathan - could you comment on the probability that something like this would be added to the main web2py distribution? At the very least, hooks that one could use to implement such audit/authorization/logging functionality without having to patch adapters?

I'd be happy to contribute a patch for this, if you give me an indication of how you'd like to see it implemented, if at all.

[Rick Ree]

In case anyone else is using this, I found that 1.99.7 requires a change at line 78:

    #rows = self.select(query, ['id'], {})

    rows = self.select(query, [table['id']], {})

[nick name]

On Tuesday, March 20, 2012 2:30:08 PM UTC-4, Rick Ree wrote:

In case anyone else is using this, I found that 1.99.7 requires a change at line 78:

    #rows = self.select(query, ['id'], {})

    rows = self.select(query, [table['id']], {})

Please note that there is a ticket tracking this (with interesting changes):

Issue 354: Database audit implementation / generalized web2py+dal triggers

I am adding your text as a comment there and a link back here.

[Massimo Di Pierro]

I will close that issues because now web2py has _before and _after hooks:

db.table._before_insert.append(lambda fields: None)

db.table._before_update.append(lambda query,fields: None)

db.table._before_delete.append(lambda query: None)

db.table._after_insert.append(lambda fields: None)

db.table._after_update.append(lambda query,fields: None)

db.table._after_delete.append(lambda query: None)

there is a very simple way to add auditing to any table. We are discussing on web2py-developer what the best way is.

Massimo