Using Global Context for auditing
It is often the case, thet when you use application servers there is a need to pass the username to the database, as the user logs onto the application which in turn uses a generic name to logon to the database.
This can be done through global context.
Create a procedure that updates the context.
CREATE OR REPLACE PACKAGE appusrpackage AS
PROCEDURE appusrset (usr CHAR);
END appusrpackage;
/
CREATE OR REPLACE PACKAGE BODY appusrpackage AS
PROCEDURE appusrset (usr CHAR) IS
BEGIN
DBMS_SESSION.SET_CONTEXT('appusr','id',usr);
END appusrset;
END appusrpackage;
/
Create a context, logon as sys.
CREATE CONTEXT appusr USING appusrpackage;
Grant execute on dbms_session to owner of the package:
The application the calls the procedure appusrpackage.appusrset('username') to set the username in memory. Auditing records can then be created in the database via triggers that read the username from memory via SELECT SYS_CONTEXT('appusr','id') INTO ls_appusr
FROM DUAL;