Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
Hi All,
I am new to this tool.We recently migrated to Power BI from 3rd party tool.We are trying to implement ROW Level Security for our scenario.We are getting Users(Total 9500 Users) Info from Azure AD. We have stores across all states in USA.We have to implement ROW Level security based on Store ID & Login Email ID.
Below 2 scenario's has to be implemented.
1)When user login to Power BI, Store table supposed to display data only for his/her store ID.
2)When any Manager logins, It is supposed to display all storeID's.
Our table Structures:
Table1:Store:-
Store ID Store Name Storestate
100 ABC IL
101 DEF NY
102 GHI NJ
Table2: RLS_Mapping
Store ID EMAIL
100 user1@live.com
101 user2@live.com
103 user3@live.com
Appreciated your help.
Thanks,
Raj
You're nearly there.
- Create a relationship from your RLS_mapping table to the Store table based on Store ID. This will be created as Many-to-Many (which is ok) and with bidirectional filter (which is not). Change the filter direction to single, from RLS_Mapping to Store
- In the RLS_mapping table create a rule that says EMAIL=USERPRINCIPALNAME()
That's it. Now you can decide if you want to list the manager multiple times in the RLS_Mapping table (with all stores) or if you want to have a separate table that lists all manager emails and do a different rule there etc.
Lbendlin,
Appreciated your quick response.Instead of Joining both the tables,Can we Implemneted RLS secuiry like below in Power BI??
SELECT * FROM Store where storeID in
(select storeid from RLS_Mapping where Email=USERPRINCIPALNAME())
Thanks,
Raj
sure, you could use LOOKUPVALUE() etc, but why? Let the data model do the work.
Lbendlin,
When i join 2 tables,RLS is not working when i tested with other user(User2@live.com(Power BI Pro).
Whenever you test RLS for a particular role on Power BI Desktop you need to select both the role to test and the Other User role and you need to specify the email address of the test subject.
lbendin,
Thanks alot. After selecting both the roles it was working fine.I have to Incorporate my second Conditon into the logic.
When ever IS_Manager Flag Value ='Y' then show all storeID's.
When ever IS_Manager Flag Value='N' then Only show Assgined Store ID Data.
Table2: RLS_Mapping
Store ID EMAIL IS_Manager?
100 user1@live.com Y
101 user2@live.com N
103 user3@live.com N
Thank you,
Raj
Probably easier to create a separate role for managers, and simply not specify any RLS rules for that role.
We have total 9000 users,Out of which 3000 users has to see all records(no data level security).
As i mentioned in my previous reply based on the Flag it has to decide apply RLS or Show all records.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
12 | |
2 | |
2 | |
1 | |
1 |