WEBCON Admin Process – Checking user’s groups

Learn how to easily create a tool for WEBCON BPS administrators to verify user groups. Save time managing user membership to AD and SharePoint groups.

Management through groups

In the WEBCON BPS environment, the best practice to manage permissions in the system is based on group membership. Of course, this applies to both – Active Directory and SharePoint groups. Privileges for individual users should not be allocated directly in the process, but create the appropriate groups and grant those groups the rights. Thanks to this, we gain a lot. Below I mentioned some of the pros of this approach:

• You can give independent permissions on the test and production environment. This allows a larger group of users to have access and the ability to test applications at non-production environments
• It will be easier for you to manage permissions in the application
• You will be able to appoint business users to manage individual SharePoint groups without having to involve you or the IT department
• You will be able to automate the sending and receiving of roles using processes in WEBCON BPS. Thanks to standard actions you can add users to Active Directory groups and SharePoint groups

New user – new privileges

Any new employment or change of an employee position is associated with granting him new privileges at IT systems. In an ideal world, there should be prepared a matrix for each job in the company, on the basis of which it would be known what qualifications the employee has. Proper rights should be granted based on this matrix. However, we do not live in a perfect world and usually, therefore the administrator receives the command like “Give the same permissions as the user XYZ have”. And what now? Below I present an idea of how to easily deal with such an issue on the WEBCON BPS environment.

Administrative process – WEBCON User Groups Checker

Below is the concept of a simple tool used to display which SharePoint and Active Directory groups the user belongs to. I haven’t presented such a simple process in terms of the number of steps. The workflow consists of exactly one step and has no transition paths. The only thing on the form is the picker form field and two data tables that list the groups to which the user belongs. That’s all!

Process configuration – WEBCON User Groups Checker

Firstly prepare two constants. The first one is [SITE URL] – a base URL of your WEBCON site. The second one is [SITE ID] – a SharePoint Site ID – an alphanumerical string that you can find for example in the “Groups” table in the SharePoint content database.

Then create 3 form fields:

• User account – person or group form field
• Active Directory Groups – data table
• SharePoint Groups – data table

The ” User account” form field should have the checkbox marked “Value change will cause default values to be refreshed”. Thanks to this, after selecting the user, the SQL queries presenting the data will automatically refresh on the form after indicating the employee.

The “SharePoint groups” form field has the following query:

SELECT 'link:[SITE URL]/_layouts/15/people.aspx?MembershipGroupId='
       + Cast(id AS NVARCHAR(max))
       + ';displayname:' + title AS [Group],
       Description
FROM   [userinfo]
       JOIN groupmembership
         ON tp_id = memberid
       JOIN groups
         ON groupid = id
WHERE  tp_login LIKE '%[user account login]%'
       AND '[user account login]' <> ''
       AND groups.siteid = '[SITE ID]'

Note that this query must be executed on the SharePoint content database.

In this SQL query, there are some variables and constants. You should replace them with values appropriate to your environment.

While the SQL Table displaying Active Directory groups has the following query:

SELECT DISTINCT g.cos_ad_samaccountname AS [Group],
                g.cos_ad_description    AS [Description]
FROM   [dbo].[cacheorganizationstructure] u
       JOIN [dbo].[cacheorganizationstructuregrouprelations] gr
         ON gr.cosgr_userid = u.cos_id
       JOIN [dbo].[cacheorganizationstructure] g
         ON g.cos_id = gr.cosgr_groupid
WHERE  u.cos_login = '[user account login]'

Same as with previous SQL query, you have to replace variables.

And that’s all. All you need to do is to show these form fields on the WEBCON BPS form. The result may look like below:

Further development

The example I described allows you to easily verify which groups of SharePoint and Active Directory the chosen WEBCON user belong to. On this basis, you can manually add another employee to these groups. However, I recommend you to go a step further and make your life even easier. Using the “Add a user to a group” and “SharePoint group members management” WEBCON BPS standard actions, you can add them automatically from the WEBCON BPS form.

Thanks to this, you will indicate an existing employee who has the appropriate permissions, a newly admitted employee without permission. Moreover, you can replace data tables form fields with item lists with an additional checkbox column. Then, on the transition, start the sub-workflows that will add a new employee to the groups marked on the checkboxes with the items. Some practical examples of starting sub-workflows you may find here.