VPA Groups Use Case: Building Code List - Assigning VPA Groups to Roles

This topic discusses how an Archibus Administrator can specify a Building Code List using VPA Groups.

With standard VPA, an administrator can specify a Building Code List or a Site Code List in the Archibus Users table. With VPA groups, you can implement a mapping table strategy that can apply to any Archibus table, not just to the Buildings and Sites tables.

Define VPA Groups to buildings mappings

Using the vpa_bl table, create sets of buildings organized into VPA Groups that define the buildings that different roles – such as east coast versus west coast management – should be able to access.

Define roles and assign roles to VPA Groups

Define roles in the afm_roles table:

and then assigns one or more VPA Groups to these roles (the vpa_groupstoroles table):

Assign users to roles

Assign users to roles:

Establish restrictions in the VPA Restrictions Table

In the vpa_rest table, establish a set of restrictions that the Web Central program will apply to all user roles and accounts when the user signs in.

The VPA ALL OTHER ROLES assignment makes this restriction work for all roles that do not have another VPA Restriction (vpa_rest) on the Buildings table.

Resulting SQL Restriction Clauses

With the above definitions, the program assembles a VPA restriction clause appropriate for the Buildings table or tables validated by the Buildings table.

Clause on Buildings table

For instance, the restriction clause on table Buildings (bl) would be:

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-MGR-GEO-US' // Role name associated with VPA Groups
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' // Which user name is not important in this use case.
AND vpa_bl.bl_id = bl.bl_id
)

And would return:

Clause on table validated by the Buildings table

For instance, the restriction clause on table Floors (fl) would be:

SELECT fl.bl_id, fl.fl_id FROM fl 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-MGR-GEO-US' // Role name associated with VPA Groups AND vpa_bl.bl_id=fl.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' // Which user name is not important in this use case. AND vpa_bl.bl_id = fl.bl_id )

And would return: