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

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

> @Richard: I've thought about having one DB user for each APP user. However,
> a coworker told me that it would infeasible to do that on the web
> enviroment, specifically for J2EE where a DB connection pool is used, so I
> gave up on that.

Why? You can always "SET SESSION AUTH TO <user>" before anything. It
works with transaction pools and you can even enhance your application
security by a second layer inside the database itself. DB passwords
don't need to be known by users since they won't connect directly and
your connection will be made with a priviledged user.

> @Jorge: Is this "connection id" you say equivalent to the "applicationid"
> mentioned in the ibm db2 article? If so, how could I get this data through
> my application?

It all depends on what you want to make it unique. I believe that a
simple process will be dedicated to each connection, so if you get its
PID you'll be done. Each time a user accesses the database you insert
or update a record with his PID and then you make your triggers work
with that. There will be more logic, but you got the idea.

Another option is using the transaction ID or something that always
change.

You just need a unique value that lasts for a connection and isn't
shared with any other user connected at the same time you are.

There are a lot of functions that you can use. You just have to be sure
when you want the information and what information you need. Take a
look at the ones available in pg_catalog for your specific PostgreSQL
version.

--
Jorge Godoy <jgodoy(at)gmail(dot)com>