Delphix Products

 View Only
Expand all | Collapse all

Masking Json data in Aurora PostGresql table

  • 1.  Masking Json data in Aurora PostGresql table

    Posted 03-07-2024 04:10:00 PM

    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.
    ------------------------------


  • 2.  RE: Masking Json data in Aurora PostGresql table

    Posted 03-08-2024 12:47:00 AM

    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
    ------------------------------



  • 3.  RE: Masking Json data in Aurora PostGresql table

    Posted 03-13-2024 11:35:00 AM

    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.
    ------------------------------



  • 4.  RE: Masking Json data in Aurora PostGresql table
    Best Answer

    Posted 03-13-2024 12:13:00 PM

    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
    ------------------------------



  • 5.  RE: Masking Json data in Aurora PostGresql table

    Posted 03-13-2024 12:43:00 PM

    Thanks, I will keep adding my json paths to the same file format and see, how it goes..



    ------------------------------
    Chandrapalred Borra
    TDM Engineer
    HCL America Inc.
    ------------------------------



  • 6.  RE: Masking Json data in Aurora PostGresql table

    Posted 03-21-2024 03:38:00 PM

    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.
    ------------------------------



  • 7.  RE: Masking Json data in Aurora PostGresql table

    Posted 03-21-2024 03:58:00 PM
    Edited by Michael Torok 03-21-2024 04:02:32 PM
    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)




  • 8.  RE: Masking Json data in Aurora PostGresql table

    Posted 04-04-2024 11:12:00 AM

    Hi, I have a follow up question on this topic:

    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.

    DelphixTableInsert.0 - }') was aborted: ERROR: value too long for type character varying(8128) Call getNextException to see other errors in the batch



    ------------------------------
    Chandrapalred Borra
    TDM Engineer
    HCL America Inc.
    ------------------------------



  • 9.  RE: Masking Json data in Aurora PostGresql table

    Posted 04-04-2024 11:39:00 AM
    Well .. if your String was ~ 8000 chars .. and after masking becomes over 8128 chars you might need to adjust the used algorithms.

    If original value of one node is "Hello" and after masking using SecureLookup gets a significantly longer value you have the reason.

    Option 1) create SL (SecureLookup) with shorter values in lookup file
    Option 2) use CM (CharacterMapping > always preserves length of value)





  • 10.  RE: Masking Json data in Aurora PostGresql table

    Posted 04-05-2024 02:19:00 PM

    Hi @Tino Pironti, Its is not the issue with Masking. It is the formatting which is changing the length.

    Ex:

    Source data : Varchar(8128) is storing data in plain/text format with out spaces

        "dob" : {"year" : 1945,"month" : 7,"day" : 9}......

    After Masking, My target data is getting changed to below json format while inserting, in new lines which is increasing the defined length in the DB varchar(8128) to something (11750) and saying not able to insert value too long..

        "dob" : {
              "year" : 1945,
              "month" : 7,
              "day" : 9
            },

    Do we have options to ignore any json paths in defining fileformat or options to transform Insert query.. ?



    ------------------------------
    Chandrapalred Borra
    TDM Engineer
    HCL America Inc.
    ------------------------------



  • 11.  RE: Masking Json data in Aurora PostGresql table

    Posted 04-05-2024 02:31:00 PM
    You mean the "pretty print / indentation" behaviour is causing this issue?
    Does the original content not have any indentations?
    In the product functionality for JSON masking this is not configurable .. but there is a professional services JSON plugin where the "indentation" can be set to 0 > means the result does not contain any spaces/CR. 
    I see no possibility to remove indents/spaces/CR from the product functionality.






  • 12.  RE: Masking Json data in Aurora PostGresql table

    Posted 04-05-2024 02:52:00 PM

    Yeah "indentation" is causing the issue. I will check with my Delphix manager team for the Plugin details.



    ------------------------------
    Chandrapalred Borra
    TDM Engineer
    HCL America Inc.
    ------------------------------



  • 13.  RE: Masking Json data in Aurora PostGresql table

    Posted 04-10-2024 07:58:00 AM

    You could also use chaining and pass the masked output to java pgm which will fix the indentation/white space issue.



    ------------------------------
    Bhupinder Kwatra
    Senior System Engineer
    Delphix Community Members
    ------------------------------



  • 14.  RE: Masking Json data in Aurora PostGresql table

    Posted 04-24-2024 02:57:00 PM

    Hi @Tino Pironti, I got the Indentation issue fixed with Json plugin, Now I see data is getting copied correctly but data is not getting masked.

    Ex:

    I have JsonPath as: "$['request']['searchBy']['subjects']['subject'][*]['name']['first']" in my Fileformat working fine and masking correctly.

    For the same above I see Json Plugin Doc says I need to set the path in below format for creating Extended Algo with Json Framework:

        {
            "path":"request/searchBy/subjects/subject/*/name/first",
            "type":"String", 
            "method":{ "algo": 
                {"name" : "dlpx-core:FirstName"
                } ,
                "fixed":""
            }
        }

    I tried using wildcard(*) and 0,1,.. both of them didn't wok to mask the json data at all.

    What is the exact conversion of "$['request']['searchBy']['subjects']['subject'][*]['name']['first']" for the path variable ?



    ------------------------------
    Chandrapalred Borra
    TDM Engineer
    HCL America Inc.
    ------------------------------



  • 15.  RE: Masking Json data in Aurora PostGresql table