Other Information from the USERENV Namespace

Here is a sample of some other information available from this workspace that might whet your interest.

For a complete list of other parameters and more extensive detail on how the values returned might be used, please review the Oracle 9i SQL Reference Manual.

Building Our Own Namespaces

The USERENV namespace does store extensive information, but the power of SYS_CONTEXT does not stop there. I 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.

Jim Czuprynski is an Oracle DBA for a telecommunications company in Schaumburg, IL. He can be contacted at jczuprynski@gr.com.

Previous

Prev  [1] [2]