Oracle Virtual Private Database Policy Functions VPD

Security features in the database.Virtual Private Database (VPD), a feature of Oracle Database 11g Enterprise Edition, was introduced in Oracle8i and is one of the most popular security features in the database

• Row level security>> We have Object level security by using Grants,synonyms, View but for row level we use VPD(Virtual Private Database Policy Functions)
• Virtual Private Databases (VPD) allow multiple users to access a single schema whilst preventing them from accessing data that is not relevant to them.
• 
  

create context employees using pkg_context;
/
--Context created.

---Package created

create or replace package pkg_context is
  procedure p_set_context(p_dept number);
  end;
  /

create or replace package body pkg_context is
  procedure p_set_context(p_dept number) is
  begin
  dbms_session.set_context(NAMESPACE=>'EMPLOYEES',ATTRIBUTE =>'DEPARTMENT',VALUE=>P_DEPT);
    END;
   END;

  /

begin
pkg_context.p_set_context(10);

end;
/

SELECT SYS_CONTEXT('EMPLOYEES','DEPARTMENT') from dual;

create or replace function f_get_dept_predicate(p_schema varchar2,p_obj varchar2)
   return varchar2 is
   a varchar2(20);
   BEGIN
   select sys_context('EMPLOYEES','DEPARTMENT') into a from dual;

  return 'deptno = sys_context(''EMPLOYEES'',''DEPARTMENT'')';
    end;
/


begin
 DBMS_RLS.ADD_POLICY(object_schema=> 'APPS'
                     ,object_name => 'EMP'
                     ,policy_name => 'EMP_DEPT_20'
                     ,function_schema => 'APPS'
                     ,policy_function =>'f_get_dept_predicate'
                     ,statement_types => 'SELECT,INSERT,UPDATE,DELETE');
 END;
/