search cancel

How can we achieve constancy using the FormatEncrypt function

book

Article ID: 228775

calendar_today

Updated On:

Products

CA Test Data Manager (Data Finder / Grid Tools)

Issue/Introduction

Within our environment, we have had many databases added and assimilated due to several mergers and acquisitions of companies that were in a similar business.
As a result, we often come across the use of different data type for the same field e.g. TIN (tax identification number) for any provider.
Whereas in some places it is defined as VARCHAR2(9-10 digit), in some places it is defined as (NUMBER).
This poses a serious challenge to our data, as our masking does not return consistent results. 

Can you suggest a way this inconsistency be overcome while using FormatEncrypt function?

Environment

TDM 4.9
Test Data Manager
FDM 
Fast Data Masker

Component :

Resolution

Option one:
for FORMATENCRYPT1 we implemented the new option "Mask as Number" which allows producing the same outputs for numeric and text data types (in particular the same rules for numeric columns - preserving leading zeros and first digit - are applied to text columns).

Example:
if you have 123 stored in a numeric column and a text column with the option to mask as a number they will end up with the same mask.

Option two:
use FORMATENCRYPT in SCRAMBLE by using SQLFUNTION to overcome this... which could need some additional operations depending on the data.. i.e. convert, substring etc

https://chat.google.com/u/0/api/get_attachment_url?url_type=FIFE_URL&content_type=image%2Fpng&attachment_token=AAUuIGtUscaXio1fJFxPvub9l%2BM%2F8LxMH32f9rRyyYjeU%2FXYS39kjufYqLdPqU6c86iVNMXsjhGhiQdk39zvbVZoN7gCYFYFt6krQfApx3ee3Sbcm6HI9ecQFEtOa%2BGsGURZK%2BcL2T%2FBGftFWO0MuEPZGtdqObxPGkjcCs0xneXv0LGa62kV9YjsWDOgEQhgH8Y6ug3hu36naJy%2B%2FTEEF2OIiv3vmxGpVmP8WJHcVwktrqwKCMcPDKu1Z8o10jPLcp3YWVHvq%2B6HW1kzG2kSlAkCJc2bpvZPjO%2BXwL2JJSt0bbALrre6I6HHxIJd9xqPm3503qXU2WuF1fWdMe0Vu0A5D%2Fj9oN%2B5kjNViVTPE19JlDkdNoXHn1fy8z6Ckb1fHefRxxHPe%2BNhi1PsEp%2FMLWGqF4YmgomyCiVF%2BQ%3D%3D&sz=w512

Before masking

https://chat.google.com/u/0/api/get_attachment_url?url_type=FIFE_URL&content_type=image%2Fpng&attachment_token=AAUuIGsBhIWYU9BweNzbENEks%2FpwBUcIB0Y5h%2BmYaNQvcvvhHXKqwhE814muKqIgR42e6aCPL0rlVy0TVVA6ej7G94WqMLZsZhQFAZ2jJMHAnfAkDEoXqhVDqkmc7zfH%2FRjdAg%2BD5NXkFEiLY8eR1Lb9OBbSjGmaXIxloYgWxbDD0krpUhYy4b4oi%2BnQiOXgO79PiQg2oWwuRWQN3aCL4yhsFp8V0RGovn9AT8jkHM1Li%2Fmcu44MZLLcDEqibSAKH%2FAhN3epDTgTIEki21WrK4nucjb2gOLQMsQDaSkGaRPhbtQDCSECYkC0w581dxkZmLtiiC9puiM0Ygpko8l1SU0TIf5EZpV3V1UNnI1pePcVV0Es3j5fgNElzpB9LKyVcBj2iyDz86fVBXinXt35mPLxrY3QHJK0HmY%2Bxg%3D%3D&sz=w512

After executing "SELECT  SCRAMBLE.gtsrc_scramble2('LOB_TABLE2.ID',NULL,16,'Y',rownum,'N',null,'N',null,ID,'FORMATENCRYPT',0,0) ID,
DOC,
CLOB_COL,
SCRAMBLE.gtsrc_scramble2('LOB_TABLE2.CHAR_COL',NULL,16,'Y',rownum,'N',null,'N',null,CHAR_COL,'FORMATENCRYPT',0,0) CHAR_COL
 FROM TRAVEL_E.LOB_TABLE2;"

https://chat.google.com/u/0/api/get_attachment_url?url_type=FIFE_URL&content_type=image%2Fpng&attachment_token=AAUuIGsGhRJWYEANDP5rzkz5ggXrPdwH9ZovOm2hKKOqxAdVDYd%2B2wc1TL%2F0%2BVmP7fTsNtepiSVELgkce%2BewneOh%2FniXPjUolwa73KoLW9i%2FLrG9V7FA%2FBc3uUnXX9PpjeE2Mr%2FAAJDjFQOl72aTp8YbOeIKRoPEGxoosCFN44kpaDS15MZ00G9pz%2FTY%2BggtHFNGZ0C2XvMzRMk%2FpEBYLwKIlrzLrlshT0z5D9S%2BTDzPEokPExAR%2FhnMTXXfXUJxU1g1eu%2F9QojqxmCxW9F9%2Bt5UUmPWPqUFhvw2TusXSweVhTo%2B7tM6ckRAK92%2FW6SP9qYkQN%2FDIQEc6x%2F9Mz17J%2FpsVTP9GhEwAx8LGOx3RzSkpR4xaYCTRt2glhBZKDwEvd1WnHhVcsbTsnvtjGUAZPg1p2BK6Zpop73Bhg%3D%3D&sz=w512

Additional Information

1) the gtsrc_scramble2 is an additional function that can be created on an oracle scramble database
2) Please see the below documentation:
https://techdocs.broadcom.com/us/en/ca-enterprise-software/devops/test-data-management/4-9/provisioning-test-data/mask-production-data-with-fast-data-masker/data-scrambling.html

3) You would need to add the scramble database with this function to your oracle server so the same connection can be used for the masking table as well as to reach this function on the scramble database.
4) You could create a temp database and add the table as in the example provided here.
5) install the scramble database to the same server and grant the user permissions to it.
6) then create the function if you have not already.
7) then run the masking example query.