Oracle VPD in Practice: Read-Only Support Access Without Exposing Restricted Customers

Tested on: Oracle Database 19c Enterprise Edition 19.27 | Level: Intermediate

A production support team needs to query live data to troubleshoot incidents. In a bank, that same data includes customers the support team must never see, such as staff accounts or accounts under legal hold, plus sensitive columns like national ID and phone number. In this post I use Oracle Virtual Private Database (VPD) to give support engineers real read-only access while Oracle silently filters restricted rows and masks sensitive columns, with no change to the application.

The problem

Creating a filtered view for every table does not scale, and application teams will not rewrite queries for one support user. Granting plain SELECT has a second risk: a support user can run SELECT ... FOR UPDATE and lock production rows during an incident.

VPD solves the access part inside the database, and the READ privilege solves the locking part. Everything below was run end to end on a 19c lab database, and every output shown is real.

How VPD works

VPD attaches a policy to a table. When a user queries that table, Oracle calls a PL/SQL policy function, takes the string it returns (for example is_restricted = 'N') and appends it to the statement's WHERE clause before execution. The user never sees or controls this predicate.

VPD is part of Oracle Database Enterprise Edition and is managed with the DBMS_RLS package. The lab builds four things:

1. A lab schema VPD_DEMO with a CUSTOMERS table
2. A read-only SUPPORT_RO user that cannot modify or lock rows
3. A row-level policy that hides restricted customers from SUPPORT_RO only
4. A column-masking policy that returns NULL for national ID and phone for SUPPORT_RO

Step 1: Create the lab schema and data

Connect as SYSDBA. The block below creates the schema in the database's default tablespace, so it works on any 19c install.

DECLARE
  l_ts VARCHAR2(128);
BEGIN
  SELECT property_value INTO l_ts
  FROM   database_properties
  WHERE  property_name = 'DEFAULT_PERMANENT_TABLESPACE';
  EXECUTE IMMEDIATE 'CREATE USER vpd_demo IDENTIFIED BY Welcome_2026 '
                 || 'DEFAULT TABLESPACE ' || l_ts
                 || ' QUOTA UNLIMITED ON ' || l_ts;
END;
/
GRANT CREATE SESSION, CREATE TABLE, CREATE PROCEDURE TO vpd_demo;
GRANT EXECUTE ON sys.dbms_rls TO vpd_demo;

CREATE TABLE vpd_demo.customers (
  customer_id    NUMBER        PRIMARY KEY,
  full_name      VARCHAR2(100) NOT NULL,
  branch_code    VARCHAR2(10)  NOT NULL,
  national_id    VARCHAR2(20),
  phone          VARCHAR2(20),
  is_restricted  CHAR(1)       DEFAULT 'N' NOT NULL
                 CHECK (is_restricted IN ('Y','N'))
);

INSERT INTO vpd_demo.customers VALUES (1, 'Arun Kumar',     'CHN01', 'ID-1001', '+91-90000-00001', 'N');
INSERT INTO vpd_demo.customers VALUES (2, 'Meena Raj',      'CHN01', 'ID-1002', '+91-90000-00002', 'N');
INSERT INTO vpd_demo.customers VALUES (3, 'Staff Account',  'CHN02', 'ID-1003', '+91-90000-00003', 'Y');
INSERT INTO vpd_demo.customers VALUES (4, 'Priya Shankar',  'BLR01', 'ID-1004', '+91-90000-00004', 'N');
INSERT INTO vpd_demo.customers VALUES (5, 'Legal Hold Ltd', 'BLR01', 'ID-1005', '+91-90000-00005', 'Y');
INSERT INTO vpd_demo.customers VALUES (6, 'Vijay Anand',    'MUM01', 'ID-1006', '+91-90000-00006', 'N');
COMMIT;

Six customers, two of them restricted. All names and IDs are fictional.

Step 2: Create a truly read-only support user

Grant READ, not SELECT. Since Oracle 12c, READ allows queries but blocks SELECT ... FOR UPDATE, so support can never lock application rows.

CREATE USER support_ro IDENTIFIED BY Welcome_2026;
GRANT CREATE SESSION TO support_ro;
GRANT READ ON vpd_demo.customers TO support_ro;

Watch out: never grant EXEMPT ACCESS POLICY, the DBA role or any ANY system privilege to this user. Any of these can bypass or undermine the policies below.

Step 3: Hide restricted rows with a row-level policy

Connect as VPD_DEMO. The policy function returns a predicate only for SUPPORT_RO. Returning NULL means no filter, so the application and every other user are unaffected.

CREATE OR REPLACE FUNCTION support_rows_pred (
  p_schema IN VARCHAR2,
  p_object IN VARCHAR2
) RETURN VARCHAR2
AS
BEGIN
  IF SYS_CONTEXT('USERENV', 'SESSION_USER') = 'SUPPORT_RO' THEN
    RETURN 'is_restricted = ''N''';
  END IF;
  RETURN NULL;
END;
/

BEGIN
  DBMS_RLS.ADD_POLICY(
    object_schema   => 'VPD_DEMO',
    object_name     => 'CUSTOMERS',
    policy_name     => 'SUPPORT_RO_ROWS',
    function_schema => 'VPD_DEMO',
    policy_function => 'SUPPORT_ROWS_PRED',
    statement_types => 'SELECT',
    policy_type     => DBMS_RLS.DYNAMIC
  );
END;
/

Why DYNAMIC and not STATIC: a static policy runs the function once and reuses the predicate for every session. Our predicate depends on who is connected, so caching it would apply one user's answer to everyone. Start with DYNAMIC for correctness, then evaluate CONTEXT_SENSITIVE if function calls show up as a cost.

Step 4: Mask sensitive columns

Support often needs to see that a customer exists, but not their national ID or phone. A column-masking policy keeps the rows and returns NULL in the listed columns wherever its predicate is false. Returning 1 = 0 for SUPPORT_RO masks those columns on every row.

CREATE OR REPLACE FUNCTION support_mask_pred (
  p_schema IN VARCHAR2,
  p_object IN VARCHAR2
) RETURN VARCHAR2
AS
BEGIN
  IF SYS_CONTEXT('USERENV', 'SESSION_USER') = 'SUPPORT_RO' THEN
    RETURN '1 = 0';
  END IF;
  RETURN NULL;
END;
/

BEGIN
  DBMS_RLS.ADD_POLICY(
    object_schema         => 'VPD_DEMO',
    object_name           => 'CUSTOMERS',
    policy_name           => 'SUPPORT_RO_MASK',
    function_schema       => 'VPD_DEMO',
    policy_function       => 'SUPPORT_MASK_PRED',
    statement_types       => 'SELECT',
    sec_relevant_cols     => 'NATIONAL_ID,PHONE',
    sec_relevant_cols_opt => DBMS_RLS.ALL_ROWS,
    policy_type           => DBMS_RLS.DYNAMIC
  );
END;
/

Without sec_relevant_cols_opt => DBMS_RLS.ALL_ROWS, Oracle would remove whole rows whenever a query touches those columns, which confuses support users far more than an empty value.

Step 5: Test and verify

The same query, run by two different users:

SELECT customer_id, full_name, national_id, phone, is_restricted
FROM   vpd_demo.customers
ORDER  BY customer_id;

As VPD_DEMO (the application owner): all 6 rows, all columns

CUSTOMER_ID FULL_NAME        NATIONAL_ID  PHONE            IS_RESTRICTED
----------- ---------------- ------------ ---------------- -------------
          1 Arun Kumar       ID-1001      +91-90000-00001  N
          2 Meena Raj        ID-1002      +91-90000-00002  N
          3 Staff Account    ID-1003      +91-90000-00003  Y
          4 Priya Shankar    ID-1004      +91-90000-00004  N
          5 Legal Hold Ltd   ID-1005      +91-90000-00005  Y
          6 Vijay Anand      ID-1006      +91-90000-00006  N

6 rows selected.

As SUPPORT_RO: restricted customers gone, sensitive columns empty

CUSTOMER_ID FULL_NAME        NATIONAL_ID  PHONE            IS_RESTRICTED
----------- ---------------- ------------ ---------------- -------------
          1 Arun Kumar                                     N
          2 Meena Raj                                      N
          4 Priya Shankar                                  N
          6 Vijay Anand                                    N

4 rows selected.

Next, confirm the support user cannot change or lock data:

UPDATE vpd_demo.customers SET phone = NULL WHERE customer_id = 1;
SELECT * FROM vpd_demo.customers WHERE customer_id = 1 FOR UPDATE;

ERROR at line 1:
ORA-01031: insufficient privileges

ERROR at line 1:
ORA-01031: insufficient privileges

The execution plan, generated as SUPPORT_RO, shows the predicate Oracle injected even though the user never wrote it:

EXPLAIN PLAN FOR
  SELECT customer_id, full_name FROM vpd_demo.customers;
SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY(format => 'BASIC +PREDICATE'));

---------------------------------------
| Id  | Operation         | Name      |
---------------------------------------
|   0 | SELECT STATEMENT  |           |
|*  1 |  TABLE ACCESS FULL| CUSTOMERS |
---------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - filter("IS_RESTRICTED"='N')

Finally, verify from the DBA side. DBA_POLICIES lists the policies, and V$VPD_POLICY shows which predicate was applied to each cursor:

SELECT policy_name, function, sel, enable, policy_type
FROM   dba_policies
WHERE  object_owner = 'VPD_DEMO' AND object_name = 'CUSTOMERS';

SELECT sql_id, policy, predicate
FROM   v$vpd_policy
WHERE  object_owner = 'VPD_DEMO' AND object_name = 'CUSTOMERS';

POLICY_NAME      FUNCTION             SEL ENABLE POLICY_TYP
---------------- -------------------- --- ------ ----------
SUPPORT_RO_ROWS  SUPPORT_ROWS_PRED    YES YES    DYNAMIC
SUPPORT_RO_MASK  SUPPORT_MASK_PRED    YES YES    DYNAMIC

SQL_ID        POLICY           PREDICATE
------------- ---------------- ------------------------------
13au474p4vjy3 SUPPORT_RO_ROWS
13au474p4vjy3 SUPPORT_RO_MASK
13au474p4vjy3 SUPPORT_RO_ROWS  is_restricted = 'N'
13au474p4vjy3 SUPPORT_RO_MASK  1 = 0

What this output tells you: the same SQL_ID appears twice for each policy. Once with an empty predicate, from the owner's run, and once with the injected predicate, from the support user's run. Oracle keeps separate child cursors for the same SQL text when the VPD predicate differs. This is how you prove, after the fact, exactly what filter a user's query ran with, and it is also why heavy VPD use can increase child cursors in the shared pool.

Production checklist

• Know who bypasses VPD. SYS and any user with EXEMPT ACCESS POLICY see everything. Audit it regularly: SELECT grantee FROM dba_sys_privs WHERE privilege = 'EXEMPT ACCESS POLICY';
• Separate the policy owner. In this lab the table owner holds the policy functions to keep things simple. In production, put them in a dedicated security schema so the application owner cannot change the rules that protect its own data.
• Keep policy functions small and safe. If the function fails, every query on the table fails with errors such as ORA-28110, ORA-28112 or ORA-28113. Never query the protected table inside its own policy function.
• Check plans, not just results. The predicate becomes part of every support query. Index the filter column on large tables if it is selective, and confirm with DBMS_XPLAN.
• Identify the user correctly. SESSION_USER works when each support engineer or team has its own account. With a shared account or a connection pool, use an application context set at login instead.
• Views are covered. A policy on the base table also applies when the table is read through a view.
• Treat policies as code. Keep functions and ADD_POLICY calls in version control and deploy them through normal change control.

Rollback and clean-up

-- Temporarily disable a policy without losing its definition
BEGIN
  DBMS_RLS.ENABLE_POLICY('VPD_DEMO', 'CUSTOMERS', 'SUPPORT_RO_ROWS', FALSE);
END;
/

-- Remove both policies
BEGIN
  DBMS_RLS.DROP_POLICY('VPD_DEMO', 'CUSTOMERS', 'SUPPORT_RO_ROWS');
  DBMS_RLS.DROP_POLICY('VPD_DEMO', 'CUSTOMERS', 'SUPPORT_RO_MASK');
END;
/

-- Remove the lab completely (as SYSDBA)
DROP USER support_ro CASCADE;
DROP USER vpd_demo CASCADE;

Summary

With two short functions and two DBMS_RLS.ADD_POLICY calls, the support team gets real read-only access for troubleshooting, restricted customers stay invisible, sensitive columns stay masked and no one can lock production rows. The application needed no changes, and the rules live in the database where every access path is covered.

In a follow-up post I will extend this with an application context, so access rules come from a mapping table instead of hard-coded user names.

Tamilselvan Perumal is an Oracle ACE Apprentice and Senior DBA with 12+ years in banking databases. Connect on LinkedIn.