Database Journal
MS SQL Oracle DB2 Access MySQL PostgreSQL Sybase PHP SQL Etc SQL Scripts & Samples Links Database Forum

» Database Journal Home
» Database Articles
» Database Tutorials
MS SQL
Oracle
DB2
MS Access
MySQL
» RESOURCES
Database Tools
SQL Scripts & Samples
Links
» Database Forum
» Sitemap
Free Newsletters:
DatabaseDaily  
News Via RSS Feed


follow us on Twitter
Database Journal |DBA Support |SQLCourse |SQLCourse2
 

Featured Database Articles

Oracle

Posted Jun 26, 2003

It All Depends on the CONTEXT: Using Oracle's SYS_CONTEXT Function - Page 2

By Jim Czuprynski

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.



Oracle Archives

Comment and Contribute

 


(Maximum characters: 1200). You have characters left.

 

 




Latest Forum Threads
Oracle Forum
Topic By Replies Updated
Oracle Data Mining: Classification jan.hasller 0 July 5th, 07:19 AM
Find duplicates - Unique IDs Lava 5 July 2nd, 08:30 AM
no matching unique or primary key rcanter 1 April 25th, 12:32 PM
Update values of one table based on condition of values in other table using Trigger Gladiator 3 February 29th, 06:01 PM