xml column obfuscation using FDM

[Ashish Jaiswal]

Hi,

We have column having xml data present in SQL and Oracle databases. is there any way to obfuscate it using FDM?
Is there any way to replace only PII fields present in the column.

Thanks,
Ashish Jaiswal

[Scott Schmitz]

Hi Ashish,

Yes, FDM can detect the XML schema structure when you select the XML/JSON data checkbox.  You can select the XML node you wish to mask.

I had created a Library entry focused on Oracle CLOB/BLOB masking (including embedded XML) here:
https://community.broadcom.com/enterprisesoftware/viewwebinar/oracle-clob-and-blob-masking?CommunityKey=909e0526-d8ad-455d-97eb-aa009df5d3aa&tab=librarydocuments

[Ashish Jaiswal]

Hi Scott,

Thanks for sharing the video on CLOB/BLOB masking.

When I tried running the obfuscation on xml data for one tags as per the steps mentioned in the video, I got the below error.

>2020-08-21 17:09:03.418 Masking will be as follows
>2020-08-21 17:09:03.418 ***************************
>2020-08-21 17:09:03.418 table lexisnexis_test and column xmlData will be masked by hashing the current value and using this to consistently pick from a seed list
>2020-08-21 17:09:03.418 The seed value will be from seed data file Asset First Name.txt
>2020-08-21 17:09:03.418 ******************************
>2020-08-21 17:09:03.418
>2020-08-21 17:09:03.434 Table lexisnexis_test has no unique columns defined
>2020-08-21 17:09:03.434 Updating may be unreliable and take a long time
>2020-08-21 17:09:03.575 java.lang.NullPointerException
> at com.grid_tools.products.datamasker.Datamasker.addtomasked(Datamasker.java:17313)
> at com.grid_tools.products.datamasker.GenFunctions.processXML(GenFunctions.java:1996)
> at com.grid_tools.products.datamasker.Datamasker.resolveFunction(Datamasker.java:13548)
> at com.grid_tools.products.datamasker.SQLNoIndexUpdate.nonUniqueMasking(SQLNoIndexUpdate.java:1310)
> at com.grid_tools.products.datamasker.Datamasker.doMasking(Datamasker.java:10287)
> at com.grid_tools.products.datamasker.Datamasker.main(Datamasker.java:2539)

Thanks,
Ashish

[Scott Schmitz]

Sorry to hear of that.    Can you please open a ticket with Support, asking for the download link to the latest FDM patch for the version of Test Data Manager that you are running so we can see whether this issue has been corrected already?

[Ashish Jaiswal]

Hi Steve,

I am using FDM version 14.9.46.0 which I got in the beginning of this month after raising the case support.
I'll raise again a new case and request for the latest FDM patch.

Thanks,
Ashish

[Scott Schmitz]

Ashish, I did a quick validation with 4.9.37 and the latest 4.9.51 and they both worked ok for me on an Oracle CLOB with embedded XML.
Can you provide some more detail about your environment so I can try to reproduce?
Database Type / Version (e.g.  Oracle 12.2)
The specification of the xmlData column in the lexisnexis_test table  e.g. VARCHAR(50)
A sample (sanitized) record from that column.

Do you see any more detail if you change the DIAGLEVEL to 2 in the Options file?

[Ashish Jaiswal]

Hi Scott,

I am trying to obfuscate xml data present in SQL database.
SQL database version - 13.0.4259.0
Table - lexisnexis_test, column - xmlData (Varchar (max),null)
Sample (sanitized) record value - 
<?xml version="1.0" encoding="utf-16"?><Result xmlns:xsd="http://xxxxxxxxxxx" xmlns:xsi="http://XXXXXXXXXXX"><Summary><Status>true</Status><ID>1322028045</ID><IKey>1547030987</IKey><equifaxUsername /><Reference>52852a37-3a55-43bd-bf3d-00f7d3ae2db6</Reference><Scorecard>APIScore</Scorecard><Smartscore>0</Smartscore><ResultText>PASS</ResultText><ProfileURL>https://xxxxxxxxxxxxxx</ProfileURL><Credits>423474</Credits></Summary><Address><Forename>ASHISH</Forename><MiddleName>A</MiddleName><Surname>JAISWAL</Surname><MatchType>Partial</MatchType><DOB>1989-09-08</DOB><ForenameAppended>false</ForenameAppended><MiddleNameAppended>true</MiddleNameAppended><DOBAppended>false</DOBAppended><Telephone>XD</Telephone><Telephonename>T JAISWAL</Telephonename><GoneAway>N</GoneAway><Source>ER2004,ER2005,ER2009,ER2010,ER2011,ER2012,ER2013,ER2014,ER2015,ER2016,ER2017,ER2018,TR</Source><Recency>2017-10-15</Recency><Occupants><OccupantPart><Name>MRS ASHISH A JAISWAL</Name><DOB>1989-09-08</DOB><Recency>2017-10-15</Recency><Residency>2003</Residency><ResidencyScore>VH</ResidencyScore><Telephone>XD</Telephone><TelephoneName>T JAISWAL</TelephoneName><Source>ER2004,ER2005,ER2012,ER2014,ER2015,ER2016,ER2017,ER2018,TR</Source></OccupantPart><OccupantPart><Name>MR ABHIJIT N JAISWAL</Name><DOB>1990-02-04</DOB><Recency>2017-10-15</Recency><Residency>2003</Residency><ResidencyScore>VH</ResidencyScore><Telephone>XD</Telephone><TelephoneName>T JAISWAL</TelephoneName><Source>ER2004,ER2005,ER2012,ER2014,ER2015,ER2016,ER2017,ER2018,RegistryTrust,TR</Source></OccupantPart></Occupants><Property><PropertyPart><Type>Detached</Type><Tenure>F</Tenure><Date>2002-11-08</Date><Price>143500</Price><Silhouette>C21</Silhouette></PropertyPart></Property><AddressValidated>true</AddressValidated><Phonematch><string>01270505593</string><string>079845749658</string></Phonematch></Address><DOB><TracesmartDOB>1</TracesmartDOB><ExperianDOB>9</ExperianDOB><ExperianDOBSpecified>true</ExperianDOBSpecified><EquifaxDOB>9</EquifaxDOB><EquifaxDOBSpecified>true</EquifaxDOBSpecified><EquifaxDOBStatus>Y</EquifaxDOBStatus></DOB><Crediva><FullER>true</FullER></Crediva><CreditActive><InsightAccounts>12</InsightAccounts><InsightAccountsSpecified>true</InsightAccountsSpecified><InsightLenders>9</InsightLenders><InsightLendersSpecified>true</InsightLendersSpecified><CAISLenders>9</CAISLenders><CAISLendersSpecified>true</CAISLendersSpecified><ActiveInsightAccountsSpecified>false</ActiveInsightAccountsSpecified><ActiveInsightAccountsMonth1Specified>false</ActiveInsightAccountsMonth1Specified><ActiveInsightAccountsMonth2Specified>false</ActiveInsightAccountsMonth2Specified><ActiveInsightAccountsMonth3Specified>false</ActiveInsightAccountsMonth3Specified><ActiveInsightAccountsMonth4Specified>false</ActiveInsightAccountsMonth4Specified><ActiveInsightAccountsMonth5Specified>false</ActiveInsightAccountsMonth5Specified><ActiveInsightAccountsMonth6Specified>false</ActiveInsightAccountsMonth6Specified><ActiveInsightAccountsMonth7Specified>false</ActiveInsightAccountsMonth7Specified><ActiveInsightAccountsMonth8Specified>false</ActiveInsightAccountsMonth8Specified><ActiveInsightAccountsMonth9Specified>false</ActiveInsightAccountsMonth9Specified><ActiveInsightAccountsMonth10Specified>false</ActiveInsightAccountsMonth10Specified><ActiveInsightAccountsMonth11Specified>false</ActiveInsightAccountsMonth11Specified><ActiveInsightAccountsMonth12Specified>false</ActiveInsightAccountsMonth12Specified></CreditActive></Result>

WIth DIAGLEVEL=2, it is showing the same exception as shared above.

Could you please create a table with above details and share your feedback.

Thanks,
Ashish

[Scott Schmitz]

Hi Ashish,

I was able to reproduce what you are seeing with FDM 4.9.51.   I then defined a Primary Key on the table (your FDM output states "Table lexisnexis_test has no unique columns defined") and the masking completed without issue.   I recommend you open a Support ticket with your findings, and, if possible, apply a unique index on the table as a workaround so you may proceed with masking.

Scott

[Ashish Jaiswal]

Hi Scott,

Yes, it is working now after after creating primary key on the table. It is obfuscating tags with hash values now.
Thanks a ton :)

I have one other question. 
We have oracle database where all tables has XML data into it and the data type is "XMLTYPE". I tried using the same method for identifying the tags but it is showing as blank  in XML/JSON data. Could you please share your thoughts here.

Thanks,
Ashish

[Scott Schmitz]

Hi Ashish,

Happy to hear of the success on the SQL Server xml data.

I'm unsure whether we support masking of Oracle XMLTYPE, as it requires a special commandset to select/manipulate the data.   I'd recommend opening a support ticket asking whether it is supported for masking.   If it is, we'll troubleshoot what the issue is.   If it isn't, then Engineering will know of your interest and add it to the backlog with your company tagged on it.

Scott

[Ashish Jaiswal]

Thank you Scott for all your support.

I'll raise a support ticket on ORACLE XMLTYPE datatype obfuscation.

Regards,
Ashish

[RaviTeja CH]

Hi Scott Schmitz,

I have the same similar scenario, Added the unique key too. After this when i triggered masking, Masking is getting completed, but values are not getting updated. 

DB Type: XML
Table Columns, ID (Unique key), XML (To be masked)
DB ; SQL Server
FDM version - 4.8

Regards,
Ravi Teja CH

[RaviTeja CH]

Hi Scott Schmitz,

Logs for your reference. 
2021-05-13 07:17:48.449 java.lang.IllegalArgumentException: The default (no prefix) Namespace URI for XPath queries is always '' and it cannot be redefined to 'http://XXXX.com/gx/XXX.cc.messages.XXXXX.XXXXl'.
at org.jdom2.xpath.util.AbstractXPathCompiled.<init>(AbstractXPathCompiled.java:144)
at org.jdom2.xpath.jaxen.JaxenCompiled.<init>(JaxenCompiled.java:148)
at org.jdom2.xpath.jaxen.JaxenXPathFactory.compile(JaxenXPathFactory.java:82)
at org.jdom2.xpath.XPathFactory.compile(XPathFactory.java:261)
at com.grid_tools.products.datamasker.genfunctions.processXML(genfunctions.java:1257)
at com.grid_tools.products.datamasker.Datamasker.resolveFunction(Datamasker.java:15057)
at com.grid_tools.products.datamasker.SQLUpdate.batchPreparedMasking(SQLUpdate.java:4170)
at com.grid_tools.products.datamasker.SQLUpdate.run(SQLUpdate.java:5444)
at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1149)
at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:624)
at java.lang.Thread.run(Thread.java:748)

Regards,
Ravi Teja CH

[Scott Schmitz]

@RaviTeja CH

Your FDM version (4.8) is too old to support what you are wanting to do.   Please open a ticket with Technical Support to request the latest Fast Data Masker and you should have better results.

Scott​

[Gene Howard]

There is no need to open a ticket.
The latest version can be found on the below link.
TDM Support Patches Broadcom Support Portal

------------------------------
Gene Howard
Principal Support Engineer
Broadcom
------------------------------