Oracle Virtual Private Database

SA,
What Is Oracle Virtual Private Database?
Oracle Virtual Private Database (VPD) enables you to create security policies to control database access at the row and column level. Essentially, Oracle Virtual Private Database adds a dynamic WHERE clause to a SQL statement that is issued against the table, view, or synonym to which an Oracle Virtual Private Database security policy was applied.

Oracle Virtual Private Database enforces security, to a fine level of granularity, directly on database tables, views, or synonyms. Because you attach security policies directly to these database objects, and the policies are automatically applied whenever a user accesses data, there is no way to bypass security.

You can apply Oracle Virtual Private Database policies to SELECT, INSERT, UPDATE, INDEX, and DELETE statements.

To implement Oracle Virtual Private Database, you must create a function to generate the dynamic WHERE clause, and a policy to attach this function to the objects that you want to protect.
I will explain that definition using example below.

Sample Case

we have department (10,20,30) in EMP table and want
1-user "user10" to select employees in department 10 only.
2-user "user20" to select employees in department 20only.
3-user "user30" to select employees in department 30 only.

Step 1 : Create Policy Group
This allows you to manage your policies, you can assign a policy to a policy group.
We can use create policy group from Toad

Or run the below code
BEGIN
  SYS.DBMS_RLS.CREATE_POLICY_GROUP
  (
   object_schema => 'SCOTT'
   ,object_name => 'EMP'
   ,policy_group => 'EMP_POLICIES'
  );
END;

Step 2 : Create Policy Function 
Policy function will return varchar2 and that is predicate that is added to where clause at table.
Function signature must be as done at function example.
This can be in a package or a stand-alone function.
<textarea rows="3" cols="25">
CREATE OR REPLACE FUNCTION auth_emps (
   schema_var   IN   VARCHAR2,                                      --required
   table_var    IN   VARCHAR2                                       --required
)
   RETURN VARCHAR2
IS
   return_val   VARCHAR2 (400);
BEGIN
   return_val :=
      CASE USER
         WHEN 'USER10'
            THEN 'DEPTNO = 10'
         WHEN 'USER20'
            THEN 'DEPTNO = 20'
         WHEN 'USER30'
            THEN 'DEPTNO = 30'
         ELSE NULL
      END;
   RETURN return_val;
END auth_emps;
</textarea>

Step 3 : Create the policy
We can create policy using Toad

You can decide which columns can be fetched at  result set using Security Relevant Columns tab

or you can do that step using the below code
BEGIN
  SYS.DBMS_RLS.ADD_GROUPED_POLICY
    (
      Object_schema         => 'SCOTT'
     ,Object_name           => 'EMP'
     ,policy_group           => 'EMP_POLICIES'
     ,policy_name           => 'SCOTT_EMPS'
     ,function_schema       => 'SCOTT'
     ,policy_function       => 'AUTH_EMPS'
     ,statement_types       => 'SELECT '
     ,policy_type           => dbms_rls.dynamic
     ,long_predicate        => FALSE
     ,sec_relevant_cols     => 'EMPNO,ENAME,JOB,MGR,HIREDATE,SAL,COMM,DEPTNO'
     ,sec_relevant_cols_opt => NULL
     ,update_check          => FALSE
     ,enable                => TRUE
    );
END;

Step 4 : Test
Let's now connect using user10 the result set will be only employees that in Department 10 like the below

Let's now connect using user20 the result set will be only employees that in Department 20 like the below

 Let's now connect using user30 the result set will be only employees that in Department 30 like the below

At policy we didn't specify predicate for scott so if we log in by scott and query we will retrieve all employees like the following

At that post I used Toad many times to make process easy and if you aren't interested with Toad, You can do everything at VPD using sys.DBMS_RLS package.
I planned that article to be shortly, so for further details you can read that chapter.

I hope that post is helpful and useful
Mahmoud Ahmed El-Sayed