VPA Groups Use Case: Bridge Tables (Sites example)

In some cases, you want to specify a restriction via a bridging table.

Take, for example, an organization that has 6,000 buildings assigned to 500 sites. You want to maintain just the VPA Group lists assigning sites to roles (managing 500 items) rather than assign buildings to roles (which would require managing all 6,000 items).

You can create a restriction that "bridges" between the table being restricted (e.g. the rm table) through the validating table (e.g. the bl table) to the bridge table (e.g. the site table, which has the VPA groups, but not the VPA restriction).

VPA Groups to Sites Mapping (vpa_site) table

Set up this mapping table:

VPA Groups to Roles Mapping (vpa_groupstoroles) table

Assign the VPA Groups to roles.

Restrictions (vpa_rest) table

Enter a "forTable" restriction on the bl table. This restriction makes certain that the building is assigned to a site that is in this role's or this user's current list of VPA Groups.

Validating Table Name	Role Name	Type	Query
bl	Z-VPA-MGR-GEO-US-EAST	Explicit Query	(EXISTS (SELECT 1 FROM ${sql.mainTable}, bl WHERE ${sql.mainTable}.bl_id = bl.bl_id AND ${sql.getVpaGroupsRestrictionForBridgeTable( "bl", "site" )}))

Resulting Query

For instance, suppose a user who is a member of the GEO-US-EAST VPA group makes a query on the Rooms table.

SELECT DISTINCT rm.bl_id FROM rm WHERE (EXISTS (SELECT 1 FROM rm, bl WHERE rm.bl_id = bl.bl_id AND 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 LIKE '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 ) ))

It would return: