[Zope-DB] Automatic capture of user ids for auditing

[Charlie Clark]

Am 07.11.2007, 21:11 Uhr, schrieb Ken Winter <ken at 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