Forum Discussion
RLS using login email to limit data view
Good day to whoever reads this. 🙂
I am trying to use RLS in my PowerBI desktop file to allow someone to view or not view certain data for example.
[email protected] would be allowed to see everything while [email protected] would only be able to view data for the eastern branch of the company and someone not in the RLS table wouldn't be able to see anything at all. I would like to filter these permissions based on the email they are using to log in with I have tried using:
[UPN] == userprincipalname ()
But fail to get it working and fully understand how to do it. After viewing the documentation on RLS I'm still unsure of how to do this so anyone that can offer some help I would highly appreciate it!
P.s. If any extra information is needed from my side please let me know I'll gladly provide anything I can 🙂
Hi Anonymous,
>>If I understand you correctly it needs to have either a column with those usernames in it in each table or a relationship where it takes the username from a table and uses it on any table that is connected to it by those relationships?
In fact, they are basic usages and username part are not necessary. If the basic username and records mapping not suitable for your usernames, you can also consider setting some two or three step roles andd add a default role for the users which not has correspond usernames.
For example, you can bind user with group/departments and use RLS filter on the group field to filter records and apply these filter effect based on relationship.(user table link to the user group/department table, and use this table as bridge to link other tables; current username as condition to find out correspond group to filter records, if not existed correspond username, return the default group filter)
Regards,
Xiaoxin Sheng
4 Replies
Hi Anonymous,
For dynamic RLS with USERNAME/USERPRINCIPALNAME, you need to create or modify your user table to add a field with correspond user accounts, then you can use Dax USERNAME/USERPRINCIPALNAME function result as condition to check that field to apply filter effect. Then these filter effect will be apply to other tables if you has enabled the relationship from user table to other table with ‘both’ directions.
DAX USERPRINCIPALNAME - Use in RLS - Power BI Docs
What is the Direction of Relationship in Power BI? - RADACAD
Regards,
Xiaoxin Sheng
Hey thereAnonymous,
Thank you for your response I did view those links before posting here but If I understand you correctly it needs to have either a column with those usernames in it in each table or a relationship where it takes the username from a table and uses it on any table that is connected to it by those relationships? if this is the case RLS wouldn't work too well for me here since not all of them are connected to the employee information where the username would be.
Sorry if the question seems like a dumb beginner's error I'm still learning a lot about BI.
Thank you in advance!Hi Anonymous,
>>If I understand you correctly it needs to have either a column with those usernames in it in each table or a relationship where it takes the username from a table and uses it on any table that is connected to it by those relationships?
In fact, they are basic usages and username part are not necessary. If the basic username and records mapping not suitable for your usernames, you can also consider setting some two or three step roles andd add a default role for the users which not has correspond usernames.
For example, you can bind user with group/departments and use RLS filter on the group field to filter records and apply these filter effect based on relationship.(user table link to the user group/department table, and use this table as bridge to link other tables; current username as condition to find out correspond group to filter records, if not existed correspond username, return the default group filter)
Regards,
Xiaoxin Sheng