Re: audit table containing Select statements submitted

Jim,

> I am trying desperately to find a way to create an audit table to
> log...who SELECTED what... as I am dealing with HR data and Health Care
> Patient Data

Well, the issue with doing this by trigger or RULE is that unlike
updates and deletes, SELECTS do *not* guarentee single execution. For
example, if the table is on the loop end of a nested loop, it could be
fired hundreds or thousands of times. This is the reason why we
recommend against trying to build a trigger/RULE for SELECT auditing.

There are workarounds though.

One possibility, which I have used, is to not allow the application
access to the base tables but instead force it to use Set Returning
Functions. For example, instead of:

SELECT * FROM users NATURAL JOIN permissions WHERE name = 'Joe';

you would do: SELECT * FROM view_users_perms(user,'Joe');

The SRF then can easily log the select statement. This also provides
you with the additional security of knowing that a user who hacks the
database connection cannot launch ad-hoc queries which the application
would not allow. I've used this approach lots for web applications for
that reason.

Secondly, you can use the log. We've discussed on this list making it
possible to log in CSV, XML or other database-digestable format.
Unfortuantely, there doesn't appear to be much momentum behind that; I
don't know that anyone is writing any code presently. Sponsorship?

In the immediate time, you can (others have done this) have the log
stream to a parser which digests the log and writes out different files
(database-loadable) depending on the logged activity recorded. I don't
know of any OSS code which does this but you can probably get advice on
the lists fromm people who have done it custom.

Good luck!

--Josh