Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
nisuomi
Resolver I
Resolver I

Security: RLS & columns

Hi,

 

I have a case where we have a colum what tells if the person has a special rights to read data with a certain level or not.

And we have 6 different levels of data in our hierarchy. Example data:

 

 

Useridlevel1level2level3level4level5level6RIGHTS
1AAAAAAAAAAAAAAAAAAAAA1
2AAAAABAABAAABAAAABAAA2
3AABABAABAAABAAAABAAAA 


Userid 1 would see all that have the same level1 that he does, so in this cse userid1 would see all 3 lines

Userid 2 would see the second level data and all that are having the same level2 value than he, so he would see 2 lines.

Userid 3 does not have any special rights and this would be proceed to normal hierarchy level with path.

 

I tried to create a RLS DAX, but for some reason I don't see any data. And Power BI is not giving me any error feedback.

Do you know what is wrong, or should I look this on a completely different angle?

 

SWITCH(LOOKUPVALUE(RLSTable[RIGHTS],'RLSTable'[User],USERPRINCIPALNAME()),1,
LOOKUPVALUE(RLSTable[level1],'RLSTable'[User],USERPRINCIPALNAME()) = 'f_data'[level1],2,
LOOKUPVALUE(RLSTable[level2],'RLSTable'[User],USERPRINCIPALNAME()) = 'f_data'[level2],3,
LOOKUPVALUE(RLSTable[level3],'RLSTable'[User],USERPRINCIPALNAME()) = 'f_data'[level3],4,
LOOKUPVALUE(RLSTable[level4],'RLSTable'[User],USERPRINCIPALNAME()) = 'f_data'[level4],5,
LOOKUPVALUE(RLSTable[level5],'RLSTable'[User],USERPRINCIPALNAME()) = 'f_data'[level5],6,
LOOKUPVALUE(RLSTable[level6],'RLSTable'[User],USERPRINCIPALNAME()) = 'f_data'[level6]
))

Any help is much appreciated!

 

edit:

And there is a f_data table which has the same level-columns with extra data, to summarize values.

 

 

 

Cheers,
Niko

1 ACCEPTED SOLUTION

Hello @v-jiascu-msft,

 

Yeah I know it would be good practice to share the files always, but they are not so anonymized yet.

Actually when I started to make it more random data, it started to work for some reason ... ? 

 

Alltho' I changed my DAX a little bit to this:

	IF(maxX (Filter(RLSTable, RLSTable[User]=USERPRINCIPALNAME()) , RLSTable[RIGHTS]) = 1,
	LOOKUPVALUE(RLSTable[level1],'RLSTable'[User],USERPRINCIPALNAME()) = 'f_data'[level1],
		IF(maxX (Filter(RLSTable, RLSTable[User]=USERPRINCIPALNAME()) , RLSTable[RIGHTS]) = 4,
			LOOKUPVALUE(RLSTable[level4],'RLSTable'[User],USERPRINCIPALNAME()) = 'f_data'[level4]

I have to check this more deeply if it actually now works. And I try to anonymize the data also for others to use.

 

 

Cheers,

Niko

View solution in original post

10 REPLIES 10
LivioLanzo
Solution Sage
Solution Sage

@nisuomi

 

You need to create a table like the below

 

Capture.PNG

 

Where the last column is a calculated column with this formula:

 

UserPath = PATH( Users[UserID],Users[Manager] )

 

And then the Row Level Security Formula is:

 

PATHCONTAINS(
             Users[UserPath],
             LOOKUPVALUE(
                   Users[UserID],
                   Users[Email],
                   USERPRINCIPALNAME()
            )
)

 

Capture.PNG

 

Capture.PNG

 

Capture.PNG

 

 

Capture.PNG

 


 


Did I answer your question correctly? Mark my answer as a solution!


Proud to be a Datanaut!  

Hello @LivioLanzo,

 

Thank you for your answer, but this quite isn't the solution I am looking for. I already have this kind of a hierarchy path solution in place for the current hierarchy RLS. But the case I had is related to special cases that overrides the normal path-based RLS. So if in the special case column there is no number, it will use the path-based solution. If there is a number on the special case column, it will get the value from the right column and use the right column from the other.

 

IDK if I need to create "special case" column for each of the unit_level -columns. Or should I create a summarized table that somehow switches all the separate columns into one. I will post here if I found any solutions on my own.

Hi @nisuomi

 

what should happen then when you have RIGHTS 1 and 2 ?

 

 

 


 


Did I answer your question correctly? Mark my answer as a solution!


Proud to be a Datanaut!  

Hi @LivioLanzo,

 

The DAX should look the RIGHTS column for access rigth level, for 1 it meas column level1 and for 2 it means column level2.

If it is empty, it will use the PATH() solution, which isnt present in this sample that I provided, but there is userid + managerid columns also for that and uses that solution that you presented on your first reply. 

 

But in this case, we want to override this security for few users witht he RIGHTS column and from there we should be able to check the needed level access and then find from the needed level column the value of the access rights. For example RIGHTS 1 for user 1 it would find the column value from level1 and it would be A. So, this userid1 would see all the data who have the value A on the column level1 - and so on.

 

We have the same columns also in the fact table, level1, level2 and so on. And I have tried to do a lookup between these two table. The DAX did not say any erros, but I don't see any data, so something is wrong.

 

Did you get anything out from this? 😄 I can try to make up some dummy pbix ... 

 

 

edit.

For RIGHTS 2 for userid 2 it would search column level2 and find the value from there, which would be AA.

The userid2 would see all the data who have the level2 and value AA on there. So userid2 would see userid1 and userid2 data.

 

Cheers,
Niko 

 

Does your table look like the below?  try the below RLS rule:

 

 

 

Capture.PNG

 

 

 

=
SWITCH (
    LOOKUPVALUE ( Users[Rights], Users[Email], USERPRINCIPALNAME () ),
    1, Users[Level1]
        = LOOKUPVALUE ( Users[Level1], Users[Email], USERPRINCIPALNAME () ),
    2, Users[Level2]
        = LOOKUPVALUE ( Users[Level2], Users[Email], USERPRINCIPALNAME () ),
    PATHCONTAINS (
        Users[Path],
        LOOKUPVALUE ( Users[UserID], Users[Email], USERPRINCIPALNAME () )
    )
)

 


 


Did I answer your question correctly? Mark my answer as a solution!


Proud to be a Datanaut!  

Hi @LivioLanzo,

 

Not quite, but almost. All the levels had some information in those columns, there are no empty values. It is hierarchy so do speak as well.

 

As seen on the image below, where is the access table (not affected by the RLS)

RLS_special_case_2.png

 

usersaccounts 1,2,3 all have special_case on them, but not the 4.

All of these will have the rights determined by that unit_level_X that they have been assigned on the special_case column and what calue is on their row on that column. 

 

So as example user1@sample.com has special_case as 4. What happens:

  • Check unit_level_4 column, it is the level of accesses that the user will have
  • Check unit_level_4 column value for user1@sample.com, it is AAAB
  • We will show all the data for user1@sample.com which has value AAAB on the unit_level_4 column

Did this make this situation more clear :)? 

It actually got worked with the code I proved earlier, I got a little help from one site. Which I have already lost 'cause I cleared system memory etc. today... Maybe have to find it again and I can link it here.

 

 

 

Cheers,
Niko

 

 

 

@nisuomi

 

as far as I can see then you just need to account for the extra levels in my previous formula, because I only accounted for two

 

 

 


 


Did I answer your question correctly? Mark my answer as a solution!


Proud to be a Datanaut!  

Hi Niko,

 

Please share a dummy pbix file. You can upload it to the cloud drive like OneDrive, GoogleDrive, then share the download link here.

 

 

Best Regards,
Dale

Community Support Team _ Dale
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Hello @v-jiascu-msft,

 

Yeah I know it would be good practice to share the files always, but they are not so anonymized yet.

Actually when I started to make it more random data, it started to work for some reason ... ? 

 

Alltho' I changed my DAX a little bit to this:

	IF(maxX (Filter(RLSTable, RLSTable[User]=USERPRINCIPALNAME()) , RLSTable[RIGHTS]) = 1,
	LOOKUPVALUE(RLSTable[level1],'RLSTable'[User],USERPRINCIPALNAME()) = 'f_data'[level1],
		IF(maxX (Filter(RLSTable, RLSTable[User]=USERPRINCIPALNAME()) , RLSTable[RIGHTS]) = 4,
			LOOKUPVALUE(RLSTable[level4],'RLSTable'[User],USERPRINCIPALNAME()) = 'f_data'[level4]

I have to check this more deeply if it actually now works. And I try to anonymize the data also for others to use.

 

 

Cheers,

Niko

Thanks for sharing, Niko. You can post further questions anytime.

 

 

Best Regards,
Dale

Community Support Team _ Dale
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.