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.

use_case_1_bl_mapping_table.png

Define roles and assign roles to VPA Groups

Define roles in the afm_roles table:

use_case_1_roles_table.png

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

use_case_1_rolestogroups.png

Assign users to roles

Assign users to roles:

use_case_1_password.png

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.

use_case_1_restrictions.png

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:

use_case_1_return.png

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:

use_case_1_return2.png