Assign VPA Groups Using Mapping Tables

The VPA Groups feature uses a related set of mapping tables to map and record access rights for users and roles. This topic explains how to assign VPA groups using mapping tables, in the following sections:

Discussion

Discussion focuses on several points related to mapping tables and VPA Groups. To create new mapping tables, refer to sample tables and code in this topic, and topics listed in VPA Groups: Use Cases . See especially sample tables for vpa_bl and vpa_site, as well as references to vpa_eq and vpa_servcont. Adapt these examples to your own environment. The points below help you plan your mapping tables.

Simple and complex cases

In the simplest case, for example, vpa_bl and vpa_site each have two fields: the VPA Group field, and the field that contains the variable to be restricted. In more complex cases, internal VPA Groups logic may comprehend other groups and restricted variables.

Multipart keys

If you apply a restriction to a table with a multipart key, you can specify in the code how the VPA Group logic defines the resulting fields. For example, you might specify that vpa_dp populates both dv_id and dp_id, after a restriction is applied.

Naming conventions

Be consistent, concise, and descriptive when you choose names for tables and fields. Naming conventions help you navigate the structure of your VPA Groups as you maintain and refine them.

Use cases

Use cases contain instructions about how to develop mapping tables and apply restrictions in order to define VPA Groups. Several of these use cases contain sample code, and resulting output. See especially the first three examples:

Typical Mapping Table

Mapping tables assign a value or code to a VPA Group. In the example below, a VPA buildings mapping table, vpa_bl, assigns building codes to one of two VPA Groups.

VPA Group ID Building Code
REGN-EAST HQ
REGN-EAST SRL
REGN-EAST BOSMED
REGN-EAST JFK-A
REGN-WEST LA-OFFICE
REGN-WEST SF-OFFICE
REGN-WEST OAK-WARE

VPA Restrictions refer to these lists when they apply restrictions.

VPA Groups to Buildings Mapping (vpa_bl) table

You can assign inventory records, such as Buildings records, to VPA Groups using one mapping table.  The VPA Group ID identifies a group of buildings.

VPA Group ID

Building Code

REGN-EAST

HQ

REGN-EAST

SRL

REGN-WEST

LA-OFFICE

REGN-WEST

SF-OFFICE

VPA Groups to Roles (vpa_groupstoroles) mapping table

You can assign VPA Groups to roles via a mapping table.  Doing so gives any user who is a member of that role access to all buildings that are part of one of those VPA Groups.

Role

VPA Groups

MGR-REGN-EAST

REGN-EAST

MGR-REGN-WEST

REGN-WEST

MGR-US

REGN-EAST

MGR-US

REGN-WEST

...

VPA Groups to Users (vpa_groupstousers) mapping table

You can also assign VPA Groups  to individual users via a mapping table.  Doing so gives that user access to all buildings that are part of that VPA Group.

User VPA Groups

CARLO

REGN-ONTARIO

ABERNATHY

REGN-NUNAVUT

...

VPA Restrictions (vpa_rest) table

Use the VPA Restrictions table to inform the Web Central program that it should apply a VPA restriction to each table.  To do so, the Web Central core assembles a forValidatingTables VPA restriction on the "Applies to table."

If you use the VPA Groups option, as shown below, the program assembles a subquery that uses the VPA Groups mapping tables.

Applies to table Applies to role Type Query

bl

VPA Groups

afm_legal

VPA Groups

...

Example restriction: Buildings table

For instance, a VPA Groups restriction on the Buildings ( bl ) table instructs the Web Central program to add the VPA WHERE clause shown below to all queries on the Buildings table:

SELECT bl.bl_id FROM bl WHERE ${sql.getVpaRestrictionForTable("bl")}

The program also adds the same where clause to all queries against tables that validate on the Buildings table, such as the Rooms (rm) table:

SELECT rm.bl_id, rm.fl_id, rm.rm_id FROM rm WHERE ${sql.getVpaRestrictionForTable("bl" )}

Resulting queries: Buildings table

A query against the Buildings table expands to an SQL statement shown below. See example below for ${sql.getVpaRestrictionForTable(table)} .

Resulting queries: Rooms table

A query against the rooms table would expand to an SQL statement shown below. See example below for ${sql.getVpaRestrictionForTable(table)} .

Example restriction: personalized SQL queries

In some cases, you will want to relax the default VPA restriction for a personalized SQL query, as the default restriction for the "main" table of the query may not be appropriate.

These personalized SQL queries can use the VPA group restriction binding expression directly.

In the example below, we want to use the bl_vpa table groups to restrict buildings and equipment.  However the main table for the datasource is asset and not bl .  The personalized query can specify the restriction clause using the binding expression.

<dataSource>
<table name="asset"/>
<field name="cost_insurance"/>
<sql dialect="generic">
SELECT value_market FROM bl
WHERE ${sql.getVpaRestrictionForTable('bl')}  // "main" table does not match desired "VPA Table" UNION
SELECT cost_replace FROM eq
WHERE ${sql.getVpaRestrictionForTable('bl')}  // "main" table does not match desired "VPA Table"
</sql>
</dataSource>

The core creates VPAs for the current user's role based on the  VPA Restriction ( vpa_rest ) definition.   The sql.getVpaRestrictionForTable( table ) expression will return that restriction.

  • For explicit SQL queries (vpa_rest.type="EXPLICITQUERY"), the sql.getVpaRestrictionForTable( table ) will return that explicit SQL query literally.
  • For VPA Group restrictions (vpa_rest.type="VPAGROUPS"), the sql.getVpaRestrictionForTable( table ) macro will return an SQL expression formatted like that below.

Assembling the VPA Groups query

When creating the user role, the core program creates <restriction /> entries like the following for any role that has a VPA Groups restriction.

<restriction
type="forVpaGroups">
<title translatable="true">Vpa Groups Restriction on bl</title>
<table name="bl"/>
</restriction>

When called by the DataSource code or by the sql.getVpaRestrictionForTable( table ) binding expression to produce an SQL clause, the program uses a Java method with the approach below to assemble this SQL. The program must generate this SQL dynamically when the program is assembling the query because the program must evaluate the method below in the context of the current recordset or query.

${sql.getVpaRestrictionForTable(table)}

The caller supplies:

  • table = The name of the table (e.g. bl) which holds values that this VPA restriction limits access to.

This method retrieves these values from the context:

  • validatingField = The last primary key field of the validating table (e.g. bl_id).
  • userRole = The current user's role (as in ${user.role})
  • userName = The current user's name (as in ${user.name})
  • mainTable = The mainTable name in the current datasource or one of the standard table names defined in the datasource.  This is the table being restricted because it is the specified table or because one of its field validates against the specified table.

The method then calculates:

  • vpaMappingTable = "vpa_" + mainTable

Then the method inserts these values into the template below, and returns the resulting string.

EXISTS
( SELECT 1 from vpa_groupstoroles AS vgr
INNER JOIN vpaMappingTable ON vpaMappingTable.vpa_group_id = vgr.vpa_group_id
WHERE vgr.role_name = userRole
AND vpaMappingTable.validatingField=table.validatingField
UNION
SELECT 1 FROM vpa_groupstousers AS vgu
INNER JOIN vpaMappingTable ON vpaMappingTable.vpa_group_id = vgu.vpa_group_id
WHERE vgu.user_name = userName
AND vpaMappingTable.validatingField= table.validatingField
)

Examples:

Input:

SELECT bl.bl_id FROM bl WHERE ${sql.getVpaRestrictionForTable('bl')}

Output:

SELECT bl.bl_id FROM bl WHERE
EXISTS
( SELECT 1 from vpa_groupstoroles AS vgr
INNER JOIN vpa_bl ON vpa_bl.vpa_group_id = vgr.vpa_group_id
WHERE vgr.role_name = 'Z-VPA-EXEC-MGR'
AND vpa_bl.bl_id=bl.bl_id
UNION
SELECT 1 FROM vpa_groupstousers AS vgu
INNER JOIN vpa_bl ON vpa_bl.vpa_group_id = vgu.vpa_group_id
WHERE vgu.user_name = 'AFM'
AND vpa_bl.bl_id = bl.bl_id
)

Input:

SELECT rm.bl_id, rm.fl_id, rm.rm_id FROM rm WHERE ${sql.getVpaRestrictionForTable('bl')}

Output:

SELECT rm.bl_id, rm.fl_id, rm.rm_id FROM rm WHERE
EXISTS
( SELECT 1 from vpa_groupstoroles AS vgr
INNER JOIN vpa_bl ON vpa_bl.vpa_group_id = vgr.vpa_group_id
WHERE vgr.role_name = 'Z-VPA-EXEC-MGR'
AND vpa_bl.bl_id=rm.bl_id
UNION
SELECT 1 FROM vpa_groupstousers AS vgu
INNER JOIN vpa_bl ON vpa_bl.vpa_group_id = vgu.vpa_group_id
WHERE vgu.user_name = 'AFM'
AND vpa_bl.bl_id = rm.bl_id
)

${sql.getVpaGroupsRestrictionForBridgeTable( validatingTable,  bridgeTable)}

This binding expression allows access to an sql formatting expression like the one above.  Use this macro for defining restrictions using bridge tables – such as using the Buildings (bl) table to bridge between Rooms (rm) and Sites (site).

The differences in the bridge table condition are that:

  • the bridgeTable that the query relates in the vpaMappingTable is the bridge table (bl) and not the inventory table being queried (rm)
  • the validatingTable (site) validates the bridge table (bl) and not the inventory table being queried (rm)

For this reason, the caller provides the validatingTable and the bridgeTable .

This method retrieves these values from the context:

  • validatingField = The last primary key field of the validating table (e.g. site_id).
  • userRole = The current user's role (as in ${user.role})
  • userName = The current user's name (as in ${ user.name })

The method then calculates:

  • vpaMappingTable = "vpa_" + validatingTable

It uses the SQL template below:

EXISTS
( SELECT 1 from vpa_groupstoroles AS vgr
INNER JOIN vpaMappingTable ON vpaMappingTable.vpa_group_id = vgr.vpa_group_id
WHERE vgr.role_name = userRole
AND vpaMappingTable.validatingField=bridgeTable.validatingField
UNION
SELECT 1 FROM vpa_groupstousers AS vgu
INNER JOIN vpaMappingTable ON vpaMappingTable.vpa_group_id = vgu.vpa_group_id
WHERE vgu.user_name = userName
AND vpaMappingTable.validatingField= bridgeTable.validatingField
)

Examples:

Input:

SELECT rm.bl_id, rm.fl_id, rm.rm_id FROM rm WHERE ${sql.getVpaGroupsRestrictionForBridgeTable( "site", "bl")}

Output:

SELECT rm.bl_id, rm.fl_id, rm.rm_id FROM rm WHERE
EXISTS
( SELECT 1 from vpa_groupstoroles AS vgr
INNER JOIN vpa_site ON vpa_site.vpa_group_id = vgr.vpa_group_id
WHERE vgr.role_name = 'Z-VPA-MGR-GEO-US-EAST'
AND vpa_site.site_id=bl.site_id
UNION
SELECT 1 FROM vpa_groupstousers AS vgu
INNER JOIN vpa_site ON vpa_site.vpa_group_id = vgu.vpa_group_id
WHERE vgu.user_name = 'AFM'
AND vpa_site.site_id = bl.site_id
)

See also

VPA Groups: Use Cases

Assign VPA Groups to Roles: Building Code List Example

Assign VPA Groups to Users: Building Code List Example

Use Legal IDs

Use bridge tables (Sites example)

Use bridge tables (Service Contracts example)

Unpack dependencies

Use a hard partition

Use a message workflow rule