Row Level Security using any source data base / file

rajiv_tarafdar — Wed, 09 May 2018 04:24:48 GMT

Hi Guys,

After scanning through the forums to find a way to implement Row Level Security using Power BI Report Server and not finding anything positive, I decided to build my own. I would like to share this with you all so others can overcome this challenge.

Requirements

Implement Row Level Security on a database based on A User belonging to a certain Department or Division or any other field that you want to filter the data based on
Use Microsoft AD Login for authentication
Reports to contain all data by default and only filter on user view.

Assumptions

Users are only browsers on the Report Server and will not be editing the reports / dashboards
The User to Department mapping will be sotred in at able or file

Design

Create a Tables USER_ACCESS with the following
UID, Name, Role Type and Department / Division (or similar column)
Populate the data in the table assigning users to a Department / Division
Load this table in the Power BI Data Set and Join to the other table where Department / Division exists i.e. user_access.department = <fact_name>.department or <dimension_name>.department
Cross Filter Direction = Both
Apply Security Filter in both direction = Yes
If you want to filter further objects based on this column join it to the USER_ACCESS table or the Target table you joined to and ensure the Cross Filter Direction is Single.
Reload the data and ensure the filter works
On the Table USER_ACCESS create ameasure UserID_Match = IF(CONTAINS(USER_ACCESS,USER_ACCESS[FULL_USER_ID], USERNAME()), 1 , 0)
When a user logs in their record will automatically return a value one.
Now add this Filter to the Visual Level Filter with a value set to 0.
Add the data along with the Department / Divion Columns on the Graph or Cross Table
The data should automatically filter to the user using the Report on the Power BI Desktop and Power BI Report Server.

Disclaimer

This solution may not work for all depdneing on how your data joins. We have tested this on a Star Schema with 1 Fact and 9 Dimensions and it worked for all the Dimensions and Fact records. if you need help on this solution, please put your comments in below.

Thanks to Greg King for helping me desig and test this.

Good Luck.

Re: Row Level Security using any source data base / file

mohammadhijazi — Thu, 08 Nov 2018 15:28:53 GMT

Thank you @rajiv_tarafdar for sharing your solution, I have a question are you able to apply the same filter by current user to slicer?

Re: Row Level Security using any source data base / file

healthEteam — Fri, 09 Nov 2018 01:08:41 GMT

I believe that Row Level Security is a feature that is expected to be added in the January update (it was slated for November update but just now saw that it changed). I may be looking at using your solution until this update as I am currently deploying a large solution using the Report Server and RLS is a requirement.

https://docs.microsoft.com/en-us/business-applications-release-notes/october18/intelligence-platform/power-bi-report-server/index

topic Re: Row Level Security using any source data base / file in Report Server

Row Level Security using any source data base / file

Re: Row Level Security using any source data base / file

Re: Row Level Security using any source data base / file