VPA Groups Use Case: Legal IDs (Explicit Query, Role-specific VPA)
Rather than VPA Groups, you may wish to use an explicit query.
Uses for explicit queries
Use explicit queries for complex conditions such as:
- restricting against multi-part keys (which need two correlating values in the WHERE clause)
- restricting that join through a third table (which need two subquery selects that are nested or AND'ed together)
- restricting against enumerated fields (which do not need a mapping table)
- restricting against a hierarchical value
- providing a restriction for administrators that need more general access to data than the default VPA Groups style restriction provides.
Examples hierarchical restrictions might be security groups, CSI codes, account codes, telecom hierarchy values, or problem types that use prefixes and concatenated values to express a hierarchy. In these cases, a LIKE restriction is often most convenient.
Hierarchy example (CSI Codes)
For instance, suppose you want to limit the CSI table and all tables that validate against it (Equipment, Action Items, etc.) to just structural items:
You would use a VPA Restriction (vpa_rest) record like the following:
| Validating Table Name | Type | Query |
|---|---|---|
| Type | Explicit Query | (${sql.mainTable}.csi_id LIKE '051%' ) |
When you enable the VPA Groups feature, the explicit subquery option takes the place of the SQL Restriction in the Archibus Roles (afm_roles) table.
Resulting Query
The resulting query and restriction on an Equipment table would be of the form:
SELECT eq_id
FROM eq
WHERE eq.csi_id LIKE '051%'
Equipment example
For instance, suppose each record of the Equipment (eq) table (and any other table needing this restriction) is labeled with a Legal ID. This ID records which legal entity owns that record of data.
Users (afm_users) table
You can assign each user account a Legal ID identifying the legal entity to which that user belongs.
When the program creates the user account, it caches the Legal ID for each user and makes it accessible via the ${user.legalId} macro0.
VPA restrictions (vpa_rest) table
You can set up an explicit restriction on the Equipment (eq) table:
The ${sql.mainTable} macro evaluates to the main table of the datasource being evaluated.
Resulting Query
The resulting query and restriction on an Equipment table query for a user in the "BSC" Legal entity would be of the form:
SELECT eq_id, eq.legal_id FROM eq WHERE
eq.legal_id='BSC' ;
The result is:
Alternate Example: Customer Service Representatives (CSRs)
Example above illustrates how to use an explicit query, and the legalId, to set up a restriction for each staff member at a customer site. Then staff members can use only data that belongs to the specified customer. This section presents an alternate example, which uses the legalId macro to save you from having to set up a VPA Groups to Roles mapping for Legal ID for each customer role.
Suppose you wish to set up a role for the service provider's customer service representatives, who can access multiple customer's data. In this case, you can set up an explicit restriction in the VPA Restrictions (vpa_rest) table:
For users of role Z-VPA-CSR, this restriction will take precedence over the VPA ALL OTHER ROLES restriction.
In addition, you can set up an "all customers" VPA Group such as CSR-ALL-CMRS in the Legal IDs to Groups Mapping Table (vpa_legal):
Use the VPA Groups to Roles Mapping table (vpa_groupstoroles) to add the group to the Z-VRA-CSR role:
The program returns a VPA Restriction clause that grants appropriate access. For instance, the restriction on the eq table would be in the form:
SELECT eq.eq_id, legal_id FROM eq WHERE EXISTS ( SELECT 1 from vpa_groupstoroles AS vgr INNER JOIN vpa_legal ON vpa_legal.vpa_group_id = vgr.vpa_group_id WHERE vgr.role_name = 'Z-VPA-CSR' // Role name associated with VPA Groups AND vpa_legal.legal_id=eq.legal_id UNION SELECT 1 FROM vpa_groupstousers AS vgu INNER JOIN vpa_legal ON vpa_legal.vpa_group_id = vgu.vpa_group_id WHERE vgu.user_name = 'AFM' // Which user name is not important in this use case. AND vpa_legal.legal_id = eq.legal_id )
Applied to sample data, the result is:
