VPD (Virtual Private Database) controls data-level visibility. It operates at the database row level, silently filtering query results so that a user only ever sees records they are permitted to access — for example, a planner restricted to their own region’s shipments, or an approver who can only see invoices within their approval threshold. VPD rules are invisible to the user: the application does not show a filtered view, it simply never returns rows they are not permitted to see.
Access Control List (ACL) controls function-level access. Even if a user can see a record, ACLs determine which actions they can take — whether they can initiate a Bulk Plan, approve a tender, run a recurring process, or access the SQL servlet. ACLs work at the UI entry-point level, granting or restricting individual buttons, menus, and screens.
VPD answers what data can this user see? ACL answers what can this user do? Both are attached to the User Role and must be planned together — a gap in either can expose data or functionality that should be restricted.
OTM application users are grouped and classified based on the daily functions they perform in their organization. Each such group of users is assigned a “Role” in OTM.
In summary, a “Role” controls:
- Data visibility (using VPD setups)
- UI or Feature Access (using ACLs)
For example, the ADMIN role might have complete data visibility and access to all OTM data and UI functions, whereas the PLANNER role might have access only to a particular domain and particular Order Type PO transactions. The PLANNER may also need access to only a few OTM functions like Order review, Bulk Plan, and Tendering.
Before defining roles for a domain, identify the following:
- Complete list of users who need access to the application for the domain.
- Functions each user in that domain performs using the application.
- Whether users require access to roles other than the Default role granted to them at user definition level.
Define a new role:
Important values to enter:
VPD Profile:
Important values while defining a VPD Profile:
- Check the options ‘Use External Predicate Rule’ and ‘User Domain Role’ (to restrict users from accessing data in other domains than where their user record is created).
- The External Predicate Rule option allows you to define conditions at table level to restrict data access. For example, to allow users to view only location data they created:
- Table Name: LOCATION
- Predicate: location.insert_user = SYS_CONTEXT('gl_user_ctx','gl_user_gid')
- External Predicate Access: Read
It is always better to keep VPD logic as simple as possible by populating required filtering data elements on the base objects (attribute columns, etc.) and defining rules on those values. For example, to ensure a user has access to specific types of POs, use custom logic in agents to populate an attribute value on those POs and then define the predicate as:
Tables related to VPD profile:
select * from vpd_profile where vpd_profile_gid = 'DOMAIN.VPD_PROFILE_ID'
select * from external_predicate where vpd_profile_gid = 'DOMAIN.VPD_PROFILE_ID'Grantee User Role: Add all role names in this list that require access to the current role being defined. For example, if you have a role like LOGISTICS_SUPERUSER and those users need to switch to the current role, add ‘LOGISTICS_SUPERUSER’ in this list. It is better to add the Domain ADMIN role to all new roles you define for that domain. Adding this access should be done from a DBA.ADMIN login if the current user has restricted privileges at the current domain level.
Access Control List: This is used to restrict certain screens (certain UI-based functionality) like bulk plans and tender actions. OTM has ‘Access Control Entry Points’ for each UI function, and has also grouped most commonly used entry points into default Access Control Lists that can be readily used — for example, ‘Bulk Plan - View’.
As per Oracle documentation, every custom ACL defined with the ‘Granted’ option checked should include the ‘COMMON’ ACL provided by Oracle. This COMMON list covers standard OTM functionality like user logins.
Example — ACL for order-only access:
Child Access Control List values: Allocation - View, COMMON, Customer-Actions, Customer-Update, Customer-View, Logic Config-View, Material-View, Order-Actions, Order Update, Order-View, Parameter Set-View, Remark Qualifier - Update, Remark Qualifier-View, Shipment-View.
Restricting Certain ACL/Entry Points:
To restrict users from seeing Bulk Plan related data, create a custom ACL using these child ACLs:
- Bulk Plan — View
- Bulk Plan — Update
- Bulk Plan — Actions
To use this as a restricted list in the role definition, uncheck the “Granted” checkbox when saving this ACL in the “Access Control List” section of the Role definition.
If you cannot control certain access with ACLs provided by Oracle, you may need to go to the Entry Point level and define your own ACLs. For example, to prevent users from accessing User Preferences, Recurring Process, or Business Monitor templates, Entry Point level access control lists can be created and restricted.
Assigning a Role to a User:
OTM Tables:
Query to see the default role associated to a user and vice versa:
select * from gl_user where gl_user_gid = 'DOMAIN.USER_ID_VALUE'
select * from gl_user where default_user_role_gid = 'DOMAIN.ROLE_ID'Query to see role definition (VPD, etc.):
select * from user_role where user_role_gid = 'DOMAIN.ROLE_ID'Query to see ACLs associated to a user role:
SELECT * FROM USER_ROLE_ACR_ROLE WHERE USER_ROLE_GID = 'DOMAIN.ROLE_ID'Query to see ACLs available within an ACL:
SELECT * FROM ACR_ROLE_ROLE WHERE ACR_ROLE_GID = 'DOMAIN.ACCESS_CONTROL_LIST_ID'Query to see entry points available within an ACL:
SELECT * FROM ACR_ROLE_ENTRY_POINT WHERE ACR_ROLE_GID = 'ACCESS CONTROL LIST ID OR SUB LIST ID'Steps to disable SQL Servlet access for some users:
At the role level, associate an ACL that restricts these two elements:
- SQL - Update
- SQL - View
You can test by re-logging in and accessing the servlet at:
https://hostname/GC3/glog.webserver.sql.SqlServlet