Mailing List Archive: Automatic capture of user ids for auditing

Automatic capture of user ids for auditing

Nov 6, 2007, 11:12 AM

Post #1 of 5 (3999 views)

I have a Zope site with a PostgreSQL (8.1) back end. Only authenticated
users have access to the site and the database. Each database table has
created_by and last_updated_by columns for auditing purposes.

The question here is: How to get these audit columns *automatically*
updated with the user id of the user who initiates each INSERT or UPDATE
action? "Automatically" means that the application doesn't have to do
anything to get these updates to happen.

The problem is that I have multiple application-level, Zope-authenticated
end users, but only one user at the database level. So when end user 'fred'
initiates an UPDATE action (implemented as a ZSQL method), PostgreSQL
doesn't know which end user initiated it. So the problem is: how to tell
PostgreSQL that 'fred' was the user who initiated this transaction? Only
with this piece of info can database-side machinery (triggers or whatever)
put 'fred' into the last_updated_by column of each record that was updated
by that UPDATE action.

The idea of simply requiring every ZSQL method to update the audit column
explicitly (e.g. to have every UPDATE command look something like "UPDATE
... SET last_updated_by = request.AUTHENTICATED_USER, ...") fails the
"automatically" test. It's not a good idea to leave system auditing to the
whims and foibles of each application developer.

It seems like the components that might be able to pass this info to the
database are the ZSQL methods and/or the database adapter (PsycopgDA, in
this case). So, questions:

1. Is there any way to get the info across with either of these components
as they currently exist? If so, how?

2. Would it be possible to modify or add on to either of the to do what is
needed? If so, how?

3. Is having one Dhatabase user per Zope-level end user an alternative worth
considering?

4. Any other ideas about how to solve this problem?

~ TIA
~ Ken

P.S. I have found two previous discussions of (approximately) this issue:

http://www.gossamer-threads.com/lists/zope/db/48026

http://archives.postgresql.org/pgsql-general/2005-12/msg01323.php

Neither of these seemed to produce an answer (that I could understand).

_______________________________________________
Zope-DB mailing list
Zope-DB@zope.org
http://mail.zope.org/mailman/listinfo/zope-db

Re: Automatic capture of user ids for auditing [ In reply to ]

charlie at egenix

Nov 6, 2007, 12:26 PM

Post #2 of 5 (3834 views)

Permalink

Am 06.11.2007, 20:12 Uhr, schrieb Ken Winter <ken@sunward.org>:

Hi Ken,

an interesting problem although probably not unique!

> It seems like the components that might be able to pass this info to the
> database are the ZSQL methods and/or the database adapter (PsycopgDA, in
> this case). So, questions:
> 1. Is there any way to get the info across with either of these
> components
> as they currently exist? If so, how?

Not currently as far as I know.

> 2. Would it be possible to modify or add on to either of the to do what
> is
> needed? If so, how?

Yes, you could quite easily extend the DA to pass the current Zope user
and possibly extend any statements as required. Not sure if that is how
you would want to do it that way. Have you an idea how you want to do this
on the PostgreSQL side? Any chance of accepting an additional parameter?

> 3. Is having one Dhatabase user per Zope-level end user an alternative
> worth
> considering?

I would think not as it is very much against the connection pool principle.

> 4. Any other ideas about how to solve this problem?

You might want to authenticate users for PostrgreSQL and Zope against the
same system say using mod_auth? for apache and either XUF or PAS for Zope,
as both allow you to hold your users outside of Zope. I'm not sure whether
you wouldn't still be stuck with the problem of having the connection tied
to a one user while wanting to log another.

Charlie
--
Charlie Clark
eGenix.com

Professional Python Services directly from the Source
>>> Python/Zope Consulting and Support ... http://www.egenix.com/
>>> mxODBC.Zope.Database.Adapter ... http://zope.egenix.com/
>>> mxODBC, mxDateTime, mxTextTools ... http://python.egenix.com/
________________________________________________________________________

:::: Try mxODBC.Zope.DA for Windows,Linux,Solaris,MacOSX for free ! ::::

eGenix.com Software, Skills and Services GmbH Pastor-Loeh-Str.48
D-40764 Langenfeld, Germany. CEO Dipl.-Math. Marc-Andre Lemburg
Registered at Amtsgericht Duesseldorf: HRB 46611
_______________________________________________
Zope-DB mailing list
Zope-DB@zope.org
http://mail.zope.org/mailman/listinfo/zope-db

RE: Automatic capture of user ids for auditing [ In reply to ]

ken at sunward

Nov 7, 2007, 12:11 PM

Post #3 of 5 (3833 views)

Permalink

Charlie, thanks for the encouraging response!

What you suggest (below) is definitely the way I'd like to do this.
However, ZPsycopgDA and its communications with PostgreSQL are currently
black boxes for me, so I think I need some help taking the next steps.

A few hours of poking around have led me to the following fragmentary ideas.

* I have found what may be a likely candidate for the code that could be
customized: the method DB.query(self, query_string, max_rows=None,
query_data=None) defined (on my Windows system) in C:\Program Files\Plone
2\Data\Products\ZPsycopgDA\db.py.

* One idea is to parse the query_string in DB.query and, if it contains an
INSERT or UPDATE command, splice a clause to record the current user's id
into it (e.g. put "last_updated_by = request.AUTHENTICATED_USER" into the
SET clause of an UPDATE). Doing it this way should require nothing special
on the PostgreSQL side, since the user identifier would come through as part
of the INSERT or UPDATE statement.

* The idea of "extending the DA pass the current ZOPE user" seems cleaner.
However, I don't know how to get the DA to pass an additional parameter, and
I don't know how to get PostgreSQL to pick up the parameter so it can store
it as required. In other words, I don't understand this API, and I don't
know where it is documented.

* I'm not sure if any of the above is on the right track at all - and if
not, what is the right track?

I'd appreciate any guidance anybody can offer.

~ Thanks
~ Ken

> -----Original Message-----
> From: Charlie Clark [mailto:charlie@egenix.com]
> Sent: Tuesday, November 06, 2007 3:26 PM
> To: Ken Winter; Zope-DB List
> Cc: Jim Myers; Michael Adams
> Subject: Re: [Zope-DB] Automatic capture of user ids for auditing
>
> Am 06.11.2007, 20:12 Uhr, schrieb Ken Winter <ken@sunward.org>:
>
...
>
> > 2. Would it be possible to modify or add on to either the ZSQL methods
> > and/or the database adapter (ZPsycopgDA, in
> > this case) to do what is needed? If so, how?
>
> Yes, you could quite easily extend the DA to pass the current Zope user
> and possibly extend any statements as required.
> Have you an idea how you want to do this
> on the PostgreSQL side? Any chance of accepting an additional parameter?
>

_______________________________________________
Zope-DB mailing list
Zope-DB@zope.org
http://mail.zope.org/mailman/listinfo/zope-db

Re: Automatic capture of user ids for auditing [ In reply to ]

charlie at egenix

Nov 7, 2007, 1:00 PM

Post #4 of 5 (3832 views)

Permalink

Am 07.11.2007, 21:11 Uhr, schrieb Ken Winter <ken@sunward.org>:

> Charlie, thanks for the encouraging response!

Encouraging? Not sure if that's quite the right word! ;-)

Just assuming you did have connections per user, how would you go about
this?

> What you suggest (below) is definitely the way I'd like to do this.
> However, ZPsycopgDA and its communications with PostgreSQL are currently
> black boxes for me, so I think I need some help taking the next steps.
> A few hours of poking around have led me to the following fragmentary
> ideas.

> * I have found what may be a likely candidate for the code that could be
> customized: the method DB.query(self, query_string, max_rows=None,
> query_data=None) defined (on my Windows system) in C:\Program Files\Plone
> 2\Data\Products\ZPsycopgDA\db.py.

Yep, that's the place.

> * One idea is to parse the query_string in DB.query and, if it contains
> an
> INSERT or UPDATE command, splice a clause to record the current user's id
> into it (e.g. put "last_updated_by = request.AUTHENTICATED_USER" into the
> SET clause of an UPDATE). Doing it this way should require nothing
> special
> on the PostgreSQL side, since the user identifier would come through as
> part
> of the INSERT or UPDATE statement.

This is going to be your quickest way assuming you can write a good enough
parser so you don't catch statements with "INSERT" or "UPDATE" as part of
the parameters! Using the underlying .execute(SQLStatement, (paras,))
method on the cursor might be preferable and would be available from our
mxODBC Zope DA but this is unfortunately not compatible with ZSQL methods
which predate the Python DB-API. You might, however, be able to use a
stored procedure on the database to execute the query as rendered by the
ZSQL method with the user passed as a parameter to the stored procedure.
Again the mxODBC Zope DA would give you access to the .callproc(). No idea
whether you could write such a stored procedure but I don't see why not.

> * The idea of "extending the DA pass the current ZOPE user" seems
> cleaner.
> However, I don't know how to get the DA to pass an additional parameter,
> and
> I don't know how to get PostgreSQL to pick up the parameter so it can
> store
> it as required. In other words, I don't understand this API, and I don't
> know where it is documented.

Zope doesn't support it and unfortunately relational databases aren't
getting the attention they deserve in the Zope world at the moment.

Charlie

--
Charlie Clark
eGenix.com

Professional Python Services directly from the Source
>>> Python/Zope Consulting and Support ... http://www.egenix.com/
>>> mxODBC.Zope.Database.Adapter ... http://zope.egenix.com/
>>> mxODBC, mxDateTime, mxTextTools ... http://python.egenix.com/
________________________________________________________________________

:::: Try mxODBC.Zope.DA for Windows,Linux,Solaris,MacOSX for free ! ::::

eGenix.com Software, Skills and Services GmbH Pastor-Loeh-Str.48
D-40764 Langenfeld, Germany. CEO Dipl.-Math. Marc-Andre Lemburg
Registered at Amtsgericht Duesseldorf: HRB 46611
_______________________________________________
Zope-DB mailing list
Zope-DB@zope.org
http://mail.zope.org/mailman/listinfo/zope-db

Re: Automatic capture of user ids for auditing [ In reply to ]

maciej.wisniowski at coig

Nov 7, 2007, 1:17 PM

Post #5 of 5 (3830 views)

Permalink

>> * The idea of "extending the DA pass the current ZOPE user" seems
>> cleaner.
For me, changing DA seems to add 'audit' fields seems nasty. What if you
will have one specific table that doesn't need audit fields (this may
happen when you're using authentication sources that rely on DB and in
many other cases).

I think that much cleaner might be to create something like
AuditEnabledZSQLMethods that will render to sql that contains necessary
statements and use it instead of ZSQLMethods when necessary.

Above assumes that you'll create a Zope Product that (possibly) inherits
from ZSQLMethods. You may take a look at CCZSQLMethods as they are
example of extending ZSQLMethods.

--
Maciej Wisniowski
_______________________________________________
Zope-DB mailing list
Zope-DB@zope.org
http://mail.zope.org/mailman/listinfo/zope-db