Masking JSON data stored in a SQL Server table

Products

• CA Test Data Manager (Data Finder / Grid Tools)

Issue/Introduction

Is it possible to obfuscate JSON stored in a column in SQL server database? Below is an example. We need to mask the data stored in COL3.

 

Example provided:

COL1	COl2	COL3
1	TEST1	"Id":"1234","ID2":"ABC","ID3":"100CC","ID4":"B123","ID5":null,"IsWhatIf":false,"ValueType":null
2	TEST2	"Id":"789","ID5":"ABZ","ID3":"10CC","ID4":"Z123","ID5":null,"IsWhatIf":false,"ValueType":null

Environment

FastDataMasker 4.10

Resolution

Make sure the data stored in the SQL Server column is a valid JSON document. In the example provided, the JSON data in COL3 needs to be placed inside {} brackets. If the data is not in a valid JSON format, then FDM may not find the proper JSON paths.

To mask the JSON data in the SQL Server COL3, for example:

1. Connect FDM to the SQL Server Table.
2. Select the column to mask, in this case COL3.
3. Choose the Masking Function to use
4. Check the XML/JSON Data checkbox
5. In the xpath dropdown, select the JSON path you wish to mask.
6. Repeat, if multiple JSON paths in the column need to be obfuscated.

See the screenshot below:

Additional Information

For more information, see:

Defining Masking Rules - https://techdocs.broadcom.com/us/en/ca-enterprise-software/devops/test-data-management/4-10/provisioning-test-data/mask-production-data-with-fast-data-masker/mask-stored-data/mask-data-stored-in-relational-databases/define-masking-rules.html