Row level security using if-clause

[CAG]

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: 

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?

[v-jiascu-msft]

Hi @CAG,

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

[CAG]

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]

Hi @CAG,

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. 

Best Regards,

Dale

[CAG]

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]

Hi @CAG,

Could you please mark the proper answer as a solution?

Best Regards,
Dale

[CAG]

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.

[Anonymous]

Hello, 
I encountered the same scenario. Did you resolve your issue? 

How to create a role with DAX. for example if you wanted to have `Restricted access: Company`? 
In my case  I would like to get the `company` of current user (by matching the id)  and filter rows by this company. 

[CAG]

Hi @Anonymous ,

yes, I have solved my issue, but I solved it differently, not with a DAX statement in the role setup.

I have created calculated tables, that crossjoin the users that have a * (access to everything) with the respective dimension. The DAX statement you'll need is CROSSJOIN().

For example: User A has access to all companies. I therefore receive one line from my security table like the following:

User: Mark

Dimension: Companies

Restriction: *

I am then crossjoining this line with the company dimension, so I am getting a new table that delivers me the user Mark and all companies that exist in that dimension.

This table you can then use to place the RLS restriction on.

Hope this helps, good luck!

[ncbshiva]

Hi,

Can you please share me the DAX code ?

Regards

Shiva

[v-jiascu-msft]

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.

Best Regards,

Dale

[CAG]

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