Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
Hi,
I'm working on a model that needs to have two layers of restriction.
I can't do both at the same time because it is applied on a model that is used for several reports, and not all of them need the over-restriction to take place.
The first layer is based on the business unit that a user has been assigned to, in CRM (D365), and defined as a dynamic RLS in a role. The hierarchy that these business units describe for this customer has 6 levels. This part is working ok. Let's call this table "A".
The second layer is an over-restriction on top of the first one, with the purpose to allow the users to access just some of the lowest level items in the business unit hierarchy. It's defined with two tables:
The way it is intended to work, is to set a column in table A, determining for each entry whether it's visible or not to the user, and it is applied as a filter at report level, "IsVisible = Yes".
It all would be easy peasy if i were allowed to use the function USERPRINCIPALNAME() in that column. But i'm not, so i've had to get creative...
In order to ascertain whether a lowest level business unit is visible or not for a given user, i have set RLS to restrict the entries of the tables for the second layer of restriction (B1, B2), to those that are explicitly related to the user that is accessing the model.
This part works fine as well, and if i test the solution by switching to a user who has restrictions, i'm able to see the effect of RLS because there's just one entry in table B1, and the list of allowed business units for that user on table B2.
Now, here's where i found the incoherence. I need to use a formula such as FIRSTNONBLANK() in a column in table A, to resolve if the current user accessing the report has any additional restriction according to the content of B1, and in case there is, which are the allowed items listed in B2.
As i've applied RLS to the tables B1 and B2, only one value should be available to be returned for FIRSTNONBLANK(), and that should be for the current user.
But FIRSTNONBLANK() and MIN(), are returning the first real value of the table before RLS has been applied, which does not match to the actual user accessing the report.
How can this be possible?
I am certainly lost, and in a hurry to get this solved.
Thanks in advance for any hint you can provide.
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.