🔒 WEBCON Privileges

There comes a time, when WEBCON gets more popular in the company, and lots of users are working in the system. In this post, I'll guide you through the process of how to determine which users have access to specific applications/processes/workflows/forms within WEBCON

So let's start at the beginning - there is a really good matrix showing where you can set user privileges on the WEBCON Community, and there is whole article about it, explaining where you can configure them.

On the left side of the table are scopes, and levels on which you can define privileges - in the parenthesis name of the level in database dictionaries:

• Global
  • System administrators (AppAdministrator)
  • Business administrators (Admin)
  • Workflow data read-only access (ReadOnly)
• Application
  • Application Administrator (AppAdministrator)
  • Portal designer (AppDesigner)
  • Application access (Visibility)
  • Metadata access (AppMetadata)
• Process and Workflow/Form combination
  • Business administrator (Admin)
  • Access and edit all workflow instances (Modify without delete)
  • Launch new workflow instances (AddNew)
  • Access all workflow instances (ReadOnly)
  • Access all workflow instances (excluding attachments) (ReadOnly without attachments)
  • Manage archive (haven't found data)
  • Designer Desk edit in Portal (Designer Desk edit in Portal)

So the granularity is really nice, there is also possibility to grant/remove privileges per each document - I'll talk about it later, but I wont focus on this case. We can set them per user, or per group which might come from different sources:

• Active Directory
• Azure Active Directory
• BPS Users
• Custom LDAP Server

We have to consider them based on our environment - in my case I have AAD, and BPS Users and groups, so I'll focus on those. There is a chance, that it will work on AD and LDAP too, but can't guarantee that.

Where to find what we need?

BPS_Content is the database with our answers, we will use quite a few tables from here:

• CacheOrganizationStructure - Keeps all the users and groups
• CacheOrganizationStructureGroupRelations - Keeps information about which users are connected to which group
• WFApplications - Keeps list of all applications
• WFDefinitions - Keeps list of all processes
• WorkFlows - Keeps list of all workflows
• WFDocTypes - Keeps list of all form types
• DocTypeAssocciations - Keeps connections between form types and workflows - form doesn't have to be available for each workflow in a process
• WFConfigurationSecurities - Keeps information about assigned privileges per application, start button, report, dasbhoard
• WFSecurities - Keeps information about assigned privileges per document (each one in the system look at SEC_WFDID column), workflow, company, process, or global
• DicConfigurationSecurityLevels - Dictionary of configuration security levels
• DicSecurityLevels - Dictionary of security levels
• Companies - List of all companies

Users and groups

There is a lot to do, I'll show and explain first few parts of the SQL needed, and at the end there will be combined CTE. At first we need users and groups, so those are queries that returns them:

WITH 
  Users (COS_ID, COS_DisplayName, COS_BpsID) AS (
        SELECT
            COS_ID,
            COS_DisplayName,
            COS_BpsID
        FROM
            CacheOrganizationStructure 
        WHERE
            COS_AccountType IN (
                1 -- AAD User
            )
    ), 

    Groups (COS_ID, COS_DisplayName, COS_BpsID) AS (
        SELECT
            COS_ID,
            COS_DisplayName,
            COS_BpsID
        FROM
            CacheOrganizationStructure 
        WHERE
            COS_AccountType IN (
                4, -- BPS Group
                2 -- AAD Group
            )
    ),

This might require slight change depending on environment - in my case, this filter was good enough to separate which records are users, and which are groups.

    UsersAndUsersInGroups (COS_UserBpsID, COS_GroupBpsID, UserName, GroupName) AS (
        SELECT
            u.COS_BpsID,
            g.COS_BpsID,
            u.COS_DisplayName,
            g.COS_DisplayName
        FROM
            Users u JOIN
            CacheOrganizationStructureGroupRelations c ON u.COS_ID = c.COSGR_UserID JOIN
            Groups g ON g.COS_ID = c.COSGR_GroupID
        
        UNION ALL

        SELECT
            COS_BpsID,
            NULL,
            COS_DisplayName,
            NULL
        FROM
            Users
    ),

Global Privileges

This provides us first part of the question - what users are in the system. Now going to second question - where do they have privileges. At the end we want to have those columns Scope, Level, ID, UserID, UserName, GroupID, GroupName, APP_Name, DEF_Name, WF_Name, DTYPE_Name, COM_Name. We will start with the highest scope - global.

    GlobalPrivileges (Scope, Level, ID, UserID, UserName, GroupID, GroupName, APP_Name, DEF_Name, WF_Name, DTYPE_Name, COM_Name) AS (
        SELECT
            'Global' As Scope,
            d.Name,
            NULL,
            u.COS_UserBpsID,
            u.UserName,
            u.COS_GroupBpsID,
            u.GroupName,
            NULL As APP_Name,
            NULL As DEF_Name,
            NULL As WF_Name,
            NULL As DTYPE_Name,
            NULL As COM_Name
        FROM
            WFConfigurationSecurities JOIN
            UsersAndUsersInGroups u ON (u.COS_UserBpsID = CSC_USERGUID AND u.COS_GroupBpsID IS NULL) OR u.COS_GroupBpsID = CSC_USERGUID JOIN
            DicConfigurationSecurityLevels d ON CSC_LevelID = d.TypeID
        WHERE
            CSC_IsGlobal = 1
    ),

There is a lot of NULL values due to the fact that there is no application, process, definition, workflow, form or company data in this scope - the amount of NULL's will be smaller with increasing granularity - we need them, to UNION ALL of the privileges at the end.

It's mostly regular query, but we have one specific JOIN condition (u.COS_UserBpsID = CSC_USERGUID AND u.COS_GroupBpsID IS NULL) OR u.COS_GroupBpsID = CSC_USERGUID - we want to join on COS_UserBpsID only if it's not a group, because in UsersAndUsersInGroups user identifiers are not distinct - they might be there multiple times - if the user is member of any group.

Further on it's mostly the same thing - we are just joining different tables to get application/process/workflow/form. The final version is available here:

GitHub - lumenn/webcon-privileges-lookup

Contribute to lumenn/webcon-privileges-lookup development by creating an account on GitHub.

GitHublumenn

In the future i might prepare some report with BI tool, if it will see the light of the day there will be a post on that for sure.