Forum Discussion

eirika's avatar
eirika
Frequent Visitor
2 years ago
Solved

RLS security

I have 3 tables: access - this table holds userid and access_id. each user has one row for each access_id he has access to. access_id is the foreign key for the tables t1 and t2 t1 - this table ho...
Need Help
  • SamWiseOwl's avatar
    SamWiseOwl
    2 years ago

    eirika 

    Great I've added some fake email addresses and used UserPrincipalName to apply the filter:
    testrls - sam.pbix

     

    You can impersonate an email by entering it into Other user and ticking the role you want to test:

     

     

SamWiseOwl's avatar
SamWiseOwl
Super User
2 years ago

Hi eirika  Can you share a sample file? It works on my test file but the stucture could be different.

  • SamWiseOwl's avatar
    SamWiseOwl
    Super User
    2 years ago

    Hi eirika 

    How will the filtering work on publish?

    Are you going to add emails into the Access table next to their UserId?

    • eirika's avatar
      eirika
      Frequent Visitor
      2 years ago

      No, I'm using the embedded solution.

      So when the code call the EffectiveIdentity method from the application to open the PowerBI report we pass the role "User" and the value of "userId" for the logged in user. This give us possibility to use the username() function in RLS to get the userId value.

       

      So the filter on the useraccess table in my example is hard coded to 1, this number will be replaced with username() on deploy.

       

      the access table is only for limiting the rows the user has access to in t1 and t2. There will bo no extra columns.

      • eirika's avatar
        eirika
        Frequent Visitor
        2 years ago

         

        I did get it to work now by using the filter below on t1 and t2 (replaces username() with hard coded value 1). But is this a nice and fast solution?

         

        t1[access_id] IN CALCULATETABLE(DISTINCT(access[access_id]), access[userid] = USERNAME())