auditing with usernames in database

joris77 · **Joined:** Wed Feb 11, 2004 7:50 pm **Posts:** 10

Hibernate version: 3.1.2

Name and version of the database you are using: Oracle 8.1.7

How can I pass a j2ee username to the database (in some kind of context) so that I can use it for auditing purposes in triggers. I don't want to pass it through objects because that gives me trouble with deleting (no record anymore) and pure link tables (no objects).

Thanks anyway,

Joris Wijlens

tenwit · **Joined:** Thu Dec 23, 2004 9:08 pm **Posts:** 2008

Triggers have access only to data in the table. Use stored procedures if you need other information.

joris77 · **Joined:** Wed Feb 11, 2004 7:50 pm **Posts:** 10

Hi,

No it does work with triggers.

It took me half a day of strugling to find out how it worked but ....

In Oracle you can access the sys.v_$session view from triggers. This view has a client_identifier column. This column can be accessed in a J2ee application by invoking the setClientIdentifier method on the OracleConnection object. This way you can pass j2ee usernames to the database. Vendor specific as hell, but allowed at my site.

The hardest part was to invoke the Oracle connection from a hibernate session. In the end I did it like this but I don't know if this is the proper way. Is there a better way to invoke the connection?

Code:

Connection connection = sessionFactory.getCurrentSession().connection();
Proxy l_connectionProxy = (Proxy)connection;
InvocationHandler l_invocationHandler = Proxy.getInvocationHandler(l_connectionProxy);
l_invocationHandler.invoke(l_connectionProxy,OracleConnection.class.getMethod("setClientIdentifier",new Class[]{String.class}),new Object[]{username});

The code for the trigger is:

Code:

CREATE OR REPLACE TRIGGER DAO.AUDIT_TRIGGER
BEFORE DELETE OR INSERT OR UPDATE
ON DAO.PERSON 
REFERENCING NEW AS NEW OLD AS OLD
FOR EACH ROW
DECLARE
tmpVar sys.v_$session.CLIENT_IDENTIFIER%type;
BEGIN
   SELECT ses.client_identifier INTO tmpVar FROM sys.v_$session ses where ses.audsid = userenv('sessionid');
   :new.cid := tmpVar;
END ;

person is the table that is audited and cid is the column for the username. The user has to have select rights on the v-$session view.

Joris

laurentschneider · **Joined:** Tue May 08, 2007 11:07 am **Posts:** 1

I have googled a bit to search how to set the client identifier, and the joris77 solution seems to the wiser I found!

About the trigger, you can do better with sys_context and setting :new in a before delete trigger hardly makes sense

Code:

CREATE OR REPLACE TRIGGER DAO.AUDIT_TRIGGER 
BEFORE INSERT OR UPDATE 
ON DAO.PERSON 
FOR EACH ROW 
BEGIN 
   :new.cid := sys_context('USERENV','CLIENT_IDENTIFIER'); 
END ;

abbala · **Joined:** Thu Jan 07, 2010 1:26 am **Posts:** 1

Thanks for taking the time to help, I really apprciate it.