Forum Discussion
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 :
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 :
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.
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.
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 - RADACADReza also has a great book dedicated specifically to RLS:
https://www.amazon.com/Row-Level-Security-Power-BI-different-ebook/dp/B082SFR2J4
4 Replies
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.
