Masking a column based on user login

[Anonymous]

Hi,

I need to mask the data in a column of a table (Table 1) based on the user login. I have another table (Table 2) that has user ids who have access to see the masked column. Calculated column does not allow use of user principal. Is there any work around that I can still use to mask the column in Table 1 based on the user login.

Thanks in advance.

[alxdean]

I know this answer is wayyyy overdue, but the solution to the problem has been provided by combining two answers into one...

Step 1) Create a mapping Table that maps the UserPrincipalName to your permisisons. let's say

UPN = alex@contoso
Permission = Masked

UPN = manager@contoso

Permission = All

then create a Role in RLS to filter the Permission Table based onn UPN=UserPrincipalName(). so their Login will automatically filter that table. 

then in your Table you can use a calculated field with a Lookup for Min(Permission)="All" in the permission table to display the data or not. you won't be using the UserPrincipalName in the field at all. that already did its magic when the user opened the report. 

Even better would be to separate out the sensitive data into a separate table with a 1-1 match on the orginal one and use relationships to filter the data already at Row Level. In that way the user can't even circumvent the masking if they start using Self-Service features like QnA or online report editing. 

i.e. 

Original:

ID = 1

Name=Alex

Creditcard = 1234-4567-9012-4444

New:

Employee:

ID = 1

Name=Alex

Sensitive:

ID = 1

Creditcard = 1234-4567-9012-4444

Permission = "HR"

Permissions:

UPN=alex@contoso

Permission = "HR"

Add a m-n relationship between Permissions and Senstive based on the Permission Column with security filter applied.

Add a 1-1 relationship between Employee and Sensitive based on ID column

Then either just drag the fields together on the fly, or add a new calculated column that shows a mask "****-****-****-****" when no Related(Creditcard) is found.

Don't forget to apply the RLS with the UPN on the Permission table though...

Hope this all makes sense!

[ArunTiruveedula]

Hi alxdean,

Thanks for your contribution. I have tried your solution and I have a problem with MIN in the Calculated column.

Calculated column is calculated on the Permission tabe even before the RLS is applied on it. MIN(PERMISSION) = "ALL" always even when the PERMISSION table is having only one record with PERMISSION = "MASKED" after RLS is applied.

So data is never masked in this case. Am I missing something?

Thanks in advance for your reply.

[alxdean]

Yeah, I tried different options in the end and the MIN option won't work, as you said, it is evaluated before the RLS is applied. A real bummer! So the only option available is to use related tables and enforce RLS via the relationship. You won't be able to use RLS with calculated columns.  I.e. move the sensitive data out to a related table and lock it down with RLS. 

[v-shex-msft]

Hi @Anonymous ,

Nope, this is impossible. Username and userprincipal are dynamically generated based on login account, current power bi does not allow you to create these type of dynamic columns.

In my opinion, I'd like to suggest you create a user mapping table to store all usernames, then add an RLS filter based on the current username to filter records based on login username.

RLS with UserName() 

Regards,

Xiaoxin Sheng

[Anonymous]

Hello,

My issue is not with RLS - I understand how RLS works. I need to be able to mask an attribute column at run time based on the logged in user. For example - only super users have access to see employee addresses. For all other users, the address field should appear masked in the report/dataset.

Are there any work-arounds?

[v-shex-msft]

HI @Anonymous ,

They are pre-programming functions to extract specific information from the Data model itself and they can't use in the calculated column:

CUSTOMDATA, USERNAME, USERCULTURE and USERPRINCIPALNAME functions are not supported in calculated tables/columns. These functions may only be used in Measures or in the AllowedRowsExpression.

where is the AllowedRowsExpression 

I'd like to suggest you vote below idea with similar requirement:

Provide username function in Power Query - M or DAX calculated columns 
Regards,

Xiaoxin Sheng

[smarthp29]

So I tried it. As you said the Column does not allow variable based swapping. But the Measure does. I have implemented the measure using min(Column) because I think since you want address to be displayed there would be a single record per Customer.

There are three formulas that I implemented: 

AccessVariable = MIN(CLS[AddressAccessValue])

AddressDisplayColumn = SWITCH([AccessVariable],"Yes",Data[AddressUnEncrypted],"No",Data[AddressEncrypted])

AddressDisplayMeasure = SWITCH([AccessVariable],"Yes",MIN(Data[AddressUnEncrypted]),"No",MIN(Data[AddressEncrypted]))

I have attached the results of this experiment below. This is a workaround and would have limitations based on use case.

Access as User1 
Access as User2

Default without CLS

[smarthp29]

Not sure if this will work but try creating another column which has a one-to-one mapping with your service principle like IDs or something like that and then use it to do your calculated column. So you will have 2 columns which have distinct values and one is used as a User Principal and other one for calculation. 

[Anonymous]

Calculated columns do not allow use of username or userprincipalname in the formula. Otherwise I could have been able to conditionally mask it.