Configuring “all” access with dynamic row-level security

Row level security

Row-level security is an important part of many data models. In this article, I am showing a way to selectively grant “all” access with dynamic row-level security and only one role. For example, we can let a CEO see the whole data model, but a department head will be able to see their department’s data only. This article presents a more flexible approach in cases where using an organizational hierarchy and PATHCONTAINS would be inappropriate.

Basic scenario

Imagine we have the following fact table called Headcount:

Region Headcount
Central 44
East 27
West 17

We have four users of our report: one manager for each region, and a CEO.

Each regional manager should be able to see their region only, and the CEO should be able to see all regions.

How can this be achieved with dynamic row-level security? There are several ways:

First, we can create a role for regional managers, and a separate role for the CEO. I saw this solution suggested in a Power BI Community forum thread. In my opinion, this approach is not exactly dynamic, as we will have to assign the CEO a separate role.

Second, we can solve this problem using an organizational hierarchy and the PATHCONTAINS function in DAX. While it would work in this particular case, it may not work in case where we need to specify custom access.

Third, we can create the following security table:

Name Region
Regional Manager Central Central
Regional Manager East East
Regional Manager West West
CEO Central
CEO East
CEO West

We don’t need to relate the tables.

Then we can create a role with the following DAX code for Headcount table:

Headcount[Region]
    IN CALCULATETABLE (
        VALUES ( UserSecurity[Region] ),
        UserSecurity[Name] = USERNAME ()
    )

This does the trick, but in more complex situations, where there are many regions and hierarchies are more complex, this would bloat the security table, and maintaining it could be arduous.

Here’s an alternative approach. We can create the following security table:

Name Region
Regional Manager Central Central
Regional Manager East East
Regional Manager West West
CEO ALL

Note how the security table is more concise and easier to maintain. To make it work, all we need to do is change the DAX code for our security role:

VAR Regions =
    CALCULATETABLE (
        VALUES ( UserSecurity[Region] ),
        UserSecurity[Name] = USERNAME ()
    )
RETURN
    OR (
        "ALL" IN Regions,
        Headcount[Region] IN Regions
    )

The same approach can be used in more complex scenarios as well.

More complex scenario

Now let’s say our Headcount table also contains departments:

Department Region Headcount
Finance West 1
Finance East 1
Finance Central 2
Marketing West 3
Marketing East 4
Marketing Central 8
Sales West 13
Sales East 21
Sales Central 34

We have the following users of our report:

  • One regional manager for each region
  • Finance Director
  • Sales & Marketing Director
  • CEO

Here are the business rules concerning security:

  • Each regional manager should see all departments within their region only
  • Each director should see all regions within their departments only
  • The CEO should be able to see everything

Let’s assume we have the following security table:

Name Department Region
Regional Manager East ALL East
Regional Manager West ALL West
Regional Manager Central ALL Central
Sales & Marketing Director Sales ALL
Sales & Marketing Director Marketing ALL
Finance Director Finance ALL
CEO ALL ALL

You can appreciate the ease of this approach by noting that a security table without ALL would contain 27 rows instead of 7, even in this relatively simple case.

Here is the DAX code that should be used as table filter for Headcount:

VAR Regions =
    CALCULATETABLE (
        VALUES ( UserSecurity[Region] ),
        UserSecurity[Name] = USERNAME ()
    )
VAR Departments =
    CALCULATETABLE (
        VALUES ( UserSecurity[Department] ),
        UserSecurity[Name] = USERNAME ()
    )
RETURN
    AND (
        OR (
            "ALL" IN Departments,
            Headcount[Department] IN Departments
        ),
        OR (
            "ALL" IN Regions,
            Headcount[Region] IN Regions
        )
    )

This approach can work on dimensions as well. Let’s say you now have the following data model:

Headcount has one-to-many relationships with Department and Region. UserSecurity is not related to any table.

Note that UserSecurity is still not related to any table. To make row-level security it work, you just need to split the filters between two tables:

-- Department table filter
VAR Departments =
    CALCULATETABLE (
        VALUES ( UserSecurity[Department] ),
        UserSecurity[Name] = USERNAME ()
    )
RETURN
    OR (
        "ALL" IN Departments,
        Department[Department] IN Departments
    )

-- Region table filter
VAR Regions =
    CALCULATETABLE (
        VALUES ( UserSecurity[Region] ),
        UserSecurity[Name] = USERNAME ()
    )
RETURN
    OR (
        "ALL" IN Regions,
        Region[Region] IN Regions
    )

If you now view the report as Sales & Marketing Director, for example, you will see only Sales and Marketing:

We see all regions but only Sales and Marketing departments

Conclusion

The approach outlined above can be useful when the security table is maintained manually. Writing “all” instead of listing all possible values not only saves time, disk space and RAM, but also future-proofs the solution when new values are introduced. The latter effect, however, must be carefully considered in case it is undesirable.

Download sample files: SecurityAll.zip

  • Hi Daniil
    I’m really interested in this solution.
    With the line UserSecurity[Name] = USERNAME () you will capture the user’s email address so how are you then using that to filter the UserSecurity[Name] column?
    Cheers

    Wyn

    • Hi, Wyn. Thanks for your comment.

      This article presents a simplistic data model for educational purposes.

      In my opinion, the logic would have been a bit more difficult to grasp with email addresses instead of job titles 🙂

      In a real security table, you will likely have a column with email addresses, so you will be able to filter it with either USERNAME or USERPRINCIPALNAME.

  • Hi Daniil,
    that’s a great solution for the ALL filter. Thanks for sharing!

    Nicky

  • Hi Daniil,

    Got this working so thanks for the heads up (initially got stuck before I realised I hadn’t added the email addresses to the security role in the Power BI service).

    In your sample file under Manage Roles you have a FALSE against UserSecurity. What is the purpose of this?

    Cheers

    Wyn

    • Glad to hear it, Wyn!

      Because UserSecurity is a technical table, it is best to hide its content from users completely. Simply hiding the table from client tools does not prevent a user from querying the table. However, if you specify FALSE as a security table filter, a user will not be able to get any data from the table.