cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
rajiv_tarafdar Helper I
Helper I

Row Level Security using any source data base / file

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

  1. 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
  2. Use Microsoft AD Login for authentication
  3. Reports to contain all data by default and only filter on user view. 

 

Assumptions 

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

 

Design

  1. Create a Tables USER_ACCESS with the following 
  2. UID, Name, Role Type and Department / Division (or similar column)
  3. Populate the data in the table assigning users to a Department / Division
  4. 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
  5. Cross Filter Direction = Both 
  6. Apply Security Filter in both direction = Yes 
  7. 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
  8. Reload the data and ensure the filter works
  9. On the Table USER_ACCESS create ameasure UserID_Match = IF(CONTAINS(USER_ACCESS,USER_ACCESS[FULL_USER_ID], USERNAME()), 1 , 0)
  10. When a user logs in their record will automatically return a value one. 
  11. Now add this Filter to the Visual Level Filter with a value set to 0. 
  12. Add the data along with the Department / Divion Columns on the Graph or Cross Table  
  13. The data should automatically filter to the user using the Report on the Power BI Desktop and Power BI Report Server. 

Disclaimer 

  1. 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. 

 

2 REPLIES 2
mohammadhijazi
New Member

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

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?

healthEteam Resolver I
Resolver I

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

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...

 

Helpful resources

Announcements
New Ranks Launched March 24th!

New Ranks Launched March 24th!

The time has come: We are finally able to share more details on the brand-new ranks coming to the Power BI Community!

‘Better Together’ Contest Finalists Announced!

‘Better Together’ Contest Finalists Announced!

Congrats to the finalists of our ‘Better Together’-themed T-shirt design contest! Click for the top entries.

Arun 'Triple A' Event Video, Q&A, and Slides

Arun 'Triple A' Event Video, Q&A, and Slides

Missed the Arun 'Triple A' event or want to revisit it? We've got you covered! Check out the video, Q&A, and slides now.

Community Summit North America

Community Summit North America

Innovate, Collaborate, Grow. The top training and networking event across the globe for Microsoft Business Applications

Top Solution Authors