Test Data Manager

 View Only
  • 1.  Masking XML column in Oracle with SQLFUNCTION with FDM

    Posted Feb 13, 2020 03:47 AM
    Hi, 

    Hi, 

    We have a requirement to mask XML content in an Oracle DB column. 
    We masked using "MASK_TYPE" = FIXED. Filling the "XML / JSON Data" section and "Use Masked Values".

    Data was successfully masked.

    As we need to use now a customize function instead of Fixed value to mask the same value at the same column, we've changed MASK_TYPE = FIXED by an SQLFUNCTION we've already used to mask other non XML values at the same table. Other parameters still the same ("XML/JSON Data" definition and "Use Masked Values")

    Function is working but same value is being applied to the whole column instead of masking only the specified field at the "XML/JSON Data" path.
    Here's the syntax of the SQLFUNCTION:

    SCHEMA.TABLE.FUNCTION(COLUMN_NAME)

    We don't know why it's not applying the sqlfunction to the field we're specifying at the "XML/JSON Data" section.

     

    Maybe it's a long shot but, does anyone has a SQLFUNCTION example to know the syntax and options to apply SQLFUNCTION in a XML column?

     

    Thanks in advanced,

    Jessica.



  • 2.  RE: Masking XML column in Oracle with SQLFUNCTION with FDM

    Posted Feb 13, 2020 04:00 AM
    Hi Jessica,

    Is it necessary to use FDM for this column? If you don't use specific FDM functions such as HASHLOV you could also use direct database updates. In my experience this is much faster. I trigger stored procedures directly from Javelin database elements.

    Best regards,
    Klaas-Jan Lute


  • 3.  RE: Masking XML column in Oracle with SQLFUNCTION with FDM

    Posted Feb 14, 2020 07:24 AM
    Hi @Klaas-Jan Lute

    Thank you for the suggestion. We will need to apply an exception if FDM doesn't support SQLFUNCTION option for XML fields but we would like to use FDM as it's the standard tool for the whole scope of DataBases we need to mask. 

    If FDM supports SQLFUNCTIONS for XML fields, an example will be welcome.

    Thanks again for your help.
    Regards,
    Jessica. 



  • 4.  RE: Masking XML column in Oracle with SQLFUNCTION with FDM

    Broadcom Employee
    Posted Feb 14, 2020 07:59 AM
    Jessica is there a specific reason for using an sqlfunction?  How do you want this particular xml attribute/element masked?


  • 5.  RE: Masking XML column in Oracle with SQLFUNCTION with FDM

    Posted Feb 14, 2020 09:22 AM
    Hi @Billy Keefer,

    The Sqlfunction we're using contains and centralizes the algorithms approved by the security team. Different rules are being applied depending on the content of the field to mask.

    We've already masked some Data Bases applying same sqlfunctions.

    Using other options in FDM for next Data Bases to be masked, would be risky and  time consuming regarding to maintenance of masking rules for each DB/table.


  • 6.  RE: Masking XML column in Oracle with SQLFUNCTION with FDM
    Best Answer

    Broadcom Employee
    Posted Feb 19, 2020 03:05 PM
    Hi @Jessica Astorga  it appears to me that SQLFunctions are just passed thru by FDM.   Have you tried including an XMLQuery in the SQLFunction that describes the ​​rules to be applied to the column / XML node?