Home > Knowledge Base > Checking user and group privileges

Checking user and group privileges

18.05.2021

Knowledge Base

#privileges

Applies to version: 2021.1.x and above; author: Adrian Baszak

Introduction

This article presents a workflow for checking user’s and group’s privileges in one place. It can become useful when having many deployed applications and roles with different privileges. This workflow may be used as a part of a larger group management application based on WEBCON BPS.

In the workflow, one form will display:
•   Basic user information that is stored on the BPS users list.
•   Groups to which the selected user belongs.
•   Users who belong to the selected group.
•   The user’s or group’s privileges at the application level, and at the process level, including workflow associations with the form types.
•   Business rules that point to a specific user or group.

The image shows the form for checking user's or group privileges — Fig.1 Form for checking user's or group's privileges

For each example, we will show the user’s view and the SQL query that returns the appropriate data.

Choosing a user or a group

Before any data is displayed, the user must select a person or group. Basic information about the user will be displayed next to it.

The image shows how to select a user of group to see information about them — Fig.2 Selecting a user or a group to see information about them

SQL query returning user data from BPS user list:

select COS_BpsID
, COS_DisplayName
, COS_Department
, COS_Email
, COS_JobTitle
, COS_ManagerBpsID
, COS_ManagerDisplayName
from CacheOrganizationStructure
where COS_BpsID = '{formFieldID}'

‘{formFieldID}’ should be replaced with the value from the user’s choice field.

The image shows the SQL query for returning user information in WEBCON BPS — Fig.3 SQL query for returning user information in WEBCON BPS

User groups

After selecting a user, it is possible to display a list of all groups to which they belong.

The image shows the groups to which the user belongs to — Fig.4 Displaying groups to which the user belongs to

The group ID has been configured as a link to start a new instance of the same workflow. By default, the clicked group will be prompted as the user. Thanks to this, you can immediately check the group’s privileges.

The image shows how to configuring group ID as a link starting a new instance — Fig.5 Configuring group ID as a link starting new instance

SQL query that returns all groups to which the selected user belongs:

select gr . COS_BPSID
, gr. COS_DisplayName
, ( case gr. COS_AccountType
when '4' then 'Active Directory'
when '2' then 'BPS' end ) as Type
from CacheOrganizationStructure as gr
join CacheOrganizationStructureGroupRelations on COSGR_GroupID = gr . COS_ID
join CacheOrganizationStructure as u on u . COS_ID = COSGR_UserID
where u . COS_BPSID = '{formFieldID}'
order by 3 , 2

Users in the group

If a group has been selected, we can display a list of its users.

The image shows group users — Fig.6 Displaying group users

As with the group ID, the user ID has been set up as a link to start a new instance of the same workflow so that you can check the privileges of that user right away.

SQL query returning all users belonging to the selected group:

select u . COS_BpsID
, u. COS_DisplayName
from CacheOrganizationStructure as gr
join CacheOrganizationStructureGroupRelations on COSGR_GroupID = gr . COS_ID
join CacheOrganizationStructure as u on u . COS_ID = COSGR_UserID
where gr . COS_BPSID = '{formFieldID}'
order by 2

Application-level privileges

In the following table, we can display a list of applications for which a given group has privileges:

The image shows application privileges given by groups — Fig.7 Displaying application privileges given by groups

A SQL query that returns all the applications for which the user or group has privileges:

select CSC_APPID

, APP_Name

, lvl. Name

from WFConfigurationSecurities

join WFApplications on APP_ID = CSC_APPID

join DicConfigurationSecurityLevels as lvl on lvl . TypeID = CSC_LevelID

where CSC_USERGUID = '{formFieldID}'

order by 2

When checking user privileges, please note that this list will only show users’ privileges granted directly in BPS Designer Studio. The user may have other privileges resulting from belonging to a group. To check these privileges, see the group’s privileges. This remark also applies to the following examples.

Process-level privileges and associating form types with the workflow

The image shows user privileges in individual processes and workflows — Fig.8 Displaying user privileges in individual processes and workflows

The information about available applications can be extended with a list of user privileges in individual processes and workflows.

SQL query that returns all processes, workflows, and form types for which the user or group has privileges:

select DEF_Name as Process

, '<All>' as Workflow

, '<All>' as FormType

, lvl. Name as [Level]

, isnull ( COM_Name, '<All>' ) as BusinessEnity

from WFSecurities

join WFDefinitions on DEF_ID = SEC_DEFID

join DicSecurityLevels as lvl on lvl . TypeID = SEC_LevelID

left join Companies on COM_ID = SEC_COMID

where SEC_USERGUID = '{formFieldID}'

union all

select DEF_Name

, WF_Name

, DTYPE_Name

, lvl. Name

, isnull ( COM_Name, '<All>' ) as COM_Name

from DocTypeAssocciations

join WFDocTypes on DTYPE_ID = ASS_DTYPEID

join WorkFlows on WF_ID = ASS_WFID

join WFSecurities on SEC_ASSID = ASS_ID

join WFDefinitions on DEF_ID = DTYPE_DEFID

join DicSecurityLevels as lvl on lvl . TypeID = SEC_LevelID

left join Companies on COM_ID = SEC_COMID

where SEC_USERGUID = '{formFieldID}'

order by 1 , 2 , 3

Usages in business rules

When configuring applications, we may adopt the convention that roles are represented by their corresponding business rules. This will enable us to display all of these rules to see what privileges are given with roles.

The rule configuration should look like in the following screenshot. It can be used to assign a task or in an action that grants privileges.

The image shows the roles configuration — Fig.9 Configuring roles when they are represented by their corresponding business rules

The rule documentation should accurately describe its role so that you can tell what the rule does without checking its configuration. Thanks to this, it will be enough to display the list of rules and their description to know what privileges are given with roles.

The image shows a list of rules to check what privileges are given with roles — Fig.10 Displaying list of rules to check what privileges are given with roles

SQL query that returns business rules that use the user or group:

select users . BRD_ID

, isnull ( DEF_Name, '<Global>' ) as DEF_Name

, usage. BRD_Name

, usage. BRD_Documentation

from WFBusinessRuleDefinitions as users

join WFBusinessRuleDefinitions as usage on usage . BRD_ID = users . BRD_BRDID

left join WFDefinitions on DEF_ID = usage . BRD_DEFID

where dbo . ClearWFElemIDAdv ( users . BRD_Users ) like '% {formFieldID}%'

and users. BRD_ReturnedValueType = 5

and users. BRD_IsLocal = 1

Summary

The presented reports and queries can be used immediately, or they can be the basis for creating your own reports on authorizations; for example:
•   In multilingual environments, you can add translations for application names, workflows, and form types.
•   You can associate a workflow with a workflow for managing group members - e.g., by links to the workflow that adds a user to a group. (see https://community.webcon.com/posts/post/managing-of-bps-groups/37 )
•   Display where a given group or user is used - for example, “USER IS ONE OF” rules.
•   Each query can be used independently in a different process.