Obtain logged on user in a PL/SQL trigger

by Alex Bocek
(Auburn, AL)

I have a trigger created against our address table to capture data manipulation transactions in an audit table as follows:


CREATE OR REPLACE TRIGGER ADVANCE.AU_TR_ADDRESS
AFTER DELETE OR INSERT OR UPDATE ON advance.address
REFERENCING OLD AS OLD NEW AS NEW
FOR EACH ROW

Each DELETE OR INSERT OR UPDATE operation is inserted into the audit table
au_audit_address wich has the following initial columns:
(SEQ
,TRAN_TIME
,ACTION
,USERCODE
,OS_USER
,IP_ADDRESS

On a DELETE, I insert audit values as:
(au_tr_address_seq.NEXTVAL
,SYSTIMESTAMP
,'D' --Delete
,sys_context('USERENV', 'SESSION_USER') --USERCODE
,sys_context('USERENV', 'OS_USER') --OS_USER

The issue is that (because this is a DELETE) I have no "New:operator". The 'OS_USER' being returned is from ORACLE and is not the "logged_on" operator doing the DELETE operation. So, an inaccurate operator is being populated into the audit table.

Can you suggest a way to capture the true logged on operator/user?

Comments for Obtain logged on user in a PL/SQL trigger

Average Rating starstarstarstarstar

Click here to add your own comments

Feb 25, 2015
Rating
starstarstarstarstar
Getting correct o/s user in Oracle
by: Anonymous

It's difficult to determine the cause of the problem without knowing more about your Oracle application. Here are a couple of obsrvations though:
(1) the SYS_CONTEXT function doesn't take any values from the row being updated/inserted/deleted - the USERENV namespace is global to the session and has nothing to do with the particular row for which the trigger is run, so whetever the action is, the o/s user returned should be the same.
(2) if the details of DELETEs on the table are not being obtained correctly becuase there is no NEW instance of the row then separate the delete trigger from the others or even better, have 3 distict triggers which call a common PL/SQL procedure which inserts the audit record into the audit table.

My suggestion would be to look at some of the other properties of Oracle's USERENV namespace such as AUTHENTICATED_IDENTITY, CLIENT_IDENTIFIER (set by BMS_SESSION.SET_IDENTIFIER), CLIENT_INFO (set by use of DBMS_APPLICATION_INFO) or ENTERPRISE_IDENTITY.

Full details of the properties of USERENV are in the Oracle Database SQL Refreence at http://docs.oracle.com/cd/B19306_01/server.102/b14200/functions165.htm

Click here to add your own comments

Join in and write your own page! It's easy to do. How? Simply click here to return to Oracle Questions.