Virtual Private Database, Fine-Grained Access Control, Redaction Concepts, and Tenant Isolation

Learning outcomes

ServiceHub stores North and South regional work orders in one table. The application adds WHERE region_code=:region everywhere, but one newly written report forgets the predicate and exposes every tenant. Virtual Private Database (VPD), also called fine-grained access control, injects a security predicate inside Oracle whenever protected SQL references the object. An application filter is optional code; a correctly designed VPD policy is database-enforced.

1. Application context is trusted session security state

An application context is a namespace of session attributes retrieved with SYS_CONTEXT. A secure context names a trusted package allowed to call DBMS_SESSION.SET_CONTEXT. The client should not be able to submit arbitrary tenant identity and directly set it; the trusted package derives/validates it from authenticated identity or an authoritative mapping.

2. Build two proxyable tenant users and protected data

ALTER SESSION SET CONTAINER=FREEPDB1;BEGIN EXECUTE IMMEDIATE 'DROP USER sh20_gateway CASCADE';EXCEPTION WHEN OTHERS THEN IF SQLCODE != -1918 THEN RAISE; END IF; END;/BEGIN EXECUTE IMMEDIATE 'DROP USER sh20_north CASCADE';EXCEPTION WHEN OTHERS THEN IF SQLCODE != -1918 THEN RAISE; END IF; END;/BEGIN EXECUTE IMMEDIATE 'DROP USER sh20_south CASCADE';EXCEPTION WHEN OTHERS THEN IF SQLCODE != -1918 THEN RAISE; END IF; END;/CREATE USER sh20_gateway NO AUTHENTICATION;CREATE USER sh20_north NO AUTHENTICATION;CREATE USER sh20_south NO AUTHENTICATION;GRANT CREATE SESSION TO sh20_gateway;ALTER USER sh20_north GRANT CONNECT THROUGH sh20_gateway;ALTER USER sh20_south GRANT CONNECT THROUGH sh20_gateway;PASSWORD sh20_gateway-- Enter only the gateway password interactively.

BEGIN  EXECUTE IMMEDIATE 'DROP TABLE sh20_tenant_orders PURGE';EXCEPTION WHEN OTHERS THEN  IF SQLCODE != -942 THEN RAISE; END IF;END;/CREATE TABLE sh20_tenant_orders (  order_id        NUMBER PRIMARY KEY,  region_code     VARCHAR2(8) NOT NULL,  customer_email  VARCHAR2(120) NOT NULL,  status_code     VARCHAR2(12) NOT NULL);INSERT INTO sh20_tenant_orders VALUES (1,'AZ-N','north@example.invalid','OPEN');INSERT INTO sh20_tenant_orders VALUES (2,'AZ-S','south@example.invalid','OPEN');COMMIT;GRANT SELECT,UPDATE ON sh20_tenant_orders TO sh20_north,sh20_south;

3. Deliberately wrong: object grant plus application WHERE clause

CONNECT sh20_gateway[sh20_north]@//localhost:1521/FREEPDB1-- Enter gateway password.SELECT order_id,region_code,customer_emailFROM servicehub_owner.sh20_tenant_ordersORDER BY order_id;-- Security gap: both AZ-N and AZ-S rows are visible because-- the database object grant itself has no tenant predicate.

The SQL is correct from Oracle's authorization perspective: SH20_NORTH has SELECT on the table. Application code review can reduce this risk but cannot make every future SQL path impossible.

4. Trusted package derives the region from authenticated target identity

CREATE OR REPLACE PACKAGE sh20_ctx_pkg AUTHID DEFINER AS  PROCEDURE set_region;END;/CREATE OR REPLACE PACKAGE BODY sh20_ctx_pkg AS  PROCEDURE set_region IS    l_region VARCHAR2(8);  BEGIN    l_region :=      CASE SYS_CONTEXT('USERENV','SESSION_USER')        WHEN 'SH20_NORTH' THEN 'AZ-N'        WHEN 'SH20_SOUTH' THEN 'AZ-S'        ELSE NULL      END;    DBMS_SESSION.SET_CONTEXT(      namespace => 'SH20_CTX',      attribute => 'REGION_CODE',      value     => l_region    );  END;END;/GRANT EXECUTE ON sh20_ctx_pkg TO sh20_north,sh20_south;

CREATE CONTEXT sh20_ctx  USING servicehub_owner.sh20_ctx_pkg;

Only the trusted package can set SH20_CTX. The mapping intentionally ignores a client-supplied region parameter.

5. Policy function returns SQL predicate text

CREATE OR REPLACE FUNCTION sh20_region_predicate(  p_schema VARCHAR2,  p_object VARCHAR2)RETURN VARCHAR2AUTHID DEFINERASBEGIN  IF SYS_CONTEXT('SH20_CTX','REGION_CODE') IS NULL THEN    RETURN '1=0';  END IF;  RETURN    'region_code = SYS_CONTEXT(''SH20_CTX'',''REGION_CODE'')';END;/

The function does not query the protected table, avoids user-input string concatenation and returns deny-all when trusted context is absent. Querying the protected table from its own policy function can recurse or fail; heavy SQL/network work inside a policy function can also amplify parse/execution cost.

6. Attach the VPD policy

BEGIN  DBMS_RLS.ADD_POLICY(    object_schema   => 'SERVICEHUB_OWNER',    object_name     => 'SH20_TENANT_ORDERS',    policy_name     => 'SH20_REGION_VPD',    function_schema => 'SERVICEHUB_OWNER',    policy_function => 'SH20_REGION_PREDICATE',    statement_types => 'SELECT,UPDATE,DELETE',    update_check    => TRUE,    enable          => TRUE,    policy_type     => DBMS_RLS.CONTEXT_SENSITIVE,    namespace       => 'SH20_CTX',    attribute       => 'REGION_CODE'  );END;/

CONTEXT_SENSITIVE lets Oracle re-evaluate when the named session context changes. For connection pools, the middle tier must set/reset trusted context for every borrowed identity; leaked context across clients is a tenant-isolation failure.

7. Verify North and South see different row sets

EXEC servicehub_owner.sh20_ctx_pkg.set_region;SELECT SYS_CONTEXT('SH20_CTX','REGION_CODE') AS regionFROM dual;SELECT order_id,region_code,customer_emailFROM servicehub_owner.sh20_tenant_ordersORDER BY order_id;-- Expected: only AZ-N.

CONNECT sh20_gateway[sh20_south]@//localhost:1521/FREEPDB1EXEC servicehub_owner.sh20_ctx_pkg.set_region;SELECT order_id,region_code,customer_emailFROM servicehub_owner.sh20_tenant_ordersORDER BY order_id;-- Expected: only AZ-S.

VPD modifies the effective SQL predicate. It does not physically separate tenants into different tablespaces/PDBs and does not create separate encryption keys or backup failure domains.

8. Privileged bypass is intentional—and dangerous

Users with EXEMPT ACCESS POLICY are not constrained by VPD. This is an administrative/bypass capability that should never be granted to an application runtime role. Database Vault or separation-of-duty architecture can further constrain powerful administrators where required.

SELECT grantee,privilegeFROM dba_sys_privsWHERE privilege IN (  'EXEMPT ACCESS POLICY',  'EXEMPT REDACTION POLICY')ORDER BY grantee,privilege;

9. Data Redaction masks values; it does not remove rows

Data Redaction changes sensitive column values immediately before results are returned to a user/application. It is appropriate when a user may see the row but not the original value. VPD decides which rows participate; Redaction changes what selected column values look like. A user with EXEMPT_REDACTION_POLICY bypasses redaction.

BEGIN  DBMS_REDACT.ADD_POLICY(    object_schema => 'SERVICEHUB_OWNER',    object_name   => 'SH20_TENANT_ORDERS',    policy_name   => 'SH20_EMAIL_REDACT',    column_name   => 'CUSTOMER_EMAIL',    function_type => DBMS_REDACT.FULL,    expression    =>      'SYS_CONTEXT(''USERENV'',''SESSION_USER'') = ''SH20_SOUTH'''  );END;/

Current licensing includes Data Redaction in Free; on EE/EE-ES it requires Oracle Advanced Security. Redaction is not encryption: the stored value remains unchanged and privileged/exempt paths can access it.

10. Performance and plan evidence

VPD predicates participate in optimization. Poor predicates/functions can cause extra parses, nonselective access or unexpected index choices. Use normal runtime plan evidence after applying policy under representative tenant contexts.

SELECT /*+ gather_plan_statistics */       order_id,status_codeFROM servicehub_owner.sh20_tenant_ordersWHERE status_code='OPEN';SELECT *FROM TABLE(  DBMS_XPLAN.DISPLAY_CURSOR(    NULL,NULL,'ALLSTATS LAST +PREDICATE +NOTE'  ));

Predicate information should show both the user predicate and the security predicate or transformed equivalent. A fast two-row lab does not establish production policy-function overhead.

11. Cleanup

BEGIN  DBMS_REDACT.DROP_POLICY(    object_schema => 'SERVICEHUB_OWNER',    object_name   => 'SH20_TENANT_ORDERS',    policy_name   => 'SH20_EMAIL_REDACT'  );EXCEPTION WHEN OTHERS THEN NULL;END;/BEGIN  DBMS_RLS.DROP_POLICY(    object_schema => 'SERVICEHUB_OWNER',    object_name   => 'SH20_TENANT_ORDERS',    policy_name   => 'SH20_REGION_VPD'  );END;/DROP CONTEXT sh20_ctx;DROP TABLE servicehub_owner.sh20_tenant_orders PURGE;DROP USER sh20_north CASCADE;DROP USER sh20_south CASCADE;DROP USER sh20_gateway CASCADE;

12. Production judgment

Use VPD when row-level policy must follow data regardless of which SQL path reaches the object. Keep policy functions deterministic with respect to trusted context, cheap, recursion-free and fail-closed. Reset context in pooled sessions and tightly control bypass privileges. Use Data Redaction when the row is allowed but a returned sensitive value must be masked.

The current 26ai licensing matrix lists VPD and Data Redaction as available in Free; Data Redaction requires Advanced Security on EE/EE-ES. No restart or COMPATIBLE change is required for this lab. Lesson 5 moves below SQL authorization to encryption boundaries and the key material that must survive disaster recovery.

Authoritative references

• DBMS_RLS — VPD predicate injection/policy types
• Using Virtual Private Database — application context/VPD design
• Data Redaction Guide — redaction policy/bypass concepts
• DBMS_REDACT — redaction API
• Licensing Information — VPD/Redaction/Advanced Security availability