How to identify user editing the database (the application user) ?

[fuinhaazul]

Hi

I have a question that hunts me for years.

Normally on JEE development we use a connection pool on a single database user.

But everybody ask me about how to identify the web application user that is really doing stuff on the database. If there is 500 users, they want to know who they are. The solution could be only for Oracle.

There is any way to do this on Wildfly? Maybe implementing my own connection pool manager? But, how to get the aplication user there?

Really thanks for any help.

[wdfink]

Not sure whether I understand the question correct.

I've had implemented extra columns which contain the user and change date.

The JPA objects are set these informations automaticaly on every change.

[jaikiran]

There was a similar question asked in one another forum recently and some members suggested using an interceptor to keep track of who the logged in user is http://www.coderanch.com/t/633774/Servlets/java/clean-log-database-operation-issued. Perhaps that might help? If not, tell us bit more about your application.

[fuinhaazul]

I've had implemented extra columns which contain the user and change date.

The JPA objects are set these informations automaticaly on every change.

Yes. but when the user delete some row, how to know who? and another question. Who is calling stored procedure XXX right now?

jaikiran pai escreveu:

There was a similar question asked in one another forum recently and some members suggested using an interceptor to keep track of who the logged in user is http://www.coderanch.com/t/633774/Servlets/java/clean-log-database-operation-issued. Perhaps that might help? If not, tell us bit more about your application.

jaikiran: Funny thing. The way they sugested on this link is exactly what i was doing to workaround the issue. In my case i was using an EJB interceptor to always call some database specific function that set the current user (in v$session for oracle).  Also I used this approach to create a schema based multi tenancy. Based on some rules i managed to create the user principal with relevant informations and then call a "set schema" function that points me to the right database schema.

Is there a better way for doing this? I found out "Oracle proxies" but the examples was only for Oracle ADF that have "ADFContext". (https://blogs.oracle.com/imc/entry/how_to_use_database_proxy) .

[fuinhaazul]

Jaikiran, do you know if this is the right way? Maybe there is something especific for  do that?

Thanks.