VPD stands for Virtual private database. These are used to restrict the user access to a particular section of the actual database. Many VPD contexts and profiles can be created based on the business requirement.
Lets look at a scenario.
ABC Corporation purchases items from suppliers all over the world. They use OTM to plan their shipments, etc., Since their suppliers are all over the world, based on the priority of the item, they choose the transport mode as Air, Sea, Truck, Rail.
ABC Corporation has 4 teams D1, D2, D3, D4 who take care of orders in Mode Air, Sea, Truck & Rail respectively. Now the requirement is to create/setup VPDs in such a way that employees of D1 should be able to view orders which use 'Air' as transport mode. Similarly for D2, D3 & D4.
If we observe the scenario, the access of a user is purely based on the transport mode. And actual value of transport mode will differ from user to user.
Using the above scenario, lets Create VPD Context:
- Navigate to Configuration and Administration -> Power Data -> General -> VPD Contexts
- Click on 'New'
- Enter a VPD Context ID , say 'V_D1'
- Variable Name and Value are free text fields. So we can choose and name them the way want. Enter 'PO_MODE' & 'AIR' , click on save button. [We can many Variables based on the requirement. In this scenario, 'PO_MODE' is enough]
- Click on Finished to Save the VPD Context 'V_D1'
Repeat the same steps to create VPD's like below
VPD Context ID
|
Variable Name
|
Variable Value
|
V_D1
|
PO_MODE
|
AIR
|
V_D2
|
PO_MODE
|
SEA
|
V_D3
|
PO_MODE
|
TRUCK
|
V_D4
|
PO_MODE
|
RAIL
|
A VPD profile is a set of Virtual Private Database (VPD) rules. Virtual private databases provide fine-tuned access control to users, across or within domains.
VPD works by adding a WHERE clause to every table in a SQL statement. VPD does not provide functional security; rather, it controls user access to data.
We are going to define the additional condition for a table and pick the condition values from VPD Contexts. In this scenario, for OB_ORDER_BASE Table , we are going to add a condition to check if transport mode is equal to the value of variable 'PO_MODE' of that particular user.
Below image shows how the control flow is. User queries for PO's, VPD Profile takes the relevant values from user's VPD Context and add it to the OTM queries as a conditions and brings out the appropriate data.
VPD Profile can also be compared as a code which defines how to retrieve the data. (In our scenario, its 'based on mode'). When user takes action, VPD context passes the value 'Air' etc., to VPD profile and it queries the database and gets data.
Creating VPD Profile
- Navigate to Configuration and Administration -> VPD Management -> VPD Profile
- Click on the 'New' Button
- Enter a VPD Profile ID , say VPD_ABC_CORP
- Select the check box 'Use External Predicate Rule'
- Select a Table Name for which to apply a predicate. [OB_ORDER_BASE]
- Enter the predicate exists(OB_ORDER_BASE.TRANSPORT_MODE_GID = SYS_CONTEXT('gl_user_ctx','PO_MODE')
- Select the predicate access 'Read'
- Save & Finish
SYS_CONTEXT('gl_user_ctx',VPD Variable) is used to retrieve the VPD Value from VPD Context.
With out these VPDs , let assume the query is
Select * from OB_ORDER_BASE;
Since we have added this predicate with a VPD, the query will become
(when User from Dept1 (air) queries)
select * from OB_ORDER_BASE
Where Exists (OB_ORDER_BASE.TRANSPORT_MODE_GID='AIR');
Later we can create User Roles linking VPD's
User Role
|
VPD Profile
|
VPD Context
|
V_D1
|
VPD_ABC_CORP
|
V_D1
|
V_D2
|
VPD_ABC_CORP
|
V_D2
|
V_D3
|
VPD_ABC_CORP
|
V_D3
|
V_D4
|
VPD_ABC_CORP
|
V_D4
|
Database Tables
VPD Contexts are stored in VPD_CONTEXT table.
VPD Context variables and their values are stored in VPD_CONTEXT_VARIABLE
VPD Profile details are stored in VPD_PROFILE table
