How To Audit Application Username Using Trigger

With default auditing Oracle stores Oracle username but not the application username. In order to store application username you need to set CLIENT IDENTIFIER for the application session which is connecting to the database.
 You can make use of DBMS_SESSION.SET_IDENTIFIER procedure to store the application username & later use this identifier in trigger to store the required information. The CLIENT IDENTIFIER which we are setting is nothing but V$SESSION.CLIENT_IDENTIFIER
Below is the sample code which can be used to achieve this.
EMP is the table which is audited. Application user calls UPDATE_SAL procedure to update the salary of the employee. p_app_username is the application username which is passed through UPDATE_SAL . This procedure sets CLIENT IDENTIFIER through DBMS_SESSION.SET_IDENTIFIER before updating EMP table.
Once the trigger is executed we can fetch the CLIENT IDENTIFIER using SYS_CONTEXT procedure & store this in EMP_AUDIT_LOGS table.
TEST CASE
=========

-- Create a sample table EMP.

CREATE TABLE emp( 
empno NUMBER(4) NOT NULL, 
ename VARCHAR2(10), 
sal NUMBER(7,2) 
); 

-- Create a Audit table which stores the audit information

CREATE TABLE emp_audit_logs ( 
id NUMBER(10) NOT NULL, 
log_timestamp TIMESTAMP NOT NULL, 
username VARCHAR2(30) NOT NULL, 
object_name VARCHAR2(30) NOT NULL, 
action VARCHAR2(10) NOT NULL, 
data CLOB 
); 

-- Add primary key constraint to Audit table

ALTER TABLE emp_audit_logs ADD ( 
CONSTRAINT audit_logs_pk PRIMARY KEY (id) 
); 

-- Create a sequence which will act as primary key for AUDIT_LOGS table.

CREATE SEQUENCE audit_logs_seq; 

-- Create a procedure to populate data into audit tables when ever a EMP table is updated. This procedure will be called from a trigger on EMP table.

CREATE OR REPLACE PROCEDURE audit_log_prc (p_username IN VARCHAR2, 
p_object IN VARCHAR2, 
p_action IN VARCHAR2, 
p_data IN CLOB) IS 
v_audit_logs_seq NUMBER(10); 
BEGIN 
SELECT audit_logs_seq.NEXTVAL INTO v_audit_logs_seq FROM DUAL; 
INSERT INTO emp_audit_logs ( 
id, 
log_timestamp, 
username, 
object_name, 
action, 
data) 
VALUES ( 
v_audit_logs_seq, 
SYSTIMESTAMP, 
p_username, 
p_object, 
p_action, 
p_data 
); 
END; 
/ 

-- Create a trigger on EMP table.

CREATE OR REPLACE TRIGGER emp_audit_trg 
AFTER UPDATE OR DELETE ON emp 
FOR EACH ROW 
DECLARE 
v_action VARCHAR2(30) := 'NONE'; 
BEGIN 
IF UPDATING THEN 
v_action := 'UPDATE'; 
ELSIF DELETING THEN 
v_action := 'DELETE'; 
END IF; 
audit_log_prc ( 
SYS_CONTEXT('USERENV','CLIENT_IDENTIFIER'), 
'EMP', 
v_action, 
'NEW.EMPNO=[' || :NEW.EMPNO || '] OLD.EMPNO=[' || :OLD.EMPNO ||']' || 
'NEW.ENAME=[' || :NEW.ENAME || '] OLD.ENAME=[' || :OLD.ENAME ||']' || 
'NEW.SAL=[' || :NEW.SAL || '] OLD.SAL=[' || :OLD.SAL || ']' 
); 
END; 
/ 

-- Let below procedure is called from application to update EMP table.
CREATE OR REPLACE PROCEDURE update_sal (p_app_username IN VARCHAR2, 
p_empno IN NUMBER, 
p_sal IN NUMBER) IS 
BEGIN 
DBMS_SESSION.SET_IDENTIFIER(p_app_username); 
UPDATE emp SET sal=p_sal WHERE empno=p_empno; 
DBMS_SESSION.CLEAR_IDENTIFIER; 
END; 
/  

You can use the following code also to put additional information for your client (Oracle Form) application.
For example I wrote below code in login button of my client's application.




:Global.Client_Identifier  :=

                                                       'TZ(' ||Webutil_Clientinfo.Get_Time_Zone        ||

                                                                                                                             ')IP('||Webutil_Clientinfo.Get_Ip_Address       ||

                                                                                                                             ')HN('||Webutil_Clientinfo.Get_Host_Name        ||

                                                                                                                             ')UN('||Webutil_Clientinfo.Get_User_Name        ||

                                                                                                                             ')'; 

                                                                               

    :Global.Client_Info  :=         'OS('||Webutil_Clientinfo.Get_Operating_System ||

                                                                                                                             ')LN('||Webutil_Clientinfo.Get_Language         ||

                                                                                                                             ')'; 

                dbms_session.set_identifier(:Global.Client_Identifier);

        dbms_Application_Info.set_client_Info(:Global.Client_Info);

The following code was added in every form of my client's application  (When-New-Block-Instance)

    dbms_Application_Info.set_client_Info('FRM('||get_application_property(current_form)  ||

                                        ':'||:System.Cursor_Block                   ||

                                         '/'||Get_block_Property(:System.Cursor_Block ,DML_DATA_TARGET_NAME )||

                                         ')'||:Global.Client_Info);

 You will see the info in V$SESSION as below.