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.
