BRApi check user access to entities

Hi Cheryl,

Here’s an XFBR you can use to get the SQL WHERE clause you need.  When UseWorkflow is set to True, it also filters to only entities assigned to the workflow profile that the user also has access to.  If the user doesn’t have access to any Entities, then the rule returns 1=0 (no entities) and if the user is an admin, it returns 1=1 (all entities).

First it checks what entities the user has access to, then using that list, filters based on workflow.  In the end you get entities for that workflow that the user has access to.

Use it like this: XFBR(EntityAccessWhereClause, GetEntityWhereClause, EntityDim=ReplaceWithEntityDimName, UseWorkflow=True)

​
Imports System
Imports System.Collections.Generic
Imports System.Data
Imports System.Data.Common
Imports System.Globalization
Imports System.IO
Imports System.Linq
Imports Microsoft.VisualBasic
Imports OneStream.Finance.Database
Imports OneStream.Finance.Engine
Imports OneStream.Shared.Common
Imports OneStream.Shared.Database
Imports OneStream.Shared.Engine
Imports OneStream.Shared.Wcf
Imports OneStream.Stage.Database
Imports OneStream.Stage.Engine

'================================================================================
' Dashboard XFBR String rule. Returns a SQL WHERE clause containing 
' the entities the current user may reach. Security sets the list first by testing
' each entity member's read and read-write data groups against the user's groups,
' then optional workflow filtering narrows it to the current profile's entities.
' An administrator reaches every entity, and an empty generates 1 = 0 rather 
' instead of an empty IN list that could cause errors. 
'
' Called from a dashboard as
' XFBR(EntityAccessWhereClause, GetEntityWhereClause, EntityDim=Geography, Column=[Entity], UseWorkflow=True).
'================================================================================
Namespace OneStream.BusinessRule.DashboardStringFunction.EntityAccessWhereClause
    Public Class MainClass
        Public Function Main(si As SessionInfo, globals As BRGlobals, api As Object, args As DashboardStringFunctionArgs) As Object
            Try
                Select Case args.FunctionName
                    Case "GetEntityWhereClause"
                        Return GetEntityWhereClause(si, args)
                End Select

                Return String.Empty

            Catch ex As Exception
                Throw ErrorHandler.LogWrite(si, New XFException(si, ex))
            End Try
        End Function

        ' Reads the XFBR parameters, then hands them to the filter builder.
        Private Function GetEntityWhereClause(si As SessionInfo, args As DashboardStringFunctionArgs) As String
            Dim entityDimensionName As String = args.NameValuePairs.XFGetValue("EntityDim", "")
            Dim entityColumnName As String = args.NameValuePairs.XFGetValue("Column", "[Entity]")
            Dim entityTopMemberName As String = args.NameValuePairs.XFGetValue("Top", "Root")

            Dim isUseWorkflowFiltering As Boolean = False
            Boolean.TryParse(args.NameValuePairs.XFGetValue("UseWorkflow", "False"), isUseWorkflowFiltering)

            If String.IsNullOrWhiteSpace(entityDimensionName) Then Throw New XFException(si, "GetEntityWhereClause requires an EntityDim parameter.", Nothing)

            Return GetEntityAccessFilter(si, entityDimensionName, entityColumnName, isUseWorkflowFiltering, entityTopMemberName)
        End Function

        ' Builds the [Entity] IN (...) clause for the entities the user may reach.
        Private Function GetEntityAccessFilter(si As SessionInfo, entityDimensionName As String, entityColumnName As String, isUseWorkflowFiltering As Boolean, entityTopMemberName As String) As String
            Dim isAdministrator As Boolean = BRApi.Security.Authorization.IsUserInAdminGroup(si)

            ' An unfiltered administrator reaches everything, so impose no restriction.
            If isAdministrator AndAlso Not isUseWorkflowFiltering Then Return "1 = 1"

            ' Expand the top to itself and all descendants. The name is trusted metadata,
            ' bracketed so names with spaces resolve.
            Dim entityMemberFilter As String = $"E#[{entityTopMemberName}].DescendantsInclusive"
            Dim entityMembers As List(Of MemberInfo) = BRApi.Finance.Metadata.GetMembersUsingFilter(si, entityDimensionName, entityMemberFilter, True, Nothing, Nothing)

            ' Security pass. Admins keep every member; others keep the members whose own
            ' data groups they belong to.
            Dim accessibleEntities As List(Of String)
            If isAdministrator Then
                accessibleEntities = entityMembers.Select(Function(candidate) candidate.Member.Name).Distinct().ToList()
            Else
                Dim userGroupIds As List(Of Guid) = BRApi.Security.Admin.GetUser(si, si.UserName).ParentGroups.Keys.ToList()
                accessibleEntities = entityMembers.Where(Function(candidate) hasGroupAccess(candidate, userGroupIds)).Select(Function(candidate) candidate.Member.Name).Distinct().ToList()
            End If

            ' Workflow pass, after security, keeping only entities in the current profile.
            If isUseWorkflowFiltering Then
                Dim workflowEntities As List(Of String) = GetCurrentWorkflowEntityNames(si)
                accessibleEntities = accessibleEntities.Where(Function(entityName) workflowEntities.Contains(entityName, StringComparer.OrdinalIgnoreCase)).ToList()
            End If

            ' An empty IN list is invalid SQL, so close the register instead.
            If accessibleEntities.Count = 0 Then Return "1 = 0"

            ' Names are trusted metadata; the helper still quotes and escapes each value.
            Return SqlStringHelper.CreateInClause(entityColumnName, accessibleEntities, True, True)
        End Function

        ' True when the user is in any of the member's read or read-write data groups.
        ' A member with no group on any slot is open to everyone.
        Private Function hasGroupAccess(entityMember As MemberInfo, userGroupIds As List(Of Guid)) As Boolean
            Dim readGroup As Guid = entityMember.Member.ReadDataGroupUniqueID
            Dim readGroupSecondary As Guid = entityMember.Member.ReadDataGroupUniqueID2
            Dim readWriteGroup As Guid = entityMember.Member.ReadWriteDataGroupUniqueID
            Dim readWriteGroupSecondary As Guid = entityMember.Member.ReadWriteDataGroupUniqueID2

            Dim hasAnyGroupAssigned As Boolean = readGroup <> Guid.Empty OrElse readGroupSecondary <> Guid.Empty OrElse readWriteGroup <> Guid.Empty OrElse readWriteGroupSecondary <> Guid.Empty
            If Not hasAnyGroupAssigned Then Return True

            Return userGroupIds.Contains(readGroup) OrElse userGroupIds.Contains(readGroupSecondary) OrElse userGroupIds.Contains(readWriteGroup) OrElse userGroupIds.Contains(readWriteGroupSecondary)
        End Function

        ' Entity names assigned to the user's current workflow profile.
        Private Function GetCurrentWorkflowEntityNames(si As SessionInfo) As List(Of String)
            Dim workflowProfile As WorkflowProfileInfo = BRApi.Workflow.Metadata.GetProfile(si, si.WorkflowClusterPk)
            Dim workflowEntities As List(Of WorkflowProfileEntityInfo) = BRApi.Workflow.Metadata.GetProfileEntities(si, workflowProfile.ProfileKey)

            Return workflowEntities.Select(Function(assignment) assignment.EntityName).Distinct().ToList()
        End Function
    End Class
End Namespace

​