Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
8 years ago

Row level security using if-clause

Hello,

 

I am trying to implement a RLS to limit the security. We are receiving the values to restrict on from a database directly, so in theory I only need to apply a simple RLS where I restrict on the user. However, the thing that makes it a bit more difficult is, that I need to restrict on two dimensions: Company and business area. A user can have access to all companies and all business areas, or access to only a few companies, but all business areas.

The main issue is, that if a user has access to all company codes or business areas, I only receive a star (*) as restriction, but no full list of all possible values. For example: 

restriction example

 

In this example, user Chris should see all business areas, but only the company codes listed. The company code restriction is easy to achieve, as I can simply link the table to my company dimension and apply a bi-directional filter. But how can I handle the star (*) entries?

 

What I have tried is to create a dummy user in my security table that has all business areas and company codes assigned to it. Then I wanted to apply the following logic in a role:

If the table contains a star value, then apply a restriction on my dummy user, else apply a restriction on the current user.

 

The code would look like this:

 

[USER] =
IF(
IF(

CONTAINS(

CALCULATETABLE('Security business area','Security business area'[USER]=USERPRINCIPALNAME())

 ,'Security business area'[Restriction],"*")

,"YES","NO")="NO"

,USERPRINCIPALNAME(),"DUMMY")

 

If I test this formula in a normal measure, it works just as I expect it to work. However, if I use this formula in the role definition, it works for the users that have a restriction (meaning they don't have a star value), but it does not work for the users that have a star value (I don't even get the "DUMMY" as result).

 

Does anyone have any idea why, or has any different solution?

 

11 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable

    Sorry, for some reason it did not upload the image for the restriction example:

  • v-jiascu-msft's avatar
    v-jiascu-msft
    Microsoft Employee

    Hi Anonymous,

     

    The star here means no restrictions. We don't need to set a rule for this situation. We only need to set the rules for those conditions that will filter out some values.

     

    Best Regards,

    Dale

    • Anonymous's avatar
      Anonymous
      Not applicable

      Thanks for your reply.

      I am aware that the star means no restrictions. So if it always was the star value, then it would not be a problem.

      However, in many cases there are restrictions on business area. So how could I tell Power BI that in case there is a star, it should ignore the rule applied, and in case there are restrictions, it should apply the rule?

      • v-jiascu-msft's avatar
        v-jiascu-msft
        Microsoft Employee

        Hi Anonymous,

         

        If it's a star, we just leave that field alone. If it has restrictions, it should have values and we can set it. Please refer to the snapshot below.

        1. If there isn't any restrictions in the table "FactSales", we just leave it be. 

        2. If any tables have restrictions in one roles, we add them like below.

        3. If one table have restrictions on more than one column, we just add them.

        BTW, these work will done by hand, so the Power BI doesn't need to know what the star will be. 

        Row_level_security_with_multiple_roles

         

         

        Best Regards,

        Dale