Access Controls

David Merrill dmerrill@lupercalia.net
Tue, 2 Jan 2001 13:20:29 -0500


On Mon, Jan 01, 2001 at 11:43:35PM -0800, Dave Peticolas wrote:
> David Merrill writes:
> > I read this again and changed my mind, and I'll tell you why...
> > 
> > When a record is changed, it is moved to the audit table, and a new
> > record is generated. That means a new GUID as well. So if the record
> > exists in the transaction table, it is necessarily the same "original"
> > data.
> 
> What is the 'audit table'? How does it work?

Here are my notes. Perhaps I should choose another term, in light of
the accounting use.

I am very unsure about how to handle the uuids when creating new
records. My notes say that the uuid always follows the data, so an
edited record gets a new uuid. This is tremendous implications for the
client.


AUDITING
--------

The database provides a complete audit trail of all changes to data. The audit
trail mechanism uses the "breadcrumb" approach. No financial data is ever
changed or deleted in the physical database. Instead, records that become
invalid are moved into an audit table, before their data is changed.

When a record is created, it is marked with the user's guid and a timestamp,
so we always know who created the record and when.

If a transaction record is changed or deleted, it is copied into its 
corresponding audit table (named the same as the regular table but appended
with "_audit"). The audit table has a few extra fields to hold audit 
information. These fields are:

new_guid		CHAR(32)	guid assigned to replacment record
change_type		CHAR		indicates 'D'elete or 'U'pdate
change_dbuser_guid	CHAR(32)	who made the change
change_timestamp	TIMESTAMP	when the change happened

If a record is deleted, it is copied into the audit table, the change_type field
is set to 'D', and the other fields are populated. For transactions, all related
splits are necessarily deleted also, so they are treated likewise. Then the
original record is deleted.

When a record is modified, the original record is copied into the
audit table, and change_type is set to "U" for update. change_timestamp and
change_dbuser_guid are populated. Then the original record is updated.

The guid which travels with the original data is left alone in all cases,
but there is no primary key on the guid in the audit table, so this isn't a
problem. The newly created record is given a brand new guid. A guid always
uniquely represents a state of the record.

Of course, all of this is handled completely by the db, and when the engine
requests records it gets a simple recordset of the active records. Only the
database knows that any of these audit records even exist, unless an admin
comes in via the backdoor of the db and browses the records.

FIXME: Which tables require an audit trail? All of them? Financial data only?


-- 
Dr. David C. Merrill                     http://www.lupercalia.net
Linux Documentation Project                dmerrill@lupercalia.net
Collection Editor & Coordinator            http://www.linuxdoc.org
                                       Finger me for my public key

Corn and Grain, Corn and Grain
All that falls shall rise again