cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
alexa_0028
Resolver II
Resolver II

Row level security to hide sensitive data in facts table

Hi All,

I am facing an issue while implementing RLS to protect sensitive data in my facts table.
Below is the diagram of my data model :

alexa_0028_1-1628719943095.png

 

Currently, I am able to protect my measure sales amount using DAX at service level. As a user with omit conditions cannot see the value.
M_Sales_Amount = If(COUNTROWS(FILTER(omit_country, omit_country [measure_group]=“Sale" && omit_country [mail]=USERPRINCIPALNAME()))>0 || COUNTROWS(FILTER(omit_product, omit_product[measure_group]=“Price" && omit_country [mail]=USERPRINCIPALNAME()))>0 ,Blank(),Sum(sales_amount))

But, there is problem when the end user opens the model in pbi desktop, they can see the column sales_amount which makes up the measure even if it is hidden and this breaches security.

So , I updated my roles and added additional filters of the M_table like below :

alexa_0028_2-1628720025038.png

This works sometimes, but if there is scenarios when a user can be omit_country and omit_product both but with same measure_group that doesn't work.
Can someone please help me here how can I achieve this?I am really stuck here and would appreciate some help please.





 

2 ACCEPTED SOLUTIONS

If your users are editors or contributors then they will be able to access it. There is no column level security in Power BI.

I see you are relying on many-to-many relationships and this could help us with an indirect solution depending on your data size.

If your M_table is not that big in size then you may duplicate it in Power Query after removing the values from your sales amount... make it zeros and add a new column lets say SecurityApplied=1.

Add the same column to your original table with 0 value (SecurityApplied=0) then append the new table to the original one.

In your security roles you have to filter the appended table for all users to have either SecurityApplied either 1 or 0 to avoid duplicating other values for users with higher privileges.

 

m_refaei_1-1628761327337.png

 

 

 

View solution in original post

I agree with @Mohammad_Refaei here: RLS and OLS is meant to be created in PBI Desktop (well, OLS needs Tabular Editor, but it's doable) and applied in PBI Service. WIth OLS it is now possible to hide columns. Second, I don't see a need to implement RLS at the measure level - generally you want to keep your measures clean. I would recommend starting with the link below and adjusting your approach:
Row Level Security Configuration in Power BI Desktop - RADACAD

Reza also has a great book dedicated specifically to RLS:
https://www.amazon.com/Row-Level-Security-Power-BI-different-ebook/dp/B082SFR2J4

View solution in original post

4 REPLIES 4
Mohammad_Refaei
Solution Specialist
Solution Specialist

Did you try hiding the column in this table? Users can rely only on the measure.

Probably this is the best option since you don't want to filter the M_table.

Hi @Mohammad_Refaei ,
Hiding the column in the table will not hide it completely from the end user. As they can still use the hidden columns to create measure and see sensitive data.

If your users are editors or contributors then they will be able to access it. There is no column level security in Power BI.

I see you are relying on many-to-many relationships and this could help us with an indirect solution depending on your data size.

If your M_table is not that big in size then you may duplicate it in Power Query after removing the values from your sales amount... make it zeros and add a new column lets say SecurityApplied=1.

Add the same column to your original table with 0 value (SecurityApplied=0) then append the new table to the original one.

In your security roles you have to filter the appended table for all users to have either SecurityApplied either 1 or 0 to avoid duplicating other values for users with higher privileges.

 

m_refaei_1-1628761327337.png

 

 

 

I agree with @Mohammad_Refaei here: RLS and OLS is meant to be created in PBI Desktop (well, OLS needs Tabular Editor, but it's doable) and applied in PBI Service. WIth OLS it is now possible to hide columns. Second, I don't see a need to implement RLS at the measure level - generally you want to keep your measures clean. I would recommend starting with the link below and adjusting your approach:
Row Level Security Configuration in Power BI Desktop - RADACAD

Reza also has a great book dedicated specifically to RLS:
https://www.amazon.com/Row-Level-Security-Power-BI-different-ebook/dp/B082SFR2J4

Helpful resources

Announcements
Carousel_PBI_Wave1

2023 Release Wave 1 Plans

Power BI release plans for 2023 release wave 1 describes all new features releasing from April 2023 through September 2023.

Power BI Dev Camp Session 30

Ted's Dev Camp - January 26, 2023

This session will provide guidance and teach campers the skills required to build Power BI reports that support multiple languages.

Power BI Summit Carousel 2

Global Power BI Training

Make sure you register today for the Power BI Summit 2023. Don't miss all of the great sessions and speakers!

Thank you 2022 Review

2022 Monthly Feature Releases

We had a great 2022 with a ton of feature releases to help you drive a data culture.