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

Hi Manuel,

each time the user sends a request I do more or less
> the following:
>
> Could a trigger be used to implement this ? Or are you doing this from the
application layer? My problem is that, like Til, I don't have full control
over my request cycle as I'm over a very high-level framework (Actually it
is an data-oriented application generator, called GeneXus).

Thanks,

Marcelo.

On 4/25/07, Manuel Sugawara <masm(at)fciencias(dot)unam(dot)mx> wrote:
>
> "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.
>