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;
/
Geodata Master 