SYS_CONTEXT

In Oracle/PLSQL, the sys_context function can be used to retrieve information about the Oracle environment.

sys_context( namespace, parameter)

NameSpace :  Is an Oracle namespace that has already been created.

If the namespace of 'USERENV' is used, attributes describing the current Oracle session can be returned.

Parameter : Is a valid attribute that has been set using the DBMS_SESSION.set_context procedure.


USERENV is the context (namespace) provided by oracle.


User defined NameSpaces:

Imagine as we are setting the session wide attributes ( like packaged variables ) and those are used within the session. ( ROW LEVEL Security Implementation )

Steps :

1. Create a namespace 

   CREATE OR REPLACE CONTEXT security_context USING security.pkg_security ACCESSED GLOBALLY;

2. Enclose the assignment of name/value pair in a package ( ex:security.pkg_security)

3. Assign (Name/Value) pair using DBMS_SESSIOn.SET_CONTEXT

   DBMS_SESSION.SET_CONTEXT(NAMESPACE => 'security_context'
                            ,ATTRIBUTE => empid
                            ,VALUE => 420
                           );

4. Attach the package to a trigger (DATABASE Level Trigger): Trigger needs to fire at the time of user login.



Example:

Create Or Replace package pkg_security
As
procedure set_attributes ;
end pkg_security;
/

Create Or Replace package body pkg_security
as
    Procedure set_attributes
    is
    Begin
       Dbms_Session.Set_Context('security_context','empid',420);
       Dbms_Session.Set_Context('security_context','sid',Sys_Context('userenv','sid'));
       Dbms_Session.Set_Context('security_context','ssn',123456789);
    end set_attributes;
End pkg_security;
/

create or replace trigger set_session_trigger after logon on database
Begin
Security.pkg_security.Set_Attributes;
end set_session_trigger;
/



login to a user:

select sys_context('security_context','empid),
         sys_context('security_context','sid'),
         sys_context('security_context','ssn')
from dual;