Using session-based application contexts

The structure of a context consists of a namespace and the associated values it contains. The namespace or name is used for accessing the context's individual attributes, and their associated values held in memory. A namespace can be represented as an associative array with a name-type organization. The initialization of application contexts is performed using a PL/SQL package used in their definition. Generally application contexts are used in VPD implementations to retrieve session information to allow or to block access to certain data.

Next, we will create a locally initialized context type, using a package containing a procedure that initializes a value from the context. The values will be generated using a logon trigger. Next, we will define a view based on the returning value from the context.

Getting ready

All steps in this recipe will be performed on the database HACKDB.

How to do it...

  1. Create a view named VIEW_REG_DATA in the HR schema, based on the tables EMPLOYEES, DEPARTMENTS, JOBS, LOCATION, and COUNTRIES from the HR schema as follows:
    CREATE OR REPLACE VIEW hr.view_reg_data
AS
  SELECT e.first_name,
    e.last_name,
    e.email,
    e.phone_number,
    e.hire_date,
    j.job_title,
    e.salary,
    e.commission_pct,
    d.department_name,
    l.state_province,
    l.city,
    l.postal_code,
    c.country_name
  FROM hr.employees e
  JOIN hr.departments d
  ON e.department_id=d.department_id
  JOIN hr.jobs j
  ON e.job_id=j.job_id
  JOIN hr.locations l
  ON d.location_id=l.location_id
  JOIN hr.countries c
  ON l.country_id=c.country_id
  AND c.region_id= SYS_CONTEXT('HR_REGVIW_CONTEXT','REGION_ID')
  2. Issue the grant select privilege to vw_europe and vw_america on hr.view_reg_data as follows:
    SQL> grant select on hr.view_reg_data to vw_europe,vw_america;

Grant succeeded.

SQL>
  3. Create an application context named HR_REGVIW_CONTEXT as follows:
    SQL> create or replace context hr_regviw_context using set_region_context_pkg;

Context created.

SQL>

Context created.
  4. Create the context package and package body SET_REGION_CONTEXT_PKG as follows:
    SQL> create or replace PACKAGE              set_region_context_pkg IS
   PROCEDURE set_regionid; -- this procedure will activate the application context
    END;
    SQL> /
Package created.

    SQL>CREATE OR REPLACE
PACKAGE BODY set_region_context_pkg
IS
PROCEDURE set_regionid
IS
  v_region_id INTEGER;
BEGIN
  IF (SYS_CONTEXT('USERENV', 'SESSION_USER')    = 'VW_EUROPE') THEN
    v_region_id                                := 1;
  ELSIF (SYS_CONTEXT('USERENV', 'SESSION_USER') = 'VW_AMERICA') THEN
    v_region_id                                := 2;
  END IF;
  DBMS_SESSION.SET_CONTEXT('hr_regviw_context', 'region_id', v_region_id);
END set_regionid;
END set_region_context_pkg;SQL> /

Package body created.
  5. Create a logon trigger that will be used to set the region_id values in hr_regview context as user logs on as follows:
    SQL> 
CREATE OR REPLACE TRIGGER set_regionid_context_trg AFTER LOGON ON DATABASE
  BEGIN
   set_region_context_pkg.set_regionid;
  END;
SQL> /

Trigger created.

SQL>
  6. Next, connect as user vw_europe and vw_america, and check the values returned by hr_regviw_context:
    SQL> conn vw_europe
Enter password:
Connected.
SQL> select sys_context('hr_regviw_context','region_id') as REGION_ID from dual;

REGION_ID
--------------------------------------------------------------------------------
1

SQL> conn vw_america
Enter password:
Connected.
SQL> select sys_context('hr_regviw_context','region_id') as REGION_ID from dual;

REGION_ID
--------------------------------------------------------------------------------
2

SQL>
  7. Next, connect as users vm_europe and vw_america, and issue a SELECT DISTINCT command based on country_name from the HR.VIEW_REG_DATA view. The values will be retrieved based on the dynamic condition set by sys_context('HR_REGVIW_CONTEXT,'REG_ID'):
    SQL> conn vw_america
Enter password:
Connected.

SQL> select distinct country_name from hr.view_Reg_data;

COUNTRY_NAME
----------------------------------------
United Kingdom
Germany

SQL> 

SQL> conn vw_america
Enter password:
Connected.
SQL> select distinct country_name from hr.view_Reg_data;

COUNTRY_NAME
----------------------------------------
United States of America
Canada

SQL>

Note

Information about current session context namespace and attributes can also be found in the SESSION_CONTEXT dictionary view or by using the dbms_session.list_context procedure.

How it works...

The database session-based application context is managed entirely within the Oracle database. The Oracle database sets the values, and then when the user exits the session, automatically clears the application context values stored in cache.

Database session-based application contexts can be initialized locally, externally, or globally. In local initialization mode, the session data is retrieved for User Global Area (UGA). External initialization can be implemented using an external application (OCI, JDBC), a job queue process, or through a connected database link. Global initialization can be implemented using an external location, such as LDAP or OID.

There's more...

Oracle provides, for any connected session, a default application context named USERENV.

..................Content has been hidden....................

You can't read the all page of ebook, please click here login for view all page.
Reset
18.220.181.186