Oracle Database Technical Articles & GeoSpatial Data Technology

Oracle Virtual Private Database

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 !!!

Then as a sys user in the pluggable database PDB_ORIGIN configure the policy:SQL> beginsys.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.