From: Greg Sabino Mullane <greg@turnstep.com>
To: pgsql-admin@postgresql.org
Subject: Re: audit trail for viewed data
Date: Sat, 29 Oct 2011 04:19:52 -0000
Message-ID: <dda160876c39e9c7f593f5ddad1482bc@biglumber.com> (raw)
In-Reply-To: <9B23E699D2D157499F6DCDE78306E85B09C6B128@VARUNA.omni.imsweb.com>
-----BEGIN PGP SIGNED MESSAGE-----
Hash: RIPEMD160
> I have been asked to investigate creating an
> "audit trail" for viewed data.
...
> My first pass attempt at a solution was to use a set returning
> function that inserted to the audit trail...
...
> I'm sure this has to do with the function not knowing anything
> about the join inclusion/exclusion nor the where clause, but
> I'm wondering if this is a problem that anyone else has
> experienced in the past. Is there a way to do this at the
> database level or do we need to try to build something
> into the application to do the tracking?
It's easy enough at the database level: just make all access
go through stored procedures. In the example you gave, disallow
direct access to table 'a' and if they need something like
that query, make them call this:
select rows_a_join_b_id_less(4250);
Obviously you can make the functions a little more generic,
but the golden rule is all direct table access is forbidden.
They want something from the database, they must go through
one of your special procs. Then you can log who viewed what
data and when :). That's going to probably end up a /very/
large audit log, but without getting into a lot more
complexity, that's the price you pay for such detailed logging.
The only other non-application option I can see is to have
something recording things at a low-level, e.g. wireshark.
Which makes the database and application changes easy (e.g.
no changes at all!) at the high cost of parsing the logs when
you need to make sense of them.
- --
Greg Sabino Mullane greg@turnstep.com
End Point Corporation http://www.endpoint.com/
PGP Key: 0x14964AC8 201110290019
http://biglumber.com/x/web?pk=2529DF6AB8F79407E94445B4BC9B906714964AC8
-----BEGIN PGP SIGNATURE-----
iEYEAREDAAYFAk6rfs0ACgkQvJuQZxSWSsg9AwCeKRF+lPlX+lpxIxkjOacoYXMO
1WQAn1ONgX8T9aZffHejetCY+yOq5PTP
=/54K
-----END PGP SIGNATURE-----
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-admin@postgresql.org
Cc: greg@turnstep.com
Subject: Re: audit trail for viewed data
In-Reply-To: <dda160876c39e9c7f593f5ddad1482bc@biglumber.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox