My Oracle Support Banner

How To Use Database Policies / Virtual Private Database (VPD) To Restrict Data In Application and SQL (Subinventory Example)? (Doc ID 2542049.1)

Last updated on JULY 18, 2021

Applies to:

Oracle Item Master - Version 12.2.6 and later
Oracle Inventory Management - Version 12.2.6 and later
Oracle Purchasing - Version 12.2.6 and later
Information in this document applies to any platform.
VPD: Virtual Private Database (VPD) -- Database Policy/Policies


How To Use Database Policies / Virtual Private Database (VPD) To Restrict Data In Application and SQL?


As an example, in a Vision environment, the organization Seattle Manufacturing (M1 / id 207) has various subinventories like FGI, Stores, FldSvc, INTRANSIT, FloorStock, etc.

What if you wanted users only access to FGI and Stores? Or some dynamic listing of subinventories by user or responsibility?

Note: In the images below, user details / company name / address / email / telephone number represent a fictitious sample (based upon made up data used in the Oracle Demo Vision Instance). Any similarity to actual persons, living or dead, is purely coincidental and not intended in any manner.

Also note, a database policy would apply to all users in the environment using SQL, forms, reports, and custom programs.

Using database policies is not supported and this is provided as an example. If you hit performance issues or application problems, please disable any custom code to confirm the application still behaves normally. You are responsible for debugging your own custom code and correcting any issues that it causes.


To view full details, sign in with your My Oracle Support account.

Don't have a My Oracle Support account? Click to get started!

In this Document
 A. Check Policy And Data
 B. Create Simple Policy Example
 C. Test Policy In SQL and Form
 D. Disable or Drop Policy
 D.1. Disable Policy
 D.2. Drop Policy
 E. More Complicated Policy Example
 E.1. Variables
 E.2. Lookup Example
 F. Using Variables in SQL
 G. Debugging / Troubleshooting
 G.1. Troubleshooting Without Policy
 G.2. Debug Messages
 G.3. Tracing
 G.4. Policies in Standard Application
 H. Frequent Questions (FAQ)

My Oracle Support provides customers with access to over a million knowledge articles and a vibrant support community of peers and Oracle experts.