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.
#
# General audit support for web2py:
# When a table is set to be "audited", a shadow table is defined for
# it, with 3 additional fields: "oid" (for original id), "audit_time", "audit_user", "audit_type" (insert/update/delete)
# on insert: after insert, the new record is copied to audit table
# on update: after update, the new records are copied to the audit table
# on delete: before deleting, records are copied to the audit table with audit_delete=true
#
# no new/additional transactions. that is, rollback will also roll back the audit; commit
# will also commit the audit.
# TODO: deactivate "on delete cascade" actions -> turn them to "restrict" or even drop the reference?
# TODO: add "idtype" to adapter - a type that can be used to store id - a "weak" reference
# TODO: is there a char(1) record type? audit_type would benefit from that
class AuditError(RuntimeError):
pass
from gluon.dal import Set as SetClassToIgnore
AUDIT_OID = Field("audit_oid", "integer") # -> switch to idtype/weak reference
AUDIT_TIME = Field("audit_time", "datetime")
AUDIT_USER = Field("audit_user", "string") # or auth_user
AUDIT_TYPE = Field("audit_type", "integer") # 1=insert, 2=update, 3=delete
AUDIT_FIELDS = [AUDIT_OID, AUDIT_TIME, AUDIT_USER, AUDIT_TYPE]
AUDIT_TYPE_INSERT = 1
AUDIT_TYPE_UPDATE = 2
AUDIT_TYPE_DELETE = 3
AUDIT_TEMPLATE = 'audit_%s'
def audited_record(shadow, record, audit_type):
"""Make a record into the audit record with a given type in place"""
record[AUDIT_OID.name] = record.id
del record['id'] # audit records gets their own id
del record['update_record'] # since we retrieve id, we get this ?!!?
del record['delete_record'] # since we retrieve id, we get this ?!?!
for k, v in record.items():
if isinstance(v, SetClassToIgnore): # since we retrieve id, we get these for all referencing tables
del record[k]
record[AUDIT_TIME.name] = request.now
record[AUDIT_USER.name] = 'unknown'
record[AUDIT_TYPE.name] = audit_type
shadow.insert(**record)
def audited_insert(self, table, fields):
shadow = self.shadow.get(table._tablename)
if not shadow: return self.unaudited_insert(table, fields)
rid = self.unaudited_insert(table, fields)
if isinstance(rid, int): # either int or Reference
record = table[int(rid)] # retrieve just inserted record
audited_record(shadow, record, AUDIT_TYPE_INSERT)
return rid
raise AuditError, "Cannot audit inserts to table %s"%table
# TODO: how do we audit a change to id? should we block it?
def audited_update(self, tablename, query, fields):
shadow = self.shadow.get(tablename)
if not shadow: return self.unaudited_update(tablename, query, fields)
db = self.db
table = db[tablename]
# find which records are about to be updated by this ....
rows = self.select(query, ['id'], {})
rows = [x['id'] for x in rows] # we only care about the ids.
count = self.unaudited_update(tablename, query, fields) # do the update
# retrieve updated records
rows = self.select(table.id.belongs(rows), [str(field) for field in table], {})
for record in rows:
audited_record(shadow, record, AUDIT_TYPE_UPDATE)
return count
def audited_delete(self, tablename, query):
shadow = self.shadow.get(tablename)
if not shadow: return self.unaudited_delete(table, query)
db = self.db
table = db[tablename]
# retrieve records about to be deleted
rows = self.select(query, [str(field) for field in table], {})
count = self.unaudited_delete(tablename, query) # do the delete
# retrieve updated records
for record in rows:
audited_record(shadow, record, AUDIT_TYPE_DELETE)
return count
# using magic described in:
# http://stackoverflow.com/questions/1015307/python-bind-an-unbound-method
def patch_adapter(a):
if hasattr(a, 'shadow'): return
print 'patching adapter %s' % id(a)
a.shadow = {} # patch instance every time
a.unaudited_insert = a.insert
a.unaudited_delete = a.delete
a.unaudited_update = a.update
a.insert = audited_insert.__get__(a, a.__class__)
a.update = audited_update.__get__(a, a.__class__)
a.delete = audited_delete.__get__(a, a.__class__)
# remove field constraints when creating audit table.
# TODO - switch all references to weakref or at least ON CASCADE RESTRICT?
def with_audit(table):
name = table._tablename
dal = table._db
adapter = dal._adapter
patch_adapter(adapter)
if name in adapter.shadow: return # audit already set
fields = AUDIT_FIELDS + [Field(field, table[field].type) for field in table.fields if field != 'id']
shadow = dal.define_table(AUDIT_TEMPLATE%name, *fields)
adapter.shadow[name] = shadow
