[external] Managing and auditing Permissions

External content by Aleksander Damaz

One of the most common challenges WEBCON administrators face is analyzing user permissions. This problem becomes especially apparent in environments that have been growing for years, containing dozens of processes, hundreds of groups, and thousands of elements.

In practice, answering a seemingly simple question — “why does this user see this document?” — often requires clicking through many administrative screens and manually analyzing the system configuration.

The challenge with Permissions Analysis

Permissions in WEBCON can be granted at multiple levels:

• through group membership,
• as global permissions,
• at the process level,
• at the workflow and document type level,
• directly on workflow elements.

SELECT

    u.COS_BPSID       AS Login,

    u.COS_DisplayName AS Name,

    g.COS_DisplayName AS GroupName,

    gr.COSGR_TSInsert AS TSInsert,

    IIF(g.COS_AccountType = 4, 'AD', 'BPS') AS Type

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_IsActive = 1

  AND u.COS_AccountType = 1

  AND u.COS_BPSID = '{Cos_BPSID}'

ORDER BY Name

In larger organizations, a single user can belong to dozens of groups. This creates situations where identifying the source of a specific access right becomes time-consuming and error-prone.

How to Streamline Permissions Auditing?

One solution is to build a dedicated administrative application whose job is to aggregate a user’s permission information in a single place. Given a user’s login, the application returns a complete data set grouped into tabs — from group membership, through global and application-level permissions, to permissions at the process, workflow/document type, and individual element levels.

Using System Data

Most of the information needed to carry out an audit sits directly in the WEBCON database. Properly constructed SQL queries make it possible to join data about users, groups, processes, and workflow elements, and present it in a readable form — without manually searching through multiple administrative sections of the system.

Below is the set of SQL queries such an audit application runs on. In all queries, “{Cos_BPSID}” should be replaced with the user’s login in COS_BPSID format.

Group Information

A list of all groups the user belongs to, together with the group type (Webcon or Active Directory).

Global Privileges

A summary of the global permissions assigned to the user, including the SysAdmin privilege.

SELECT

    [Name] AS [Type of privileges],

    IIF(SEC_USERGUID = '{Cos_BPSID}', 'User', 'Group') AS [User/Group],

    SEC_Username AS [Name]

FROM [dbo].[WFSecurities]

JOIN [dbo].[DicSecurityLevels]

    ON SEC_LevelID = [TypeID]

WHERE SEC_IsPermanent = 1 and SEC_IsGlobal = 1

  AND (

        SEC_USERGUID = '{Cos_BPSID}'

        OR '{Cos_BPSID}' IN (

            SELECT u.COS_BpsID

            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 g.COS_BPSID = SEC_USERGUID

        )

      )

UNION ALL

SELECT

'SysAdmin' as [Type of privileges],

IIF(CSC_USERGUID = '{Cos_BPSID}', 'User', 'Group') AS [User/Group],

CSC_UserName as [Name]

FROM [dbo].[WFConfigurationSecurities]

WHERE [CSC_IsGlobal] = 1 and CSC_LevelID = 1 and

(

    CSC_USERGUID = '{Cos_BPSID}'

     OR '{Cos_BPSID}' IN (

            SELECT u.COS_BpsID

            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 g.COS_BPSID= CSC_USERGUID

        )

)

Privileges per Process

A view showing which processes the user has permissions assigned in.

SELECT

    [Name] AS [Type of privileges],

    DEF_Name AS [Proces],

    IIF(SEC_USERGUID = '{Cos_BPSID}', 'User', 'Group') AS [User/Group],

    SEC_Username AS [Name]

FROM [dbo].[WFSecurities]

JOIN [dbo].[DicSecurityLevels]

    ON SEC_LevelID = [TypeID]

JOIN [dbo].[WFDefinitions]

    ON DEF_ID = SEC_DEFID

WHERE (

        SEC_USERGUID = '{Cos_BPSID}'

        OR '{Cos_BPSID}' IN (

            SELECT u.COS_BpsID

            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 g.COS_BPSID = SEC_USERGUID

        )

      )

  AND SEC_DEFID IS NOT NULL

Privileges per Workflow / Document Type

A more granular level at which permissions can be granted is the intersection of workflow and form (document) type.

SELECT

    [Name] AS [Type of privileges],

    IIF(SEC_USERGUID = '{Cos_BPSID}', 'User', 'Group') AS [User/Group],

    SEC_Username AS [Name],

    DEF_Name AS Proces,

    WF_Name AS Workflow,

    DTYPE_Name AS DocType

FROM [dbo].[WFSecurities] AS Sec

JOIN [dbo].[DicSecurityLevels]

    ON SEC_LevelID = [TypeID]

JOIN dbo.DocTypeAssocciations

    ON SEC_ASSID = ASS_ID

JOIN dbo.WorkFlows

    ON WF_ID = ASS_WFID

JOIN dbo.WFDefinitions

    ON WF_WFDEFID = DEF_ID

JOIN dbo.WFDocTypes

    ON DTYPE_ID = ASS_DTYPEID

WHERE (

        SEC_USERGUID = '{Cos_BPSID}'

        OR '{Cos_BPSID}' IN (

            SELECT u.COS_BpsID

            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 g.COS_BPSID = SEC_USERGUID

        )

      )

Privileges per Element

The most granular level — permissions assigned to specific workflow elements, identified by signature and ID.

Note: This query’s performance depends on the number of elements in the database. In large installations with hundreds of thousands of elements, consider adding a scope filter (e.g., by process or date) before running it in production.

SELECT

    [Name] AS [Type of privileges],

    IIF(SEC_USERGUID = '{Cos_BPSID}', 'User', 'Group') AS [User/Group],

    SEC_Username AS [Name],

    DEF_Name AS Proces,

    WF_Name AS Workflow,

    DTYPE_Name AS DocType,

    WFD_Signature AS Signature,

    WFD_ID AS ID

FROM [dbo].[WFSecurities] AS Sec

JOIN [dbo].[DicSecurityLevels]

    ON SEC_LevelID = [TypeID]

JOIN [dbo].[V_WFElements]

    ON SEC_WFDID = WFD_ID

WHERE (

        SEC_USERGUID = '{Cos_BPSID}'

        OR '{Cos_BPSID}' IN (

            SELECT u.COS_BpsID

            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 g.COS_BPSID = SEC_USERGUID

        )

      )

Application Privileges

A summary of permissions at the level of the WEBCON application itself — administration, portal designer, metadata access, or application access.

SELECT

    CASE CSC_LevelID

        WHEN 1 THEN 'Admin'

        WHEN 2 THEN 'Portal designer'

        WHEN 3 THEN 'Metadata access'

        WHEN 4 THEN 'Access to Application'

        ELSE 'Unknown'

    END AS [Name],

    APP_Name AS [Application name],

    IIF(CSC_USERGUID = '{Cos_BPSID}', 'User', 'Group') AS [User/Group],

    CSC_UserName as [GroupName]

FROM dbo.WFConfigurationSecurities

JOIN dbo.WFApplications ON CSC_APPID = APP_ID

WHERE (

        CSC_USERGUID = '{Cos_BPSID}'

        OR '{Cos_BPSID}' IN (

            SELECT u.COS_BpsID

            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 g.COS_BPSID = CSC_USERGUID

        )

      )

Appendix: Users in a Group

The reverse perspective — not “what permissions does the user have,” but “who is in this group.” The lookup can be done either by the group’s login (COS_BPSID_Group) or by its display name (COS_DisplayName_Group).

-- By group login:

SELECT u.COS_BpsID

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 g.COS_BPSID = '{COS_BPSID_Group}'



-- By group display name:

SELECT u.COS_BpsID

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 g.COS_DisplayName = '{COS_DisplayName_Group}'

Benefits for Administrators

Centralizing permission information significantly cuts down the time needed to analyze user tickets. The most common use cases for such a solution are:

• security incident analysis,
• access verification during audits,
• diagnosing document visibility issues,
• checking the correctness of group configuration,
• supporting employee onboarding and offboarding.

Summary

The larger the WEBCON installation, the harder permission management becomes. A dedicated audit tool significantly simplifies this process, shortens analysis time, and increases the security of the environment.

If you administer a large-scale WEBCON platform, it’s worth considering building your own permissions audit center that shows a user’s complete access path to the system in one place.