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.

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.

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: