Security Report - Dashboard for Admin- Access

Question

Good day to you.
Currently, we are working on a Dashboard for User Access. We have successfully implemented the logic to retrieve Security Group Unique IDs along with their corresponding Security Group details.
As part of this dashboard, we now need to retrieve Workflow Profile–level details, specifically:

Entity assigned to the Workflow Profile
Access Group
Maintenance Group
Workflow Execution Group
Certification / Signoff Group

So far, we have been able to retrieve details for points (1), (2), and (3). However, we are unable to fetch the Workflow Execution Group and Certification / Signoff Group details using either Business Rules or SQL queries.
Could you please help us by suggesting:

The appropriate Business Rule method/API, or
A supported SQL approach,

to retrieve these remaining Workflow Profile security details?
Your guidance on the recommended approach would be greatly appreciated.

MarcusH · Accepted Answer

I get the security groups into a dictionary keyed by the security group id. Then I read the workflow profile tables to get the security group id information and look up the name of the security group from the dictionary. This is the SQL Query I use to get the workflow security groups:

thisSQLClause = "Select CASE WorkflowProfileAttributes.ScenarioTypeID "
thisSQLClause &= "when '-1' Then '(Default)' "
thisSQLClause &= "when '0' Then 'Actual' "
thisSQLClause &= "when '1' Then 'Budget' "
thisSQLClause &= "when '2' Then 'Flash' "
thisSQLClause &= "when '3' Then 'Forecast' "
thisSQLClause &= "when '4' Then 'FXModel' "
thisSQLClause &= "when '5' Then 'History' "
thisSQLClause &= "when '6' Then 'Model' "
thisSQLClause &= "when '7' Then 'Plan' "
thisSQLClause &= "when '8' Then 'Tax' "
thisSQLClause &= "when '9' Then 'Variance' "
thisSQLClause &= "when '10' Then 'Administration' "
thisSQLClause &= "when '11' Then 'Control' "
thisSQLClause &= "when '12' Then 'LongTerm' "
thisSQLClause &= "when '13' Then 'Operational' "
thisSQLClause &= "when '14' Then 'Sustainability' "
thisSQLClause &= "when '15' Then 'Target' "
thisSQLClause &= "when '101' Then 'ScenarioType1' "
thisSQLClause &= "when '102' Then 'ScenarioType2' "
thisSQLClause &= "when '103' Then 'ScenarioType3' "
thisSQLClause &= "when '104' Then 'ScenarioType4' "
thisSQLClause &= "when '105' Then 'ScenarioType5' "
thisSQLClause &= "when '106' Then 'ScenarioType6' "
thisSQLClause &= "when '107' Then 'ScenarioType7' "
thisSQLClause &= "when '108' Then 'ScenarioType8' END As ScenarioType, "
thisSQLClause &= "CASE AttributeIndex WHEN 1250 THEN 'Workflow Execution Group' WHEN 16100 THEN 'Certification SignOff Group' WHEN 6100 THEN 'Journal Process Group' WHEN 6200 THEN 'Journal Approval Group' WHEN 6300 THEN 'Journal Post Group' END AS Attribute,  "
thisSQLClause &= "WorkflowProfileAttributes.ProfileAttributeValue, WorkflowProfileHierarchy.ProfileName "
thisSQLClause &= "From   WorkflowProfileAttributes INNER Join "
thisSQLClause &= "WorkflowProfileHierarchy On WorkflowProfileAttributes.ProfileKey = WorkflowProfileHierarchy.ProfileKey "
thisSQLClause &= "Where (WorkflowProfileAttributes.AttributeIndex In (1250, 16100, 6100, 6200, 6300)) "

vasantharaidu · Answer

WITH ProfileEntity AS (
    SELECT
        h.CubeName,
        h.ProfileKey,
        h.ProfileName,
        e.EntityMemberID
    FROM WorkflowProfileHierarchy h
    JOIN WorkflowProfileEntities e
        ON h.ProfileKey = e.WorkflowProfileKey
    WHERE h.ProfileType = 3
      AND e.EntityMemberID = (
            SELECT MemberID
            FROM Member
            WHERE Name = '|!SelectedEntity!|' ' This is Parameter to call entity.
              AND DimTypeID = 0
        )

    UNION

    SELECT
        h.CubeName,
        h.ProfileKey,
        h.ProfileName,
        e.EntityMemberID
    FROM WorkflowProfileHierarchy h
    JOIN WorkflowProfileEntities e
        ON h.ParentProfileKey = e.WorkflowProfileKey
    WHERE h.ProfileType = 3
      AND e.EntityMemberID = (
            SELECT MemberID
            FROM Member
            WHERE Name = '1100'
              AND DimTypeID = 0
        )
),

AttributeData AS (
    SELECT
        pe.CubeName,
        pe.ProfileName,
        m.Name AS EntityName,
        WPA.AttributeIndex,
        CASE TRY_CONVERT(uniqueidentifier, WPA.ProfileAttributeValue)
            WHEN CONVERT(uniqueidentifier,'e31054d8-83bf-4f79-b563-0e450342de9e') THEN 'Everyone'
            WHEN CONVERT(uniqueidentifier,'b26b7926-a1ec-4f32-a402-020bc55cc528') THEN 'Nobody'
            ELSE ISNULL(sg.Name,'Unknown')
        END AS SecurityGroup
    FROM ProfileEntity pe
    JOIN WorkflowProfileAttributes WPA
        ON pe.ProfileKey = WPA.ProfileKey
    JOIN Member m
        ON pe.EntityMemberID = m.MemberID
       AND m.DimTypeID = 0
    LEFT JOIN SecGroup sg
        ON TRY_CONVERT(uniqueidentifier, WPA.ProfileAttributeValue) = sg.UniqueID
    WHERE WPA.AttributeIndex IN (1250,16100,6100,6200,6300)
      AND pe.ProfileName NOT LIKE '%.%'
),

PivotAttributes AS (
    SELECT
        CubeName,
        ProfileName,
        EntityName,
        [1250]  AS WorkflowExecutionGroup,
        [16100] AS CertificationSignOffGroup,
        [6100]  AS JournalProcessGroup,
        [6200]  AS JournalApprovalGroup,
        [6300]  AS JournalPostGroup
    FROM AttributeData
    PIVOT (
        MAX(SecurityGroup)
        FOR AttributeIndex IN ([1250],[16100],[6100],[6200],[6300])
    ) p
)

SELECT
    ROW_NUMBER() OVER (ORDER BY p.EntityName, p.ProfileName) AS RowID,
    p.CubeName,
    p.EntityName,
    p.ProfileName AS WorkflowProfileName,

    CASE m.ReadDataGroupUniqueID
        WHEN CONVERT(uniqueidentifier,'e31054d8-83bf-4f79-b563-0e450342de9e') THEN 'Everyone'
        WHEN CONVERT(uniqueidentifier,'b26b7926-a1ec-4f32-a402-020bc55cc528') THEN 'Nobody'
        ELSE ISNULL(sgR1.Name,'Unknown')
    END AS ReadGroup,

    CASE m.ReadDataGroupUniqueID2
        WHEN CONVERT(uniqueidentifier,'e31054d8-83bf-4f79-b563-0e450342de9e') THEN 'Everyone'
        WHEN CONVERT(uniqueidentifier,'b26b7926-a1ec-4f32-a402-020bc55cc528') THEN 'Nobody'
        ELSE ISNULL(sgR2.Name,'Unknown')
    END AS ReadGroup2,

    CASE m.ReadWriteDataGroupUniqueID
        WHEN CONVERT(uniqueidentifier,'e31054d8-83bf-4f79-b563-0e450342de9e') THEN 'Everyone'
        WHEN CONVERT(uniqueidentifier,'b26b7926-a1ec-4f32-a402-020bc55cc528') THEN 'Nobody'
        ELSE ISNULL(sgW1.Name,'Unknown')
    END AS WriteGroup,

    CASE m.ReadWriteDataGroupUniqueID2
        WHEN CONVERT(uniqueidentifier,'e31054d8-83bf-4f79-b563-0e450342de9e') THEN 'Everyone'
        WHEN CONVERT(uniqueidentifier,'b26b7926-a1ec-4f32-a402-020bc55cc528') THEN 'Nobody'
        ELSE ISNULL(sgW2.Name,'Unknown')
    END AS WriteGroup2,

    p.WorkflowExecutionGroup,
    p.CertificationSignOffGroup,
    p.JournalProcessGroup,
    p.JournalApprovalGroup,
    p.JournalPostGroup

FROM PivotAttributes p
JOIN ProfileEntity pe
    ON p.ProfileName = pe.ProfileName
JOIN Member m
    ON pe.EntityMemberID = m.MemberID
   AND m.DimTypeID = 0
LEFT JOIN SecGroup sgR1 ON m.ReadDataGroupUniqueID  = sgR1.UniqueID
LEFT JOIN SecGroup sgR2 ON m.ReadDataGroupUniqueID2 = sgR2.UniqueID
LEFT JOIN SecGroup sgW1 ON m.ReadWriteDataGroupUniqueID  = sgW1.UniqueID
LEFT JOIN SecGroup sgW2 ON m.ReadWriteDataGroupUniqueID2 = sgW2.UniqueID
ORDER BY
    p.EntityName,
    p.ProfileName;

Hi MarcusH,

Thanks for the code, with the above code i can be able to build complete query to get access details based on entity. this code will provide entity access group details and workflow access group details

Sign up

Login with SSO

Login to the community

Login with SSO

Scanning file for viruses.

This file cannot be downloaded