Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

The ultimate Microsoft Fabric, Power BI, Azure AI & SQL learning event! Join us in Las Vegas from March 26-28, 2024. Use code MSCUST for a $100 discount. Register Now

Reply
reddy6400
Frequent Visitor

Power BI Dynamic Row Level Security Suggestion

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

 

 

 

9 REPLIES 9
lbendlin
Super User
Super User

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

 

reddy6400_0-1594758901461.png

 

reddy6400_1-1594759042105.png

 

 

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.

@lbendlin..Can you Please suggest?

Helpful resources

Announcements
March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.

Fabric Community Conference

Microsoft Fabric Community Conference

Join us at our first-ever Microsoft Fabric Community Conference, March 26-28, 2024 in Las Vegas with 100+ sessions by community experts and Microsoft engineering.

Fabric Partner Community

Microsoft Fabric Partner Community

Engage with the Fabric engineering team, hear of product updates, business opportunities, and resources in the Fabric Partner Community.