Forum Discussion
How to set row level security customer company code linked to login ID
Hi Anonymous,
For your scenario, you can set up your RLS fitler on company_code filed.
Lookup company_code based on current username, then use this as filter parameter to apply on company_code field.
Sample formula:
[company_code] = LOOKUPVALUE ( 'Table'[company_code], 'Table'[login_Id], USERPRINCIPALNAME () )
Regards,
Xiaoxin Sheng
Hi Anonymous,
I'd like to suggest you to take a look at below link about RLS with username:
Add company code to user table to link company id with username and use this column to create relationship to other tables. Then you can use logined users to achieve dynamic rls filter.
Regards,
Xiaoxin Sheng
Hi Xiaoxin.
Thank you for reply. I tried to use RLS username() or userprincipalname() but the requeirments are racking my brain...
Table has data like below.
| company_code | login_Id | date | data |
| AAA | [email protected] | 2018/1/1 | 10 |
| AAA | [email protected] | 2018/2/1 | 20 |
| AAA | [email protected] | 2018/1/1 | 10 |
| BBB | [email protected] | 2018/1/1 | 20 |
| BBB | [email protected] | 2018/1/1 | 30 |
| CCC | 2018/2/1 | 40 |
Requirements.
-Customer company has multiple user. "user01" and "user02" can see data linked "AAA".
-Some users have same domain but other company like subsidiary. So, "user01" and "user02" do not see data linked "CCC". "user05" can see only data linked "CCC".
-Domain does not necessarily include a company name or code.
So, I might be able to solve this matter if I could use customer company code linked to loginID for condition.
Would you have any good ideas?
Hi Anonymous,
For your scenario, you can set up your RLS fitler on company_code filed.
Lookup company_code based on current username, then use this as filter parameter to apply on company_code field.
Sample formula:
[company_code] = LOOKUPVALUE ( 'Table'[company_code], 'Table'[login_Id], USERPRINCIPALNAME () )
Regards,
Xiaoxin Sheng