The idea of Virtual Private Database is to enable users having access to a table to see ONLY subset of
data within the table. This technique is based on a function that will enable SQL execution to append a WHRE clause predicate. The subset of data can be from row-level or column-level perspective.

For demonstration, I will be using a table called EMP under SCOTT schema.

I will also create 2 accounts:
SQL> create user manager identified by mono670;
SQL>grant create session to to manager;
SQL>grant select on SCOTT.EMP to manager;
SQL>create user clerk identified by clerk332;

SQL>grant create session to clerk;
SQL>grant select on SCOTT.EMP to clerk;
connecting as accounts MANAGER & CLERK I can see the full table data !!!

Now I will create security administrator account:
SQL> alter session set container=CDB$root;
SQL> create user security_admin identified by zorro3_g;
SQL> grant create session to security_admin;
SQL> grant create procedure to security_admin;
Now I am going to create the function:
SQL> CREATE OR REPLACE FUNCTION VPD_FUNC
(schema_v IN VARCHAR2, tbl_v IN VARCHAR2) RETURN VARCHAR2 IS
BEGIN
RETURN ‘upper(ename) = SYS_CONTEXT(”USERENV”, ”CURRENT_USER”) OR upper(job) =
SYS_CONTEXT(”USERENV”, ”CURRENT_USER”)’;
END;
/

 

Then as a sys user in the pluggable database PDB_ORIGIN configure the policy:
SQL> begin
sys.dbms_rls.add_policy(
object_schema=>’SCOTT’,
object_name=>’EMP’,
policy_name=>’VPD1_POLICY’,
function_schema=>’security_admin’,
policy_function=>’VPD_FUNC’,
statement_types => ‘SELECT’);
end;
/
Connecting as account “manager” I can see data related to “manager” records:

Same to “CLERK”

This is a very nice security feature that many organizations can use to restrict  access to data within the table itself.