global variables oracle !

Global Variables
You might have heard about global variables – variables that are set once during the session and are available to all processes in the same session. The simplest example of which can be a package variable as shown below:

create or replace package pkg_a as
    gv_deptno number;
 end;
When you want to assign a value to this variable, you can issue:

exec pkg_a.gv_deptno := 1
Later in the same session, you can retrieve the value of the variable, and it will be 1.

declare
     l_somevar number(1);
begin
    l_somevar := pkg_a.gv_deptno;
end;
This type of global variables are very useful in cases where there is a disconnect between processes in the same session, e.g. a database logon trigger might populate a variable that will be accessed by all SQL statements subsequently executed in the session. Since the logon trigger executes when the session is established, there is no other way a value can be passed from it to a SQL statement further in the session. Global variables are the vehicles.

The Flaw in Package Variables
There is a serious flaw in the global variables defined via package variables, as you will see in the example below. Suppose you use the example mentioned above to hold the user's department id, which is then used in a view to be used as a filter to show only the records of that department to the user. You define a post-logon trigger to set the value of the department.

  1  create trigger tr_set_deptno
  2  after logon
  3  on database
  4  declare
  5     l_deptno number;
  6  begin
  7     select deptno
  8     into l_deptno
  9     from scott.emp
 10     where ename = user;
 11     pkg_a.gv_deptno := l_deptno;
 12 end;
Now, you can define a function that returns the value of this global variable:

  1  create or replace function get_deptno
  2  return number is
  3  begin
  4     return pkg_a.gv_deptno;
  5  end;
Finally, you create a view that filters the employee records based on deptno:

  1  create or replace view vw_emp_filtered as
  2  select * from scott.emp
  3  where deptno = get_deptno
When the user SMITH (whose department number is 20) logs in and selects all records from this view, he sees only those of the department 20, not all.

SQL> conn smith/smith
SQL>select distinct deptno from vw_emp_filtered;
    DEPTNO
----------
        20
Similarly if the user JAMES of department 30 logs in, he sees only those of department 30. This system works very well, except when someone tries to abuse the system. Consider this situation when James is logged in now and selects from the view:

SQL> conn james/james
SQL>select distinct deptno from vw_emp_filtered;
    DEPTNO
----------
        30
This is as expected. James sees only the DeptNo 30. However, James now assigns the department no 20 to the global variable and reselects from the view:

SQL>exec pkg_a.gv_deptno := 20
PL/SQL procedure successfully completed.
SQL>select distinct deptno from vw_emp_filtered;
    DEPTNO
----------
        20
Alas! James can see the employees of department 20 now. Although the global variable was set to the correct value at the login time for James (preventing him from seeing other departments), there is no mechanism to prevent him from setting the global variable to a different value and bypass that security. In fact he can set the global variable to the entire list of department ids one after the other and see the employees of all departments. There needs to be a more secure global variable.

A Secure Global Variable
This is where application contexts come in handy. They are very similar to global variables in the sense that they are set once in the session and accessible throughout; but they are not assigned via a simple assignment unlike the package variables. First you define a context:

create context ctx_dept using set_ctx_dept
To create a context, you need the system privilege CREATE ANY CONTEXT. Note the clause "using set_dept_ctx", which means that the context can be set to a value only by a procedure called set_ctx_dept. Next, create that procedure.

  1  create or replace procedure set_ctx_dept
  2  (
  3     p_deptno        in number
  4  ) as
  5  begin
  6     dbms_session.set_context (
  7             'CTX_DEPT',
  8             'DEPTNO',
  9             p_deptno
 10     );
 11  end;
The context can be compared to a table. Analogous to columns, the context has attributes, which are set to values. The major difference is the attributes are not defined in advance. You can add any number of attributes and assign values to them later. The attributes are given in the second parameter to DBMS_SESSION. In this example there is an attribute DEPTNO. The third parameter is the value of the attribute, which is set from the parameter passed to the procedure. Once this is created, you can set the value of the attribute to 10 by calling the procedure just created.

call set_ctx_dept (10);
After this, if you wanted to check the value of the attribute, you will need to call the SYS_CONTEXT function.

SQL>select sys_context('CTX_DEPT','DEPTNO') from dual;
SYS_CONTEXT('CTX_DEPT','DEPTNO')
--------------------------------
10
Now that you see how the attribute value is assigned by calling the supplied package DBMS_SESSION, can you directly call it to set the value?

SQL>begin
  2     dbms_session.set_context ('CTX_DEPT','DEPTNO',10);
  3  end;
  4  /
begin
*
ERROR at line 1:
ORA-01031: insufficient privileges
ORA-06512: at "SYS.DBMS_SESSION", line 90
ORA-06512: at line 2
Note the error: "ORA-01031: insufficient privileges". The user does have privileges on this package DBMS_SESSION, so that's not the reason. The real reason is that the context can only be set by the procedure associated with it (set_ctx_dept) and not by any other means. This makes the procedure a "trusted" one of the context. Since the only way to set the context attribute is to call its trusted procedure, make the procedure airtight by not allowing it to be executed by anyone other than the privileged user. This can be used to act as a global variable for the department numbers in your original problem. Your new logon trigger now looks like:

  1  create or replace trigger tr_set_deptno
  2  after logon
  3  on database
  4  declare
  5     l_deptno number;
  6  begin
  7     select deptno
  8     into l_deptno
  9     from scott.emp
 10     where ename = user;
 11     set_ctx_dept (l_deptno);
 12 end;
It looks identical to the previous version, except line 11, where there is a context assignment. The new get_deptno function looks like:

  1  create or replace function get_deptno
  2  return number is
  3  begin
  4     return sys_context('CTX_DEPT','DEPTNO');
  5  end;
Again, instead of retuning the package variable, we have returned the context attribute. The view remains unchanged. Now when James logs in and selects from the view he gets the right department.

sqlplus james/james
SQL>select distinct deptno from vw_emp_filtered;
    DEPTNO
----------
        30
James does not have access to the procedure set_ctx_dept; so he won't be able to set the value of the context to something else, such as 20. This is exactly what you need in a secured environment.