You may be aware of the old USERENV function, which returned session values such as the user's session_id, language, and whether the user had DBA privileges. Although USERENV still exists for legacy reasons, it was superseded by a new function in Oracle 8i, SYS_CONTEXT.
SYS_CONTEXT does more than just retrieve USERENV data—it can also retrieve application-defined data values. An application context is a set of names (attributes) and matching data (values) that can be retrieved by SYS_CONTEXT. For example, to retrieve the user's department number for a human resources application, the application might include a line like this:
v_dept := SYS_CONTEXT('HR_CONTEXT', 'DEPT'); The After Logon database trigger introduced in Oracle 9i is an easy way to initialize the attributes in an application context. After a user successfully logs on to Oracle, the trigger fires and executes a stored procedure in the designated package to look up data and place it in the context via the DBMS_SESSION.SET_CONTEXT procedure.
There are several benefits to this method:
- It performs better. The application context data is stored in the SGA, so accessing it avoids repeated disk reads by the application to look up the data.
- It is secure. The only code allowed to change or clear the context is a single PL/SQL code object, usually a package, associated with the context. It is this package that the After Logon trigger executes.
- It is flexible. You can create as many contexts as you need, and each can have an unlimited number of attribute-value pairs.
Listing A shows a PL/SQL package called HR_CONTEXT_PKG. Its purpose is to initialize the context HR_CONTEXT by looking up which department the current user session should be associated with.
You can then use the following CREATE CONTEXT statement to create the HR_CONTEXT namespace and associate the package to it:
CREATE CONTEXT hr_context
USING HR.HR_CONTEXT_PKG;
The following code shows a Logon Trigger that calls the secure package to initialize the context for users as they log on. If there is an error locating the correct department, the trigger handles it via an EXCEPTION. Otherwise, users without a department setting would not be able to log on at all.
CREATE OR REPLACE TRIGGER DBT_LOGON
AFTER LOGON
ON DATABASE
BEGIN
HR.HR_CONTEXT_PKG.INITIALIZE_HR_CONTEXT;
EXCEPTION
WHEN OTHERS THEN
NULL;
END;
/
The result is that any application can now find out which department is associated to the current user without doing multiple logical reads to look it up from a table. For example:
SELECT SYS_CONTEXT('HR_CONTEXT','DEPT') FROM DUAL; will return the department_id of the current user.
Miss a tip?
Check out the Oracle archive, and catch up on our most recent Oracle tips.
Bob Watkins (OCP, MCDBA, MCSE, MCT) is a computer professional with 25 years of experience as a technical trainer, consultant, and database administrator. Visit Bob's site.
