Re: Audit-trail engine: getting the application's layer user_id

"Marcelo de Moraes Serpa" <celoserpa(at)gmail(dot)com> writes:

> I'm sorry Manuel, but after some time trying to fully understand your
> approach, I think I really don't have the required elements to do so.
>
> How do you pass your application's usename to this table? Or you don't keep
> the username at all?
>
> Could you give a more concrete example? Maybe showing the spots on your
> application where you called these functions and why?

I keep my user-names (agents) in the database along with a hashed
version of their passphrases, when a user logs in I have a procedure
written in plpgsql that checks the provided passphrase against the one
in the database and if they match the user is granted a session, and
the a corresponding row inserted in the session table. I keep the user
information (the session id and a key) in the session of the web tier
(I'm using java servlets but the concept is the same for other
frameworks). Now, each time the user sends a request I do more or less
the following:

retrieve from the web session the id of the session in the database
request a fresh connection from the pool
check if the session is still alive (if not throw an exception)
set the session id of the user
handle the user request
reset the session id
return the connection to the pool

The implementation details are left to the reader ;-). Hope that helps

Regards,
Manuel.