I am currently building up a new data model in the area of sales. In the old data model, which I want to replace, I had very specific row-level security settings for each user: Sometimes, settings can be done with an "AND" filter:
UserXY should have access to all customers from China selling Toys
Sometimes, they are defined as "OR":
UserXYZ should have access to all customers from Europe and to all food products
As the access rights differ from one customer to another, one or more role per user had to be created (if the rights are "AND" based, a additional role has to be created).
In the future, we would like to control the data access trough a table, where the respective objects the user has access to are defined. But I am afraid of possibly not be able to cover AND/OR logic on different dimensions.
Of course MS also has concepts about dynamic RLS (https://learn.microsoft.com/en-us/power-bi/connect-data/desktop-tutorial-row-level-security-onprem-ssas-tabular). But as this is just for one dimension, it would not cover our requirements.
Did someone once stumbled upon a similar problem inside the data models? Is there any best practice solution for a better handling of individual RLS access rights in tabular models?
Regards, Ivo
Whenever you have complex RLS requirements, there's a simple pattern to follow in your model.
Introduce and populate user entitlement tables of the form (UserName,DimensionKey) that flow filters to the target dimension (or perhaps (GroupId, DimensionKey) ).
So for
UserXY should have access to all customers from China selling Toys
So ahead of time run a query that calculates "all customers from China selling Toys" an insert into the CustomerEntilement table all the
insert into CustomerEntitlement (UserName, CustomerId)
select 'UserXY', CustomerId
from DimCustomer c
join FactSales s
on s.CustomerID = c.CustomerID
where s.ProductType = 'Toys'
Then you simply put an RLS filter on CustomerEntitlement.