audit trail for viewed data

pg.ddx.io  pgsql-admin@postgresql.org mailing list archive  
help / color / mirror / Atom feed

audit trail for viewed data
2+ messages / 2 participants
[nested] [flat]

* audit trail for viewed data
@ 2011-10-25 21:30 Vaughn, Adam (IMS) <VaughnA@imsweb.com>
  2011-10-29 04:19 ` Re: audit trail for viewed data Greg Sabino Mullane <greg@turnstep.com>
  0 siblings, 1 reply; 2+ messages in thread

From: Vaughn, Adam (IMS) @ 2011-10-25 21:30 UTC (permalink / raw)
  To: pgsql-admin

I have been asked to investigate creating an "audit trail" for viewed data. Essentially we have a requirement of "who saw what and when" (something similar to 'select' triggers and yes I know those don't exist and why). So far the who and when are easy but I haven't been able to track what was returned in the data set.

My first pass attempt at a solution was to use a set returning function that inserted to the audit trail and then I would revoke select from the base table and use security definer on the function to do the select. I tried something similar to the following:

create table a as select i as id from generate_series(1, 5000) as i;

create table b as select i as id from generate_series(4000, 7500) as i;

create table audit ( id integer, accessed timestamp with time zone, user_name text );

create function b_select(_user_name text) returns table(id integer) as '
  -- not sure what to use here for id because it does not seem like anything is in scope
  insert into audit (id, accessed, user_name)
   values (NULL, now(), $1);
  select * from b;' language sql;

select *
 from a
 inner join b_select('test') using (id)
 where b_select.id < 4250;

-- I was hoping this would return the 250 records viewed
select * from audit;

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?

Thanks in advance.

________________________________

Information in this e-mail may be confidential. It is intended only for the addressee(s) identified above. If you are not the addressee(s), or an employee or agent of the addressee(s), please note that any dissemination, distribution, or copying of this communication is strictly prohibited. If you have received this e-mail in error, please notify the sender of the error.

^ permalink  raw  reply  [nested|flat] 2+ messages in thread

* Re: audit trail for viewed data
  2011-10-25 21:30 audit trail for viewed data Vaughn, Adam (IMS) <VaughnA@imsweb.com>
@ 2011-10-29 04:19 ` Greg Sabino Mullane <greg@turnstep.com>
  0 siblings, 0 replies; 2+ messages in thread

From: Greg Sabino Mullane @ 2011-10-29 04:19 UTC (permalink / raw)
  To: pgsql-admin

-----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-----

^ permalink  raw  reply  [nested|flat] 2+ messages in thread

end of thread, other threads:[~2011-10-29 04:19 UTC | newest]

Thread overview: 2+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2011-10-25 21:30 audit trail for viewed data Vaughn, Adam (IMS) <VaughnA@imsweb.com>
2011-10-29 04:19 ` Greg Sabino Mullane <greg@turnstep.com>

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