**************************************
**** Building Our Own Namespaces *****
**************************************
The USERENV namespace does store extensive information, but the power of SYS_CONTEXT does not stop there. You can also create secured namespaces and store context in them for retrieval within a session or across the instance.
For example, if I create a new namespace via the CREATE CONTEXT command, I can then use SYS_CONTEXT to manage and control access to that namespace. In the example below (executed from the SYSTEM login), I’ve made the namespace accessible to any session for the database instance by specifying ACCESSED GLOBALLY
SQL> CREATE OR REPLACE CONTEXT hr_security
2 USING hr.pkg_security
3 ACCESSED GLOBALLY;
Context created.
Next, I create the corresponding package that will allow me to set parameters in the newly created context via calls to the DBMS_SESSION.SET_CONTEXT procedure:
SQL> CREATE OR REPLACE PACKAGE hr.pkg_security
2 IS
3
4 PROCEDURE set_security(
5 a_vcParameter VARCHAR2,
6 a_vcValue VARCHAR2
7 );
8
9 FUNCTION empname
10 RETURN VARCHAR2;
11
12 END pkg_security;
13 /
Package created.
SQL> CREATE OR REPLACE PACKAGE BODY hr.pkg_security
2 IS
3
4 PROCEDURE set_security(
5 a_vcParameter VARCHAR2,
6 a_vcValue VARCHAR2
7 )
8 IS
9 — Sets value for specified parameter in HRSECURITY namespace
10 BEGIN
11 DBMS_SESSION.SET_CONTEXT(
12 NAMESPACE => ‘HR_SECURITY’
13 ,ATTRIBUTE => a_vcParameter
14 ,VALUE => a_vcValue
15 );
16
17 END set_security;
18
19 FUNCTION empname
20 RETURN VARCHAR2
21 IS
22 — Returns employee’s name using employee ID set via SET_SECURITY parameter
23 vcEmpName VARCHAR2(64) := NULL;
24 BEGIN
25 SELECT last_name || ‘, ‘ || first_name
26 INTO vcEmpName
27 FROM hr.employees
28 WHERE employee_id = TO_NUMBER(SYS_CONTEXT(‘HR_SECURITY’, ‘EMPLOYEE_ID’));
29 RETURN vcEmpName;
30 END empname;
31
32 END pkg_security;
33 /
Package body created.
In this package, I have specified a call to the DBMS_SESSION.SET_CONTEXT procedure to create a new parameter and populate a corresponding value in the HR_SECURITY namespace. I’ve also built a function that returns a formatted string containing the employee’s last and first names based on the value stored in that namespace for EMPLOYEE_ID in the HR_SECURITY namespace.
The script below shows the results of calling the new package to set the value for the EMPLOYEE_ID parameter within the namespace and then using SYS_CONTEXT to retrieve the value from the namespace to get the employee’s name:
SQL> SET SERVEROUTPUT ON
SQL> DECLARE
2 lvc_employee_id VARCHAR2(255) := NULL;
3 lvc_empname VARCHAR2(64) := NULL;
4
5 BEGIN
6 PKG_SECURITY.set_security(’employee_id’, ‘302’);
7
8 BEGIN
9 SELECT SYS_CONTEXT(‘HR_SECURITY’, ‘EMPLOYEE_ID’)
10 INTO lvc_employee_id
11 FROM DUAL;
12 END;
13 DBMS_OUTPUT.PUT_LINE( ‘Value for HR_SECURITY.EMPLOYEE_ID via USERENV call is ‘ || lvc_employee_id);
14
15 lvc_empname:= PKG_SECURITY.EMPNAME;
16 DBMS_OUTPUT.PUT_LINE(‘Employee Name: ‘ || lvc_empname);
17
18 END;
19 /
Value for HR_SECURITY.EMPLOYEE_ID via USERENV call is 100
Employee Name: King, Steven
PL/SQL procedure successfully completed.
Conclusion
I have not completely explored the myriad ways SYS_CONTEXT can make my life as a DBA and PL/SQL developer easier, but it holds a lot of promise for securing sensitive information when using other namespaces besides USERENV. I’m hoping that this versatile function will be expanded to utilize other
Oracle-populated namespaces in future releases of Oracle.