[Tfug] RDBMS audit trails?

[Bexley Hall]

Hi, Claude,

--- On Wed, 7/30/08, Claude Rubinson <rubinson at u.arizona.edu> wrote:

> > Are there built-in mechanisms to implement audit
> trails in
> > most RDBMS's?
> 
> Nothing that's standardized.  Both PostgreSQL and MySQL
> provide mechanisms for monitoring what's going on with the
> database.  My memory may be failing me but I believe that
> MySQL writes to log files while PostgreSQL stores the data
> within the db (or maybe vice versa).

PostgreSQL seems to like to do *everything* in tables so I
would assume the former.  I'll snoop around.

> > E.g., I *think* I can do this by creating a bunch of
> triggers in
> > PostgreSQL.  (??)  But, what about things like
> MSAccess, MySQL,
> > etc.?
> >
> > I'm just looking for entries like:
> > 
> > record #, who, when
> 
> It's trivial to set up triggers to do such a thing but
> by doing so you're doubling your number of transactions.

Yes.  But, I figure I could treat the "log" table as a
FIFO and limit its size in the trigger code.  I had
assumed something like this would be something that
users would *want* -- even if only for testing
purposes -- hence the question.

> If you have a low number
> of transactions, a decent RDBMS won't sneeze at this. 
> But it's ugly,
> not particularly scalable, and certainly not a "best
> practice."

So, for applications that inherently *demand* an audit
trail (e.g., banking, some medical, etc.) you rely on
the application to implement the audit trail "reliably"?

> You're generally better off using the built-in
> mechanisms or one of
> the many available log analyzers out there.

Thx!
--don