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 o...
Need Help
Anonymous's avatar
Anonymous
Not applicable
8 years ago

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
8 years ago

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

  • Anonymous's avatar
    Anonymous
    Not applicable
    8 years ago

    Hi Dale,

     

    the field generally has restrictions, but for some users it doesn't. Hence I need to make it somehow dynamic, that in case the current user has restrictions, the rule has to be applied, and in case the current user does not have any restrictions (value: *), the rule should not be applied.

     

    Do you see any way how that could be achieved? With an if-clause, or maybe the USERELATIONSHIP() function?

     

    Thanks again,

    Chris

    • v-jiascu-msft's avatar
      v-jiascu-msft
      Microsoft Employee
      8 years ago

      Hi Chris,

       

      We can achieve this by assigning roles to the users. If a user have a role, the access of the user will be restricted accoring to the role. Please refer to /service-admin-rls#manage-security-on-your-model and give it a try.

      Row_level_security_using_if_clause

       

       

      Best Regards,

      Dale

    • v-jiascu-msft's avatar
      v-jiascu-msft
      Microsoft Employee
      8 years ago

      Hi Anonymous,

       

       

      Could you please mark the proper answer as a solution?

       

      Best Regards,
      Dale

      • Anonymous's avatar
        Anonymous
        Not applicable
        8 years ago

        Hi Dale, unfortunately this is not the solution I am looking for, as it would be very hard to administrate afterwards.

        I'll give you the example:

         

        There are two possible restrictions, business area and company. If I need to solve it with roles, I would need to create the following roles:

        - Full access:

        For users who should see everything

        - Restricted access: Company

        For users who should see all business areas (this would handle the star (*) cases), but are limited companies

        - Restricted access: Business area

        For users who should see all companies, but are limited business areas

        - Restricted access: Company and business area

        For users who have restrictions in both company and business area.

         

        The following scenarios will occur with the above solution:

        1) A user will request access. The admin of the dashboard does not know which restrictions this user has, so the admin will need to look up the access rights first and then assign the appropriate role.

        2) The access rights of the user get changed: The role in Power BI will need to be changed also

        3) It might be that even more dimensions are added, where restrictions need to apply on. Then it would get even more complex, as I need to create even more roles (3 restricted dimensions would mean 8 roles).

         

        I am more looking for a solution where I can tell in the role itself, that in case a star (*) value appears, then it should not apply any restriction, else it should apply the restrictions received.