Dynamic RLS by using a UserSecurity table

[NielsBakker]

Hi! 

In PowerBI, we have a data model and want to apply a quite flexible data security that includes all possible scenario's. To achieve this we are thinking about using a UserSecurity table, which has all the permissions per user. See below a short example of a scenario: 

_ID	CLIENTID	USERID	SUBSCRIPTIONID	RESOURCEGROUPID
201	3	16	25	15348
301	3	16	17	null

In this case, user 16 can have access to subscription 25 but only to ResourceGroup 15348 within subscription 25. At the same time, user 16 can see everything in Subscription 17. We want to use null (wildcard) because otherwise the UserSecurityTable can get too large.

I tried to use relationships to the dimension tables, but in the above mentioned scenario, the null value overrides it.

Therefore, our idea now is to filter the fact table instead since all the id's are already in there (probably will get performance issues, but will solve that later). Further there won't be a relationship between the tables since we have to create multiple relationships between the 2 tables. So, we need a DAX query to solve this in Manage Roles  but my DAX is so far not good enough to make it work. 

In SQL, we would use the following query to achieve this: 

from FactTable1 fact
join UserSecurity usc on 
(
    (fact.subscriptionid = usc.subscriptionid or usc.subscriptionid is null) and
    (fact.resourcegroupid = usc.resourcegroupid or usc.resourcegroupid is null)
)
where usc.userid = 16

Could someone please help me with translating this SQL query to DAX? 

And I'm happy to receive some other idea's on how to set this data security up!

Many thanks!

Kind regards,

Niels

[speedramps · Accepted solution]

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 SUBSRIPTIONID

Add 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

[speedramps · Accepted solution]

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.

[speedramps · Accepted solution]

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 SUBSRIPTIONID

Add 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

[NielsBakker]

@speedramps Many thanks for your help! This indeed works. However, my securityTable won't have unique values in the columns because a different user can have the same access permissions. So access to the same SubscriptionIDs and ResourceGroupID's. Therefore, a many to many relationship is needed between the 2 tables but then the related function doesn't work. Is there a work around for that? 🙂

Again, thanks for your help.

[speedramps]

Hi @NielsBakker 

Thank you for your comments.

I am a volunteer who is happy to answer questions, but I operate on a one answer per question basis.

If you ask a wrong or incomplete questions, then I polietly expect you to accept the solution and raise a new question rather than start moving the acceptance critera for this question.

Please accept the solution and raise a new question with example input data and desired output.

If you quote @speedramps anywhere in the text then I will receive an automated notification and will try help unless another helper answers first.

You will get a quicker and better response without misunderstandings if you put time and effort into carefully writing a clear problem description with example input and output data. Look forward to helping you when this information is forthcoming

* Please DON'T copy & paste your DAX that does not work and expect us to fathom what you want. (That is just crazy). ‌‌
* Please DO give a simple non-technical functional description of what you want
* Keep it simple and break large projects into smaller questions and ask just one question per ticket.
* Rename columns to user friendly names. Avoid your own system jargon that we may not understand.
* Most importantly please provide example input data as table text (not a screen print) so helpers can import the data to build a solution for you. (Learn how to share data below)
* Provide the example desired output, with a clear step-by-step description of calculations and the process flow.
* Take time and care to use the same table and field names in the input, output and description so we can understand your problem and help you.
* Remove any unneeded tables, rows or columns which may cause confusion. Keep it short and concise with the minimal information regarding the key problem.
* Remember not to share private data ... we don't want you to get into trouble. ‌‌
* Please click the thumbs up button for these helpful hints and tips. Thank you.

Learn how to attach data in the forum using OneDrive:-
* Save your file in a OneDrive folder
* Right click on the file and click the “Share” blue cloud icon
* Click the bottom “Copy” button
* Click” Anyone with link can edit”
* Click “Can Edit”
* Click “Can View”
* Click “Apply” button
* Click “Copy”
* Paste the generated link via the forum, email, chat, or any other method.
* Helpers can then download your data, build a solution and share it back.

Learn how to attach data in the forum using Dropbox:-
1. Open Dropbox: Access the Dropbox folder on your computer or through the Dropbox web interface.
2. Select File/Folder: Find the file or folder you want to share.
3. Click Share (or Get Link): Look for a "Share" option or a similar "Get Link" option.
4. Choose Permissions: Decide whether to allow "view only" or "view and download" access.
5. Copy and Share: Copy the generated link and share it with anyone via the forum, email, chat, or any other method.

[RemyO]

I stand corrected. You are absolutely right.

[RemyO]

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