Oracle Virtual Private Database: Row-Level Security You Can't Route Around

You wrote a view that filters each sales rep to their own region, granted the view, revoked the table, and called it row-level security. It worked — until the reporting team needed the base table, or a data export ran as a privileged account, or someone wrote an ad-hoc query straight against ORDERS. The moment anyone touches the table instead of the view, the filter is gone. A view is a detour, and a detour only works if everyone agrees to take it.

Virtual Private Database (VPD, also called Fine-Grained Access Control, implemented through the DBMS_RLS package) does it the other way around: it attaches the rule to the table. You write a policy function that returns a WHERE predicate, and Oracle transparently appends that predicate to every query against the table, per session — no view, no application change, and nothing to route around. Query the base table directly and the predicate is still there. This is the row-level counterpart to Data Redaction: redaction masks a column’s value and leaves the row; VPD removes the row entirely.

This post is what VPD actually is, how the policy function and the exempt privilege work, where it applies (and where people forget it doesn’t), and — the part that ends the argument — a lab where three users query the same table and each one provably sees only the rows they’re allowed to.

What VPD is — a predicate the database adds for you

A VPD policy has two pieces: a policy function and the DBMS_RLS.ADD_POLICY call that binds it to a table. The function takes the schema and object name and returns a string — a SQL predicate. When a session queries the protected table, Oracle calls the function, takes whatever predicate it returns, and rewrites the query to include it. A SELECT * FROM orders issued by a sales rep silently becomes SELECT * FROM orders WHERE (region = 'EAST').

The crucial word is transparently. The application doesn’t know, the SQL text doesn’t change, and the predicate is enforced on the base table — so it applies to SELECT COUNT(*), to SUM(amount), to a join, to a WHERE id = 42 lookup, to an export, to everything. There is no version of the query that sees the unfiltered table (except for the exempt sessions below). That’s the whole difference from a view: a view is a separate object you can choose not to use; a VPD predicate is part of every statement against the table itself.

Here’s the function — it keys off the session user and returns each rep’s region:

CREATE OR REPLACE FUNCTION vpdown.orders_rls(p_schema IN VARCHAR2, p_object IN VARCHAR2)
  RETURN VARCHAR2 IS
BEGIN
  RETURN CASE SYS_CONTEXT('USERENV','SESSION_USER')
           WHEN 'REP_EAST' THEN 'region = ''EAST'''
           WHEN 'REP_WEST' THEN 'region = ''WEST'''
           ELSE '1=2'                       -- deny by default: unknown users see no rows
         END;
END;
/

And the policy that binds it to the table, for SELECT:

BEGIN
  DBMS_RLS.ADD_POLICY(
    object_schema   => 'VPDOWN',
    object_name     => 'ORDERS',
    policy_name     => 'ORDERS_REGION_POLICY',
    function_schema => 'VPDOWN',
    policy_function => 'ORDERS_RLS',
    statement_types => 'SELECT');
END;
/

That’s it. No grants to juggle, no view to maintain, no trigger. From now on every SELECT against VPDOWN.ORDERS carries the predicate the function hands back for that session.

Key the predicate off something the user can’t forge

The policy function above uses SYS_CONTEXT('USERENV','SESSION_USER') — the database account the session logged in as, which a user cannot spoof. That’s the safe kind of input. The same rule applies here as in the redaction post: base the decision on a session fact the user can’t set themselves.

In the real world, app servers often connect through one pooled database account and identify the end user separately. There the standard pattern is an application context: a logon trigger (or the connection pool) sets a context attribute like app_ctx.region for the session, and the policy function reads SYS_CONTEXT('APP_CTX','REGION'). Done right, the context is set by a trusted package the user can’t call directly, so it’s just as unforgeable as SESSION_USER. Done wrong — keying off a client identifier the application sets for convenience — it’s a lock anyone can open by setting the same value. Same trap, same fix.

Who bypasses the policy

Two kinds of session are not subject to VPD:

• SYS (and SYSDBA connections) are always exempt — they see the raw table.
• Any user holding the EXEMPT ACCESS POLICY system privilege bypasses every VPD policy in the database, regardless of any function. This is how you build a “sees everything” role — a manager, a reporting account — and it is the exact analog of EXEMPT REDACTION POLICY. It is also a master key: grant it to as few accounts as possible and audit who holds it, because one such account defeats every row policy you’ve written.

-- MGR sees every row, in every region, on every VPD-protected table:
GRANT EXEMPT ACCESS POLICY TO mgr;

flowchart TD
Q["Query from REP_EAST<br/>SELECT ... FROM orders"] --> P{"VPD policy on ORDERS:<br/>is the session EXEMPT?"}
P -->|"no — subject (REP_EAST)"| F["Policy function runs<br/>returns  region = 'EAST'"]
F --> A["Oracle appends the predicate<br/>...WHERE (region = 'EAST')"]
A --> R["Base table scanned WITH the predicate<br/>→ only EAST rows return"]
P -->|"yes — EXEMPT ACCESS POLICY / SYS"| C["No predicate added<br/>→ all rows return"]

Now prove it

Put a policy like the one above on an ORDERS table of known shape — 3,000 rows, deterministically split 1,800 EAST / 1,200 WEST — then read it back as three users: REP_EAST, REP_WEST, and a MGR who holds EXEMPT ACCESS POLICY:

-- each of these is the SAME statement; only the connected user differs
SELECT COUNT(*), SUM(amount) FROM vpdown.orders;

Read that table slowly, because every column is a separate proof. The row counts show each rep is confined to their region. The SUM(amount) shows the predicate is applied to aggregates too — REP_EAST can’t even learn the company-wide total, only their own 180,000. And the WHERE id = 3 probe is the one that kills the “I’ll just ask for the row directly” idea: id 3 is a WEST order, and when REP_EAST asks for it by primary key, Oracle appends AND region = 'EAST' and returns nothing. There is no query shape that widens the result. MGR, holding the exempt privilege, sees all 3,000 — the intended bypass, working as designed.

Don’t take my word for it — run it. The VPD lab stands up an Oracle Database Free container, builds ORDERS (3,000 rows, 1,800 EAST / 1,200 WEST) behind a DBMS_RLS policy, and reads it back as REP_EAST, REP_WEST, and an EXEMPT MGR. It asserts each rep sees only its region on the row count, on SUM(amount), and on a targeted by-id lookup, and that the exempt manager sees all 3,000. If any reader ever sees a row it shouldn’t — or the aggregates aren’t filtered — the run fails. It’s proven on every CI push.

Don’t forget the other three verbs

The lab policy covers SELECT, which is where most people start. But row-level security that only filters reads has a hole: what stops REP_EAST from inserting a WEST order, or updating an EAST order to move it to WEST? By default, nothing. VPD policies apply to the statement types you name, and for writes you almost always want the full set:

DBMS_RLS.ADD_POLICY(
  object_schema   => 'VPDOWN', object_name => 'ORDERS',
  policy_name     => 'ORDERS_REGION_POLICY',
  function_schema => 'VPDOWN', policy_function => 'ORDERS_RLS',
  statement_types => 'SELECT,INSERT,UPDATE,DELETE',
  update_check    => TRUE);           -- reject writes that would land a row outside the session's slice

update_check => TRUE is the important flag: without it, the predicate filters which rows an UPDATE or DELETE can see, but an INSERT (or an UPDATE that changes region) could still write a row the session wouldn’t be allowed to read back. With it on, Oracle re-checks the new row against the predicate and raises an error if it falls outside — so a rep can’t stash a row in another region.

What teams get wrong

• Using a view (or the application) as the security boundary. A view filters only if everyone goes through it; grant the base table for one report and it’s over. Application-tier filtering is worse — every new client, script, or BI tool is a fresh way around it. VPD moves the rule into the table so there’s one place, enforced for every path.
• Filtering reads but not writes. A SELECT-only policy lets a user insert or move rows outside their slice. Add INSERT,UPDATE,DELETE with update_check => TRUE when the rows are meant to stay put.
• Keying the predicate off something spoofable. A CLIENT_IDENTIFIER or context value the application sets for convenience is a lock the user can pick by setting the same value. Key off SESSION_USER, an enabled role, or an application context populated by a trusted package the user can’t call.
• Forgetting the exempt paths. EXEMPT ACCESS POLICY holders and SYS see everything; a full Data Pump export run by such an account carries every row; the object owner’s own access can bypass the policy. Audit who is exempt and treat privileged exports as sensitive — the same discipline as EXEMPT REDACTION POLICY.
• A heavy or non-deterministic policy function. The function runs as part of query parsing/execution, so a slow lookup or a function that returns different predicates unpredictably hurts performance and plan-sharing. Keep it cheap, and use the right policy type (STATIC, SHARED_STATIC, CONTEXT_SENSITIVE) so Oracle caches the predicate instead of re-running the function on every call.
• Expecting VPD to hide a column. VPD filters rows. If you need to blank out a column’s value for some users, that’s Data Redaction (or VPD’s column-sensitive mode with sec_relevant_cols, which applies the row predicate only when a sensitive column is selected). Match the tool to whether you’re hiding a row or a value.
• Reaching for VPD when you need labels or a full model. For classification-driven access (secret/confidential/public) or complex, centrally-managed policies across many tables, Oracle Label Security and Real Application Security exist and are built for that. VPD is the lightweight, write-your-own-predicate option — perfect for “each tenant sees their own rows,” heavier machinery for more.

Row-level security is one layer of the same data-protection discipline as the rest: Data Redaction masks the values within the rows a user can see, TDE makes the files unreadable when they’re stolen, unified auditing records who looked, and the hardening checklist and least-privilege grants decide who can connect at all. VPD decides which rows each session is even allowed to see — and, unlike a view, it decides it on the base table so there’s no way around it. Put the rule where the data lives, key it off something the user can’t forge, remember the write path, and then prove it the way that ends the argument: with the VPD lab, where three users query one table and each sees only what they should.