Multiple role membership in Value Level Security

Multiple role membership in Value Level Security

For a few years now, we have worked with the concept of Value-level security (VLS). In short, VLS allows you to secure the visibility of not rows, as with Row-level security, or attributes, as with Column-level security, but with specific values in specific rows. For instance, you could hide the pay level attribute for any employee with a pay level equal to or higher than the current user’s.

I’m not explaining how to implement VLS here; in our 2022 Extreme DAX book, a chapter is dedicated to security including VLS and some other VLS-related concepts. We have presented VLS at several conferences as well.

The nice thing about sharing knowledge is that sometimes, people apply it and come back with specific issues and questions. This leads to new solutions and further polishing of existing solutions. This post is about such an issue.

In VLS, we work with private attributes that on a row level are either visible (positive cases) or replaced by blank values (negative cases). This is controlled by specific DAX security expressions consisting of a positive and a negative clause. The idea is that any user will always have access to exactly one of the two cases through a row-level security filter. This way, you can control which private attributes are visible to a specific user:

Table  Description automatically generated

However, when you have multiple security roles defined which implement different security policies, a user may have access to both the positive and the negative case for a specific data row. This leads to duplicated rows in the report like in the example below.

Table  Description automatically generated

Here, the report user is member of two roles; one gives access to the negative case for Alethea Allen, while the other gives access to the positive case. In fact, the second role gives access to all positive cases!

It is not possible to solve this issue as part of the security roles themselves, as no role has any knowledge about other roles. This means that you cannot write security filters like ‘if the positive row is visible to this users through another security role, hide the negative row’. But fortunately, we do not necessarily have to change all DAX measures.

The way to solve this, is to create a specific DAX measure to be used as a filter on each visual that uses private attributes. The problem is that a user gets to see both the blank value and the proper value of a private attribute; it then makes sense to filter away the blank value. Here is the formula:

ShowPrivate =

VAR ThisPrivateValue = MAX(‘Employee (private)'[Private])

VAR OverallPrivateValue =

    CALCULATE(

        MAX(‘Employee (private)'[Private]),

        ALL(‘Employee (private)’),

        VALUES(‘Employee (private)'[EmpNr])

    )

RETURN

IF(ThisPrivateValue = OverallPrivateValue, 1, 0)

 

This measure assumes that the context contains exactly one Employee (private) row. The variable ThisPrivateValue retrieves the Private attribute of this row (positive or negative). The OverallPrivateValue variable determines the largest Private attribute for this employee that is visible to the current report user. Note that this value is 1 for the positive case, and 0 for the negative case.

Now, if ThisPrivateValue equals 0 (so we’re looking at the negative case) but the positive case may be accessed as well, the measure returns 0. In other cases, it returns 1. Note that the situation where ThisPrivateValue = 1 and OverallPrivateValue = 0 cannot happen. We can now use the filter ShowPrivate = 1 on the visual to effectively hide negative cases for which the positive case is visible as well. The result is below.

Table  Description automatically generated

I have to admit here that this does not solve all issues. A filter controls the rows of data that are shown in the table. This means that if you have a visual with, say, sales result by pay level (assuming that is a private attribute), the result for the blank pay level would not be correct. This is a topic for another blog post.