Dynamic RLS and using DirectQuery

[NielsBakker]

@speedramps again thank you very much for your help. Really appreciate your time and help for trying to solve this issue. I hope I've explained my issue as clear as possible. 

Goal

Set up flexible (dynamic) Row-Level Security (RLS) in Power BI using a UserSecurity table that filters a FactTable.
When a user logs in, the UserSecurity table should filter to that user, which in turn should filter the FactTable to only show data the user is allowed to see.

Requirements

• All tables in the data model use DirectQuery.
• The UserSecurity table must support wildcards to indicate full access, in this case I use 0. The reaon for this is that I want to avoid that the table will get extremely large.
  • Example: If a user has access to all ResourceGroups within a Subscription, the ResourceGroup field should be 0.

• A key use case to support is (please see attached Excel file (UserSecurity) for the full overview:
  
  • User 16 has access to:
    • All ResourceGroups in Subscription 17
    • Only ResourceGroup 15348  in Subscription 25

Limitations

• Because I’m using DirectQuery, I cannot use functions like CALCULATE or COUNTROWS in Manage Roles for RLS (they are disabled).
• If a relationship between the tables is needed, a many-to-many relationship is needed between UserSecurity and the FactTable because:
  • Multiple users in UserSecurity
  • Each user can have multiple different access rules (some full access via wildcard, some partial)
  • However, many-to-many relationships are problematic with DirectQuery

What I've Tried

• It works perfectly in Import Mode (use Test 1 as Role), but I must use DirectQuery. Please see attached PBI how it should work.
• I’m stuck translating the logic into a DAX approach that works in DirectQuery mode with these constraints.

Ask
How can I implement this kind of dynamic RLS with wildcard support in DirectQuery mode?

I've attached 2 Excel files and a PowerBI file that can be used. Sorry, I don't know how to share a DirectQuery model because of privacy concerns. I hope this Excel files gives enough context. 

Link to download files: https://we.tl/t-PONAaflP8Q 

Many thanks again, and appreciate the help! 

Kind regards,

Niels

[speedramps]

Hi again @NielsBakker 

This users has posted some good videos about RLS with Direct Query that you might find interesting

• https://www.youtube.com/watch?v=WowP5vOgeqI 
• https://radacad.com/directquery-connection-in-power-bi-how-does-it-work-limitations-and-advantages/

[speedramps]

HI @NielsBakker 

If you MUST use direct query then consider implementing the row level security inside the source system rather than Power BI.

For example if the source is SQL Server, MY SQL or Oracle then implement the security in there rather than Power BI.

The data base administator will help you to that, or you can learn how via the SQL Server, MY SQL or Oracle comuinity forums.

Alternatively consider Power BI  Import Mode, a composite model or Fabric Direct Lake so that you can leverage all DAX comands inside Power BI RLS (Row Level Security).

Sorry, this is not the answer you want, but one of the reasons that Fabric Direct Lake is becoming so populare is that it is ideal for semantic models with RLS regardless of the data source.

Learn about Fabric Direct Lake here

https://learn.microsoft.com/en-us/fabric/fundamentals/direct-lake-overview

[speedramps]

Hi again @NielsBakker 

This example shows how to implement RLS so a person can be given access to just some or all stores in a country.

The Security table stipulates who has access to which stores in which countries.

The * asterisk denotes that a person has access to all stores in the designated country unless then is an "X" in the block column.

Note that several countries may have identical store names but they are separate stores.

Use the View As function to test Mark, Peter, Sue, Tim, Katy and Jane have the correct restricted access to the Fact table secrets.

I know that you require Direct Query and I will try answer that later. 
This solution works for Import Mode, Fabrix Driect Lake and potentially a Composite Mode.

You will need to rename the tables and columns names for your Resources Groups and Subscriptions. 
You will also need to have the * and X  to your  I and 0.

 Click here to download PBIX from OneDrive 

Note that Security and Fact table have a Many to Many relation with Both cross filter direction.

This will generate a red triangle warning, which you can ignore because it is ok in this scenario.

In Manage Roles the Securiry table has a RLS (Row Lecve; Security) Filter for the login username 

Whereas the Fact table uses DAX to determin the RLS Filter

Here is the DAX RLS filter logic

// create a temp table allowed stores for the User and Country
VAR allow = 
   CALCULATETABLE(
    VALUES(Security[Store]),
    Security[User] = USERNAME(),
    Security[Block] <> "X"
    )

// create a temp table prevented stores for the User and Country
VAR prevent = 
   CALCULATETABLE(
    VALUES(Security[Store]),
    Security[User] = USERNAME(),
    Security[Block] = "X"
    )

RETURN
// Allow the user access if the country store or * is in the allow temp table.
// But prevent acceess if that user country store is blocked.

(
    [Store] IN allow
    ||
    "*" IN allow
)

&& NOT [Store] IN prevent

Test by using View as Jane who has access to

• All store in France
• Just stores 1 and 4 in the UK
• And all stores in USA except 1 and 3 

Works perfectly.

I like how how this solution is easily to understand, develop, test and maintain.

So you can easily make changes to the security config table as when staff need access adding or removing.

[FBergamaschi]

Hi,

limitations are due to the need to translate in SQL complex DAX statements.

the CALCULATE and COUNTROWS calculations you need to perform are working on facts/dimensions/bridges or on the security table? I am asking because you might consider (maybe) to import only the security table if the CALCULATE and COUNTROWS calculations you need to perform and on the security table

Othwerwise either you import (not an option, if I understand correclty) or you write an old style DAX, that will be painful though

If this helped, please consider giving kudos and mark as a solution

@me in replies or I'll lose your thread

consider voting this Power BI idea

Francesco Bergamaschi

MBA, M.Eng, M.Econ, Professor of BI

[NielsBakker]

@FBergamaschi thanks for your response! 

I've tried to import only the security table, and use Direct Query for the FactTable. But in Manage Roles, the DAX is still referencing to the DirectQuery table and therefore, is not allowed to use unfortunately. 

But thanks for thinking with me!