Enhancing database security - Application Contexts & Virtual Private Database

< Hypothesis >
(1) You are a DBA for your company's database.
(2) Your company's database has a schema called HR where you basically store all the HR-related data.
(3) There is a table called employees in HR schema. This table contains name, emp_id, position, salary andcommission for all the employees in your company.
(4)  All the employees can access employees table. (Granted 'Select' on the table) 
(5) However, for security and data-confidentiality reason, you (DBA) need to find a way to limit the data access of each employee so that they can only see their own record. In other words, you should be able to control the data access of each user in row-level.

Issue   
Oracle database provides several security measures.  Password profile, authentication methods, system/object privileges and roles. However, none of these measure provides an data access control in row level. Object privileges can control data access on object level at most.
Impact
Because all the employees are granted "SELECT" object privilege on employees table. They can basically view anybody's records including salary and commission, which we don't want.

Solution
We can make use of an application context and Virtual Private Database to overcome this issue.
Virtual Private Database(VPD) combines an application context and fine-grained access control. The following steps show how application context and VPD are implemented. However the steps are simplified and doesn't show all in details.

1. Application Context: When we create an application context, a memory area is allocated to the context. An application context is like an associative array. We add attributes to the context and set values of the attributes. In regard to VPD, we can store users' current session information in an application context. Then based on the user's session information stored in the context, we can control a fine-grained data access control.

(1) Make sure HR user has all privileges required to create an application context.
- We can just let HR user to control the data access in HR schema.
- We can grant roles and privileges to HR as followings.
       >  GRANT create session  TO HR WITH ADMIN OPTION;
       >  GRANT create any context, drop any context, create role, alter any role, drop any role,

           create table, create procedure, create any trigger, administer database trigger,

            create any directory, alter profile, create profile, drop profile, audit system, alter system,

            grant any object privilege TO HR;

       >   GRANT execute on DBMS_SESSION to HR;

(2) Creating an application context (as HR user)

      SQL> CREATE CONTEXT context_name USING package_name ; 

(3) Create a PL/SQL package that set the context attributes. 
- This package should contains a procedure that sets the context attributes.
       ...
       procedure set_emp_id
       IS 
          v_emp_id NUmber;
      Begin
         SELECT employee_id INTO v_emp_id FROM hr.employees
         WHERE email = SYS_CONTEXT('userenv', 'session_user') ;
          DBMS_Session.SET_CONTEXT('context_name','attribute_name','v_emp_id) ;
      End;
      ...
(4) Create a trigger that calls the PL/SQL package to set the context attributes when a user logs in.
          Create or Replace TRIGGER hr_context_logon
          After Logon on database
          Begin
                Package_Name.procedure_name;
          End;

2. Virtual Private Database - fine-grained access control implementation. Create a security policy function thatreturns a predicate(a Where condition).
(1) GRANT execute on dbms_rls to HR. Grant exempt access policy to HR.
(2) Create a policy function as following (as HR user)
...
Funiction limit_emp( object_schema IN varchar2, object_name varchar2)
  return varchar2
Is
Begin
   return 'emp_id = SYS_CONTEXT('context_name', 'attribute_name')';
END;
...
(3) Creating the policy (as HR user)
  SQL>    dbms_rls.add_policy( object_schema=>'HR', object_name=>'orders', 
                       policy_name=> 'policy_name', function_schema=>'HR', 
                       policy_function=> 'package_name.fuction_name', statement_types => 'select');

All set...  Now when an employee run a select statement against EMPLOYEES table, she/he should only see his/her own record only. The security function returns a predicate such as "emp_id =  xxx" This xxx is the employee id value that was stored in the application context. This application context was created when the employee logged in.