complex section access scenario for masking columns

[ms_12]

Hi All,

I have data with some PII fields. I have three user role which are defined in the section access data with USERID, ROLE field. Three roles are PII,SEMI_PII,NON_PII. In data I have around 3 PII fields. When user with role PII logs in, he should see all PII field values as is. When user with role SEMI_PII logs in, he should see all PII field values partially masked. When user with role NON_PII logs in, he should see all PII field values fully masked. Masking could be anything but masked values should be unique for each field value. for eg. I have Client Name A,B & C, so masked values could be like xAnvBgH, HnbvFTb & hhGVcDr which is unique to each Name as values will be repeating.

Definition of SEMI_PII is that we have indicator in our data, if indicator value is 1 PII fields should be masked, otherwise original values should be displayed. for eg. Let's say we have Client Name A, B & C. for values A & B indicator is1 and for C it is 0. So when user logs in he should see values cHbbVch,BngVbhjK & C. Which means A & B values will be masked.

Note: I have around 1000 users and PII field values for some field might have 5 million distinct values. 

I don't know how to approach this

[Qrishna]

Step 1 - You gotta have Section Access. In the script, Modify below per you app need.

// Section Access to control user access based on roles
Section Access;
LOAD * INLINE [
ACCESS, USERID, PASSWORD
ADMIN, ADMIN, ADMIN
USER, PII, PII
USER, SEMI_PII, SEMI_PII
USER, NON_PII, NON_PII
];

// Section Application to load the actual data
Section Application;

// Load the data for conditional masking on UI
RawData:
LOAD * INLINE [
CLIENT,SALES, WAREHOUSE, EMPLOYEES
X,143256, 25789, 3478353
];

Step 2: 

create a st table, 'CLIENT' as dimension, 

Expression:

=IF(QVUser() = 'PII', SALES
, IF(QVUser() = 'SEMI_PII' , LEFT(SALES, 1) & Upper(Chr(Floor(Rand()*26)+65) & Chr(Floor(Rand()*26)+65) & Num(Floor(Rand()*10), '0'))
, Upper(Chr(Floor(Rand()*26)+65) & Chr(Floor(Rand()*26)+65) & Num(Floor(Rand()*10), '0'))
)) 

Similarly do the other expressions. See attached.

As you might have clients logging in with NTUSER, use OSUSER() in lieu with QVUSER().

Output:

[ms_12]

@Qrishna  Thanks for your reply but I am using qlik sense. Also, role filed is already available in my access data. Let's say I have below data

Load * Inline [
client_name,client_address,client_profile,indicator
A,ABC,a1,0
B,XYZ,b1,1
C,PQR,a1,1
D,DEF,b1,1 ];

As per the requirement when user logs in, I need original field to reflect as below

/* For PII User */
client_name,client_address,client_profile
A,ABC,a1
B,XYZ,b1
C,PQR,a1
D,DEF,b1

/* For SEMI_PII User */ Highlighted in red is mask value for client_profile 'b1'
client_name,client_address,client_profile
A,ABC,a1
gBhJKmc,BngVFGb,BHnnnG
KnhBNhn,HNJnhGBt,KMNbnhg
lJnhBNg,UhnhYTgb,BHnnnG

/* For NON_PII User */
client_name,client_address,client_profile
JkNJhN,RgbhNG,KMNbnhg
gBhJKmc,BngVFGb,BHnnnG
KnhBNhn,HNJnhGBt,KMNbnhg
lJnhBNg,UhnhYTgb,BHnnnG

Hope this explains

[Qrishna]

1. As i was working on QV desktop i've shown the QV views but in fact it doesnt really matter if its QS or QV, the expressions remains same for both.

2. Modify the existing expression to include indicator condition in the expression like below:

=IF(Match(QVUser(),'ADMIN','PII')
, client_name
, IF(QVUser() = 'SEMI_PII' and indicator = 0
, client_name
, IF(QVUser() = 'SEMI_PII' and indicator <> 0
, Lower(Chr(Floor(Rand()*26)+65) & Chr(Floor(Rand()*26)+65) & Num(Floor(Rand()*10), '0'))
, IF(QVUser() = 'NON_PII'
, Lower(Chr(Floor(Rand()*26)+65) & Chr(Floor(Rand()*26)+65) & Num(Floor(Rand()*10), '0'))
)
)
)
)

do the same for the remaining expressions.

if you want more number of characters in the masked field values, increase the number of chars in :

Lower(Chr(Floor(Rand()*26)+65) & Chr(Floor(Rand()*26)+65) & Num(Floor(Rand()*10), '0')) &.... & ...

if you want masking for client_profile=b1, include thta in the expression just like how indicator field is included.

Below should be the output view.

[ms_12]

@Qrishna  for SEMI_PII, your mask values for client_profile b1 is different which should be same for same values. if for value 'b1' masked value is 'xHnBg' then for all the rows of 'b1' same masked value should be displayed

[Qrishna]

yeah yu could use autonumber on client_profile b1 field value and include that in the masking value. the mask value remains the same everywhere

[Kushal_Chawda · Accepted solution]

@ms_12  I would maintain different fields for different role. Basically I would maintain client_name, client_name_semi_pii, client_name_no_pii. Depending on user profile then I will hide the column. Then capture the fields to be displayed which will be linked to section access data. You can then capture the display field in variable to create dimension dynamically. Following are the steps.

Make sure that field names are matching in OMIT field and display field with actual data fields as highlighted below.

Section access;

data:
LOAD ACCESS,
          USERID,
          ROLE as OMITGROUP
FROM section_access_data;

LOAD * inline [
OMITGROUP, OMIT
PII,client_name_semi_pii
PII,client_name_no_pii
PII,client_address_semi_pii
PII,client_address_no_pii
PII,client_profile_semi_pii
PII,client_profile_no_pii

SEMI_PII,client_name
SEMI_PII,client_name_no_pii
SEMI_PII,client_address
SEMI_PII,client_address_no_pii
SEMI_PII,client_profile
SEMI_PII,client_profile_no_pii

NO_PII,client_name_semi_pii
NO_PII,client_name
NO_PII,client_address_semi_pii
NO_PII,client_address
NO_PII,client_profile_semi_pii
NO_PII,client_profile ];

Section application:

display_fields:
LOAD * inline [
OMITGROUP, Display_client_name, Display_client_address, Display_client_profile
PII, client_name, client_address, client_profile
SEMI_PII, client_name_semi_pii, client_address_semi_pii, client_profile_semi_pii
NO_PII, client_name_no_pii, client_address_no_pii, client_profile_no_pii ];

Data:
LOAD client_name,
          if(indicator=1,hash256(client_name),client_name) as client_name_semi_pii,
          hash256(client_name) as client_name_no_pii,
          client_address,
          if(indicator=1,hash256(client_address),client_address) as client_address_semi_pii,
          hash256(client_address) as client_address_no_pii,
          client_profile,
          if(indicator=1,hash256(client_profile),client_profile) as client_profile_semi_pii,
          hash256(client_profile) as client_profile_no_pii,
          indicator
FROM Data;

Create 3 variables on front end.
vClient_name = only(Display_client_name)
vClient_address= only(Display_client_address)
vClient_profile= only(Display_client_profile)

You can now create 3 Master Dimensions

Client Name = [$(vClient_name)]
Client Address = [$(vClient_address)]
Client Profile = [$(vClient_profile)]

[ms_12]

@Kushal_Chawda  It works. Really clever way of doing it although bit lengthy.