Forum Discussion

nisuomi's avatar
nisuomi
Resolver I
7 years ago
Solved

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 d...
Need Help
  • nisuomi's avatar
    nisuomi
    7 years ago

    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

nisuomi's avatar
nisuomi
Resolver I
7 years ago

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? :D 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 

v-jiascu-msft's avatar
v-jiascu-msft
Microsoft Employee
7 years ago

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

  • nisuomi's avatar
    nisuomi
    Resolver I
    7 years ago

    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

    • v-jiascu-msft's avatar
      v-jiascu-msft
      Microsoft Employee
      7 years ago

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

       

       

      Best Regards,
      Dale