Introduction:

A virtual private database (VPD) is a security feature that masks data so that only a subset of the data appears to exist, without actually segregating data into different tables, schemas or databases.

Whenever a SQL query is executed, the relevant predicates for the involved tables are transparently collected and query results are returned with “filtered” rows based on database account session context.

By Default “DBA”role is not granted EXEMPT ACCESS POLICY system privilege.

In 19c the following are granted EXEMPT ACCESS POLICY system privilege :

SQL> select * from dba_sys_privs where privilege like ‘%EXEMPT ACCESS POLICY%’;

In 23ai the following are granted EXEMPT ACCESS POLICY system privilege:

Steps for the VPD setup Simulation:

SQL> CREATE USER sh1 IDENTIFIED BY sh1

DEFAULT TABLESPACE users TEMPORARY TABLESPACE temp;

GRANT connect, resource TO sh1;

alter user sh1 quota unlimited on users;

SQL> CREATE USER tbrooke IDENTIFIED BY tbrooke

DEFAULT TABLESPACE users TEMPORARY TABLESPACE temp;

GRANT connect, resource TO tbrooke;

SQL> CREATE USER owoods IDENTIFIED BY owoods

DEFAULT TABLESPACE users TEMPORARY TABLESPACE temp;

GRANT connect, resource TO owoods;

SQL>  CREATE USER sysadmin_vpd IDENTIFIED BY vpd CONTAINER = CURRENT;

GRANT CREATE SESSION, CREATE ANY CONTEXT, CREATE PROCEDURE, CREATE TRIGGER, ADMINISTER DATABASE TRIGGER TO sysadmin_vpd;

GRANT EXECUTE ON DBMS_SESSION TO sysadmin_vpd;

GRANT EXECUTE ON DBMS_RLS TO sysadmin_vpd;

SQL>  CREATE TABLE sh1.customers (

 cust_no    NUMBER(4),

 cust_email VARCHAR2(20),

 cust_name  VARCHAR2(20));

SQL> INSERT INTO sh1.customers VALUES (1234, ‘TBROOKE’, ‘Thadeus Brooke’);

SQL> INSERT INTO sh1.customers VALUES (5678, ‘OWOODS’, ‘Oberon Woods’);

SQL> commit;

SQL> CREATE TABLE sh1.orders_tab (

  cust_no  NUMBER(4),

  order_no NUMBER(4));

SQL> INSERT INTO sh1.orders_tab VALUES (1234, 9876);

SQL> INSERT INTO sh1.orders_tab VALUES (5678, 5432);

SQL> commit;

SQL>  GRANT READ ON sh1.customers  TO tbrooke, owoods;

SQL>  GRANT READ ON sh1.orders_tab TO tbrooke, owoods;

SQL> GRANT READ ON sh1.customers TO sysadmin_vpd;

sqlplus sysadmin_vpd/vpd@ORCLPDB1

SQL>  CREATE OR REPLACE CONTEXT orders_ctx USING orders_ctx_pkg;

SQL>  CREATE OR REPLACE PACKAGE orders_ctx_pkg IS

  PROCEDURE set_custnum;

 END;

/

SQL>  CREATE OR REPLACE PACKAGE BODY orders_ctx_pkg IS

  PROCEDURE set_custnum

  AS

    custnum NUMBER;

  BEGIN

     SELECT cust_no INTO custnum FROM SH1.CUSTOMERS

        WHERE cust_email = SYS_CONTEXT(‘USERENV’, ‘SESSION_USER’);

     DBMS_SESSION.SET_CONTEXT(‘orders_ctx’, ‘cust_no’, custnum);

  EXCEPTION

   WHEN NO_DATA_FOUND THEN NULL;

  END set_custnum;

END;

/

SQL>  CREATE OR REPLACE TRIGGER set_custno_ctx_trig AFTER LOGON ON DATABASE

 BEGIN

sysadmin_vpd.orders_ctx_pkg.set_custnum;

 END;

/

sqlplus tbrooke/tbrooke@ORCLPDB1

SQL> SELECT SYS_CONTEXT(‘orders_ctx’, ‘cust_no’) custnum FROM DUAL;

CUSTNUM

——————————————————————————–

1234

sqlplus sysadmin_vpd/vpd@ORCLPDB1

SQL>  CREATE OR REPLACE FUNCTION get_user_orders(

  schema_p   IN VARCHAR2,

  table_p    IN VARCHAR2)

 RETURN VARCHAR2

 AS

  orders_pred VARCHAR2 (400);

 BEGIN

  orders_pred := ‘cust_no = SYS_CONTEXT(”orders_ctx”, ”cust_no”)’;

 RETURN orders_pred;

END;

/

// Important Note: in 23ai you can’t define DBMS_RLS policy using sysadmin_vpd account, you can do that in 23ai using SYS account only….if you try in 23ai execute the below package an error [ORA-01031: insufficient privileges] will be thrown:

SQL>  BEGIN

 DBMS_RLS.ADD_POLICY (

  object_schema    => ‘sh1’,

  object_name      => ‘orders_tab’,

  policy_name      => ‘orders_policy’,

  function_schema  => ‘sysadmin_vpd’,

  policy_function  => ‘get_user_orders’,

  statement_types  => ‘select’,

  policy_type      => DBMS_RLS.CONTEXT_SENSITIVE,

  namespace        => ‘orders_ctx’,

  attribute        => ‘cust_no’);

END;

/

SQL> SELECT * FROM sh1.orders_tab;

   CUST_NO   ORDER_NO

———- ———-

      1234       9876

sqlplus owoods/owoods@ORCLPDB1

SQL> SELECT * FROM sh1.orders_tab;

   CUST_NO   ORDER_NO

———- ———-

      5678       5432

To Disable VPD policy:

BEGIN

DBMS_RLS.ENABLE_POLICY(‘SH1’, ‘ORDERS_TAB’, ‘ORDERS_POLICY’,FALSE);

END;

/