3
0
During this post we will discuss abut dynamic masking on JSON data. As per the requirement we have JSON data store in variant column of JSON table. This data holds all the confidential and Payment information about the customers. Data inside the JSON tag needs to be masked based on the Roles accessing the table. We have three custom Role Hierarchy in the system. Payment data should not be expose to the lower Role within the hierarchy. Topmost Role should have access to complete payment data.
Below is the table holding JSON data:
Following requirement we are going to implement in this post.
Technical Implementation:
Firstly, Create UDF:
- We will create a UDF which will accept the current Role or Responsibility as first argument. Second Argument we will pass the complete JSON data
- Inside the UDF based on the Role and Key received in JSON data masking the Value.
- In case of any Roles (does not lie in hierarchy) all data should be masked.
Secondly, Create Masking Policy:
- Check the current Role and call the UDF with JSON value as input.
- Apply Masking Policy to the VARIANT Column
Finally, Create below three Roles and assign them to the Users with required privilege’s.
- CUSTOMER_CALLER_USER
- FIRST_LINE_MGR_USER
- OPERATION_MGR_USER
———————————–UDF Function——————————————-
create or replace function Customer_Mask(current_resp string,v variant)
returns variant
language javascript
as
$$
if (CURRENT_RESP == 'CUSTOMER_CALLER' && "Invoice_info" in V) {
V["Customer_Amt"] = '******'
V["Invoice_info"]["Invc_no"] = '********';
V["Invoice_info"]["Amt"] = '********';
V["Invoice_info"]["Invc_cd"] = '********';
V["payment_info"]["type"] = '********';
V["payment_info"]["number"] = '********';
}
else if (CURRENT_RESP == "FIRST_LINE_MGR" && "payment_info" in V) {
V["payment_info"]["type"] = '********';
V["payment_info"]["number"] = '********';
}
else {
V["customer"]["company"] = '********';
V["customer"]["first_name"] = '********';
V["customer"]["last_name"] = '********';
V["Address"]["State"] = '********';
V["Address"]["City"] = '********';
V["Address"]["Pin"] = '********';
V["Customer_Amt"] = '******'
V["Invoice_info"]["Invc_no"] = '********';
V["Invoice_info"]["Amt"] = '********';
V["Invoice_info"]["Invc_cd"] = '********';
V["payment_info"]["type"] = '********';
V["payment_info"]["number"] = '********';
}
return V;
$$;
———————————–Masking and UDF——————————————-
create or replace masking policy customer_info_mask as (val variant) returns variant ->
case
when current_role() in ('ACCOUNTADMIN','OPERATION_MGR') then val
when current_role() in ('CUSTOMER_CALLER') then Customer_Mask('CUSTOMER_CALLER', val)
when current_role() in ('FIRST_LINE_MGR') then Customer_Mask('FIRST_LINE_MGR', val)
else Customer_Mask('No Role', val)
end;
———————————–Apply Masking on Table——————————————-
alter table customer_json_info modify column v set masking policy customer_info_mask;
Output:
Login with CUSTOMER_CALLER_USER:
Login with OPERATION_MGR_USER:
