Need assistance,guidance with Dynamic RLS

[ashmitp869]

Hi ,

I need some guidance with Dynamic RLS.

Attaching the sample pbix file

https://github.com/suvechha/samplepbi/blob/main/sample_info%20-%20security.pbix

Sharing the sample data model.

The security of this model should be.

Userid 9 should only view the projectid assign to the user i.e 1 and 5

Userid 7 should only view the projectid assign to the user i.e 1

 

Table                                              Column

Projects                                          Project_id

Core_UserProject                            userid,Project_id

CostItem                                         Project_id,cost_item_id   
All other tables                                cost_item_id 

Thanks

[ashmitp869 · Accepted solution]

Please see another post where its sharing the solution.
https://community.fabric.microsoft.com/t5/DAX-Commands-and-Tips/Dynamic-RLS-with-User/td-p/4592040

[lbendlin]

Please redesign your data model first. Implement a Star schema, and place the RLS dimension table on the outskirts so it can control the fact tables in the center.

[ashmitp869]

Hi @lbendlin 
Is it not the correct data modelling ?
Fact tables are :

   CurrentBudget
   TimePhasedBudget

   TimePhasedForecast 
   ActualCost
Dimensions :

       CostItem
       Projects

Please guide.

[lbendlin]

Which table has the dynamic RLS mappings?

[ashmitp869]

Projects having Project_id is linked to Core_UserProjects.

I am using Dynamic RLS in the Core_UserProjects which has got email id of user.

User = [Email] = USERPRINCIPALNAME()

The security of this model should be.

Userid 9 should only view the projectid assign to the user i.e 1 and 5

Userid 7 should only view the projectid assign to the user i.e 1

[lbendlin]

Core_Userproject is a fact table on the receiving end of the Projects dimension table. It doesn't control anything, making your RLS ineffective.

Your RLS table must control the rest of the data model to be effective.

[ashmitp869]

Will you please provide me any solution or guidance - on how to implement the Dynamic RLS. ?

As Core_Userproject is the table where I get the information -

Which user has which project_id access.
Thanks

[lbendlin]

Change the relationship to many-to-many and make it single direction from user to projects.