When I am masking the JSON data which is stored in varchar(8128) field, using unstructured JSON fileformat is converting data to JSON format and when trying to insert that data back to varchar(8128), I am seeing error due to length.
*Is there any option to avoid this, maybe: converting JSON back to String/plainText to Insert ?
*I tried trimming in the select query, that is inserting the data but that is breaking the fileformat and I see data is not getting masked.
HCL America Inc.
Original Message:
Sent: 03-21-2024 03:57:34 PM
From: Tino Pironti
Subject: Masking Json data in Aurora PostGresql table
You cannot apply a date algorithm > those json nodes are separate fields.
Your only only masking option is to use an SecureLookup or Mapping for the DD part field using a list with values 1 to 28 (as those values exist in any month)
Original Message:
Sent: 3/21/2024 4:38:00 PM
From: Chandrapalred Borra
Subject: RE: Masking Json data in Aurora PostGresql table
Hi, I am able to mask the JSON data in the text field using the fileformat approach. I have other challenge, where I need to mask DOB, where the dob column is storing dateformat: yyyy-mm-dd , but the text column has Json data like below:
"dob" : {
"year" : 1945,
"month" : 7,
"day" : 9
},
I tried datashift Algorithm with days shifted on the date column and applied same algorithm to day (xpath), seeing below error:
algorithm DOB_Shift: Could not convert value of type LONG to type LOCAL_DATE_TIME required for masking
The top nonconforming data samples were:
N
Can you please help How can I achieve this consistent masking ?
------------------------------
Chandrapalred Borra
TDM Engineer
HCL America Inc.
Original Message:
Sent: 03-13-2024 12:12:40 PM
From: Tino Pironti
Subject: Masking Json data in Aurora PostGresql table
You would need to create as FILE-FORMAT one json file that has ALL json hierarchies you need to mask.
In technical sense the FILE-FORMAT is used to render the screen ... and only what you can see you can configure.
Unfortunately in json is no option to use something alike a wildcard.
(for XML we have a special plugin that uses XPATH language with all xpath options like wildcards etc. but nothing alike in JSON logic)
------------------------------
Tino Pironti
Masking SME
Technical Manager
Delphix
Original Message:
Sent: 03-13-2024 11:34:32 AM
From: Chandrapalred Borra
Subject: Masking Json data in Aurora PostGresql table
Hi @Tino Pironti, This worked for a json file having same format. However, I have json data in the same text field, which is not same everytime, I have nested array's in some rows, which also has PII information, which I need to Mask. Can this be achieved with one fileformat:
Ex: nested: Array ['0'] doesn't exist for 1st and 3rd record...
one record: $['request']['products']['nationalCreditFile']['inquiryNationalCreditFile'][*]['searchBy']['subjects']['primarySubject']['ref']['name']['first']
another record: $['request']['products']['nationalCreditFile']['inquiryNationalCreditFile'][*]['searchBy']['subjects']['0']['primarySubject']['ref']['name']['first']
third record: $['request']['products']['nationalCreditFile']['inquiryNationalCreditFile'][*]['searchBy']['subjects']['1']['primarySubject']['ref']['name']['first']
------------------------------
Chandrapalred Borra
TDM Engineer
HCL America Inc.
Original Message:
Sent: 03-08-2024 12:47:27 AM
From: Tino Pironti
Subject: Masking Json data in Aurora PostGresql table
You can easily achieve this by applying a file-format to the Col3 column of your table.
Please look at manage inventories > Document Store Type masking.
First you create a file-format of type JSON by uploading a JSON example that contains all nodes that might exist in the JSON document hierarchy.
in the table inventory then select Data Model : Structured and Document Store Type : JSON and from select-file-format : choose your uploaded file-format
Below is a screenshot of inventory edit dialog (in that case for XML but same exists for JSON)
------------------------------
Tino Pironti
Masking SME
Technical Manager
Delphix
Original Message:
Sent: 03-07-2024 04:10:09 PM
From: Chandrapalred Borra
Subject: Masking Json data in Aurora PostGresql table
I have a text field/column with json data and need to mask FN, LN, SSN.. inside it
Ex:
Col1: FirstName (varchar)
Col2: LastName (varchar)
Col3: Text having json like sample below
{
"name" : {
"first" : "EX",
"middle" : "",
"last" : "JOHNY",
"suffix" : "PK.",
"prefix" : "Mr."
}
}
I need to mask above First and Last name fields same as other columns Col1 and Col2.
The json array/text field is very big (2356787878), I just mentioned sample object of it.
Any way to achieve this using Delphix..
------------------------------
Chandrapalred Borra
TDM Engineer
HCL America Inc.
------------------------------