Forum Discussion
Dynamic Row Level Security Based On Multiple Fields
I have a dataset that looks like this:
Each transaction has a territory code and a bill to code, and each of those codes has a user associated. In this example, territory or bill to code NAP1 is associated with user 1, territory or bill to code NAP2 is associated with user 2, and territory or bill to code is associated with user 3.
I need to implement dynamic row level security as follows:
- If the territory code OR the bill to code begins with "NAP", any associated user should have access to the data
- If the territory code OR the bill to code begins with anything else, the user associated with that code should have access to the data
In this example, users 1 and 2 both are associated with codes beginning with NAP so they should both see all rows where territory or bill to code begins with NAP. Or in other words, they should both see all transactions except 9 and 12. User 3 should only see transactions 3, 7, 9, 10, 11, and 12.
I created a sample .pbix file that you can download here.
4 Replies
When you say dynamic RLS do you mean dynamic RLS as in "based on USERPRINCIPALNAME mapping" or do you mean "roles per user with rules that include OR statements" ?
lbendlin I need to use USERPRINCIPALNAME() to base the security on the user who is logged in. The rule also needs to consider both the territory code and the bill to code, so it will probably require an OR statement.
user = USERPRINCIPALNAME() || Left([Territory Code]="NAP" || Left ([bill to code]="NAP"
