Data Access Control
Virtual Private Database Overview
The Oracle Transportation and Global Trade Management Service utilize the Oracle database functionality of Virtual Private Database or VPD. VPD allows for fine grained row level data security at the database tier layer. In brief, VPD works by dynamically adding a SQL WHERE clause to the SQL statement to provide data security. The dynamic SQL WHERE can be different for all of the different types of SQL statements like SELECT, INSERT, UPDATE, and DELETE.
Within the Oracle database there are different VPD Policy Types which control how the Oracle database actually caches the Policy Predicates. The service rely on all of the VPD Policy Types to be set to SHARED_CONTEXT_SENSITIVE in the Oracle database.
The relevant policy type is described here:
| VPD Policy Types | Description |
|---|---|
| SHARED_CONTEXT_SENSITIVE | Executes policy function when the table is first referenced in a database session or whenever local context changes. |
The service utilize VPD policies to provide two distinct data visibility capabilities called Domain Grants and External Predicates.
The domain grants capability allows the ability to give access to data in a different business domain. These can be managed through the Domain Grants user interface. By default, the service attach domain grant predicates to every table in a query. Since this domain criteria can have an adverse effect on query performance, the service utilize a capability referred to as “Active Table”. Active table provides the ability to specify which child table policies to use for a given SQL statement.
External predicates provide the ability to attach customer-defined predicates to individual tables. The predicates will automatically be appended to any SQL statement, in addition to domain grant predicates. External predicates are defined in a VPD Profile, which is assigned to a user role. External predicates provide the ability to create customer-defined data security rules (i.e. users can only see orders with a source location associated with them).
A VPD Profile is basically a set of VPD rules. A VPD rule provides the configuration capability to specific fine-grained data access control to a group of users or individual users. This data access control can be across or within Domains. The VPD Profile is then assigned to a user role. The VPD Profile allows for the use of external predicate rules, the use of the insert user rule, and the use of the service provider rule. The Use External Predicate rule enables and disables the external predicates specified in the rules. The Use Insert User rule limits the data access to only the data that the current user entered. This is ideal for a data entry employee who enters data across different domains, and should only be able to view and modify data that was entered by them. The Use of the Service Provider rule limits the data access to only the data where the user is associated to the service provider.
Please be aware that there are service performance implications when utilizing the VPD capabilities of the Oracle database. There is slight overhead in the service when setting up the user context when using a database connection. The dynamic SQL WHERE clauses that gets appended to the SQL statement could also cause additional overhead, and could completely change the execution plan used by the database. Also, depending on the customer-defined predicates and domain grants that are configured within the service there could be additional performance concerns.
Default VPD Context Variables Attributes
The service define an application context and provide the ability to allow use of context variables to be embedded within external predicates. The default application context that is used in the VPD functionality is gl_user_ctx. The default application context of gl_user_ctx has attributes that are used to build the standard VPD predicates within the service. A few examples of these attributes are DOMAIN_NAME, FROM_DOMAIN, GL_USER_GID, and VPD_DOMAIN_NAME.
There are pre-existing and default VPD Context Variables that are staged with the service for use when configuring external predicates.
VPD Context Variable Attributes
- domain_name
- gl_user_gid
- user_role_gid
Default VPD Profiles
There are pre-existing and default VPD Profiles that are staged with the service.
| Default VPD Profiles | Description |
|---|---|
| DATAENTRY | Limits data access to only those records in which a user has entered the data. Thus, in effect, users create personalized databases limited to the records they have created. |
| DBA | Provides data access for the DBA.ADMIN user. All data is visible with this profile, so it should not be used with any other user role other than a DBA user role. |
| DEFAULT | Provides data access to the entire domain, PUBLIC data, and any other data to which they have been granted access. |
| OTM-GUEST | Limits data access entirely and is only intended for OTM-GUEST user role. |
| INVOLVED_PARTY_DOMAIN_VIS | Limits data access to only records for business objects like order base, order release, and shipments in a particular domain in which the user is an involved party in that domain. |
| INVOLVED_PARTY_USER_VIS | Limits data access to only records for business objects like order base, order release, and shipments in which the user is an involved party. |
| SERVPROV | This VPD profile should be set for all users who are service providers. It contains the applicable Oracle TI specific external predicates for HD tables that limits data access to only those shipments, rates and capacity usages in which the user is associated with the Service Provider. |
| FTI_DEFAULT |
This VPD Profile is applicable for all Oracle TI users who are not service providers in Oracle Transportation Management. This includes all the external predicates available in the existing DEFAULT Oracle Transportation Management VPD profile and the new external predicates specific to the Oracle TI solution’s historical database tables. There are some standard external predicates (specific to TI) applied to each TI table/materialized views that are associated to this VPD profile. Every TI user should be associated to either the FTI_DEFAULT or SERVPROV VPD profile. |
| GTI_DEFAULT |
This includes all the external predicates available in the existing DEFAULT and FTI_DEFAULT Oracle Transportation Management VPD profiles and the new external predicates specific to the Oracle Global Trade Intelligence solution’s Historical Database tables. There are some standard external predicates (specific to GTI) applied to each GTI table that are associated to this VPD profile. Every GTI user should be associated to either the GTI_DEFAULT profile. |
Additional Data Access Constraints
For security purposes, there are additional security data access constraints for the following:
- Only users with the "DBA.ADMIN","ADMIN" or "SERVPROV.ADMIN" user roles have rights
to create/update/delete on these tables
- DOMAIN
- DOMAIN_GRANTS_MADE
- EXTERNAL_PREDICATE
- USER_ROLE
- USER_ROLE_GRANT
- VPD_CONTEXT
- VPD_CONTEXT_VARIABLE
- VPD_PROFILE
- ROLE_ROLE_GRANT
- Only users with the "DBA.ADMIN","ADMIN" or "SERVPROV.ADMIN" user roles user role have rights to create/update user role grants
- Only users with the "DBA.ADMIN","ADMIN" or "SERVPROV.ADMIN" user role have rights to create/update users to have the "ADMIN" user role.
- Only users with the "DBA.ADMIN" user role as the default user role have rights to create/update users to have the "DBA.ADMIN" user role.
- Users only have rights to modify their own user record, unless the logged in user has the "DBA.ADMIN","ADMIN", "SERVPROV.ADMIN", or the "USER-ADMINISTRATION" user role.
- Only the "ADMIN" user role is allowed for each individual "DOMAIN.ADMIN" user.
- The "SERVPROV.ADMIN" user can have only "SERVPROV.ADMIN" user role.
- Users are blocked from assigning "DBA.ADMIN" role to themselves or others, unless the logged in user already has the "DBA.ADMIN" user role. Non "DBA.ADMIN" users who already have the "DBA.ADMIN" user role are grandfathered in.