Forum Discussion
Dynamic RLS by using a UserSecurity table
Hi NielsBakker
I politely disagree with RemyO.Yes it is complicated but in the real world security is complicated,
because you have users who need access to all X and Y but only some of Z
which would be very time consumming to set up and maintain in a relationship table
but very easy to confugure in your simple example table.
If you know a little DAX then it is possible and very easywith just a few commands
Try this ....Use -1 or * or 0 or anything except "null" in your securitry table
Out your secret data in Fact table
Build a 1:M relationship from your Security table to your Fact table using the SUBSRIPTIONIDAdd a security role with the following rules
Test using the "View as" function
I tested it and it seems to works exactly as you requested.
Please click the thumbs up because I have tried to help,
and if it works then click the [accept solution] button.
Many thanks
Thank you NielsBakker
Please quote @speedramps in you next question and I can show you some more advanced row level security like this ...
So Jane has access to all store in France
but just stotes 1 and 4 in UK
and all store in USA except store 1 and 3.
We can also make these rules date effective, to give staff temporay access to stores for cover during holidays and sickness.
These real life scenarios happen and need to be easy to administer via a config table.
This setup sounds way to complex. Because how will you filter your data ?
The way power bi works. You come in being a user Most of the times with an email adress
In your case you need a table that translates the email to a client ID
Then with the client ID you go to some table like you describe You do this using a relationship
Then you get some results and use these to filter your table
Since power BI cannot handle combined keys you need a key of 1 column
In your case CONCAT (SubscriptionId, "-", ResourceGroupID) and some logic to suppress null
Resulting in the values 25-15348 AND 17
Now in your target you should have a simular column and you are done filtering
The reason this will not work is because you will have too many variations in your filters.
Having said all this There is 1 option i never tried
That is linking each separate column to different dimension colums and filtering through the dimension
IE subscriptionid filters dim_subscription
resourcegroupid filters dim_resourcegroup
The effect is dat the dims filter the fact
-----
What i am sure of which will work is
I think in your setup recourcegroup is a subset of subscription
So you can have:
Resourcegroup 1 subscription 1
Resourcegroup 1 subscription 2
Resourcegroup 2 subscription 3
Both resourcegroup AND Subscription group numbers are unique
If you want a person to access the complete recourcegroup Give him/her access to all subscriptions
In the example If Joe needs access to Resourcegroup 1 give him access to both subscriptions
Hopefully this helps