Masking Functions and Parameters

ship_to_address_ID in table orders will have a value of 5 added to existing.

customer_ID in table orders will have a value of 10% added to existing value.

unit_price in table order_items will have a value between -4 and 4 added to the existing value.

The test_date column in table credit_cards will have between 10 and 100 days taken off the existing value for that column.

creation_date will have between 6 and 9 hours added to existing value.

creation_date will have between 30 and 45 minutes added to existing value.

creation_date will have between 4 and 13 seconds taken off the existing value.

CREATION_DATE in table OPTIONS_BASE will have between 6 and 9 years added to the existing value.

Note:
The encryption key can not be more than 16 characters in length.

The value 17 Windmill St is decrypted to 4e 23 11 6a 61 30 27 0d 10 f1 08 56 fc dc 13 dc.

Note:
The encryption key can not be more than 16 characters in length.

The value COUP in the discount_type_code column will be masked to 9ccff020641cc1a68770161075cf33.

The age 52 (DOB 9/5/1958) will remain at 52 but could be adjusted by, for example, between 10 and 30 days to 16/5/1958.

The date of death 10/9/2009 will remain at 1 year but could be adjusted by, between Parm1 and Parm2 to, for example, 7/9/2009.

All data in table orders will be deleted.

Column named email will be masked by auto-email IS.

Column ACCOUNT_NUMBER will be masked with the fixed value 11100022 and column PERSON_TYPE_CODE will be masked with the value CUST.

Parm1. This is the mask key.

The mask key needs to be a series of alphanumeric characters - for example AQgt76Wr.

Parm1, Parm2 (Optional).

Parm1 is the seed value for the hash and Parm2 gives the maximum value allowed.

Here numbers column of table TEST1 have been hashed with the seed value of 35 and maximum allowed value of up to 5 digits.

Parm1 and Parm2.

Parm1 contains the start integer value of the range.

Parm2 contains the end integer value of the range. The maximum value for Parm2 is 2147483647. If the database accepts decimal values, you can also use NUMERICRANGE.

Column integer will be replaced by values between 100 and 110 and column integer_nn will be replaced by values between 120 and 200.

Parm1 and Parm2.

Parm1 contains the cross-reference value.

Parm2 contains the default value.

If Parm1 is absent, IGNORE will revert to using the default value set in Parm2. If Parm2 is also absent, IGNORE will ignore the mask and use existing value.

Parm1 (name of the sequence).

Note:
You must have an XREF connection (must be Oracle) set if you use NEXTVAL.

If you called an Oracle sequence ‘FirstSequence’ and used it to update 20,000 fields in one run, the next time it was called, the run would start from 20,001.

Parm1 and Parm2.

Parm1 contains the start value of the range.

Parm2 contains the end value of the range.

Parm1 (Parm2 and Parm3 are optional).

Parm2 is the maximum length of the data.

Parm3 is the minimum length of the data.

Parm1 (Rules for each position are built from the following formulas):

• RDXXXL – Random Digit at Position XXX from left

• RDXXXR – Random Digit at Position XXX from right

• RAXXXL – Random Alphabet at Position XXX from left

• RAXXXR – Random Alphabet at Position XXX from right

• RCXXXL - Random Alphanumeric character at position XXX from left

• RCXXXR - Random Alphanumeric character at position XXX from right

• F1XXXL - Fixed Digit 1 (for example) at Position XXX from left

• F1XXXR - Fixed Digit 1 (for example) at Position XXX from right

• FEXXXL - Fixed Alphabet E (for example) at Position XXX from left

• FEXXXR - Fixed Alphabet E (for example) at Position XXX from right

Note:
If a rule is not given for any position, then the old value is retained post-masking.

Note:
If the old value is null or all blanks, skip the row and move on to the next row.

Column PHONE_NUMBER in Table PEOPLE will have the first 3 characters masked with the fixed value 9, with the remaining digits remaining as original values. Therefore, the resulting masked value will be 999XXXXXX, where X is the existing value

Note:
All of the rules for a column must be set in one row of the CSV, as above. To seperate each positional rule, please use a hyphen (‘-’) without a space.

The data for RANDLOV functions can also be drawn from database tables. Select the columns you wish to use and place them in order in the mapping file (i.e. RD_REF_VALUE2, RD_REF_VALUE3 etc).

Note:
Colums drawn from the database tables DO NOT contain a .txt suffix.

Parm1 (Seed File Name), Parm 2 (position of column in gtrsc_reference_ data) and Parm 3 (the column in the table to get the existing zip code from).

If you have a table like in the example below with PARM3 being the column POSTAL_CODE, which is used to reference the rd_ref values 3, 5, and 4 from the gtrsc_reference data seed table using the rd_ref_id of “US_ADDRESSES” and the postal code stored in rd_ref value.

The data for RANDLOV functions can also be drawn from database tables. Select the columns you wish to use and place them in order in the mapping file (i.e. RD_REF_VALUE2, RD_REF_VALUE3 etc).

Note:
Colums drawn from the database tables DO NOT contain a .txt suffix.

Parm1 and Parm2.

Parm1 contains the start value of the range.

Parm2 contains the end value of the range.

Note:
Parm1 and Parm2 must be provided in the format YYYYMMDD if applied to a date.

Parm1 and Parm2.

Parm1: The minimum length of the text.

Parm2: The maximum length of the text.

RANDOMTXT,3,12. Column will have the value XrzFF. The length of the text string will be between 3 and 12 long.

Columns listed in ‘Column’ will be replaced by a Random Social Security number. Entering a separator character into Parm1 (i.e. *), will generate a social security number like 987*65*4320.

Parm1 and Parm2.

Parm1 contains the character pattern to be searched in the Column. If Parm2 is absent then only Parm1 is the name of the CSV file that contains list of values to be replaced.

Parm2 contains the character pattern to be replaced.

Case 1 : When Parm2 is present.

Here pattern ‘Ab’ when found in the column ‘char’ is replaced by ‘23’, ‘a’ if found in the column ‘char_nn’ is also replaced by ‘23’.

Case 2 : When Parm2 is absent.

Here Parm2 is absent and Parm1 is the name of the CSV file that contains a list of values to be replaced.

The example CSV file could look like shown below. Also please note that REPLACE.csv is placed in the same directory as GTSDM.exe

The example would mask the LAST_NAME (e.g. Smith) as something like “55555SMITH”.

Note:
This function is most likely to be used in conjunction with substr functionality. The string is right justified to the size of the column (if substr is being masked, the column size is assumed to be the end of the substring).

The data for SEQLOV functions can also be drawn from database tables. Select the columns you wish to use and place them in order in the mapping file (i.e. RD_REF_VALUE2, RD_REF_VALUE3 etc).

Note:
Colums drawn from the database tables DO NOT contain a .txt suffix.

Parm1 (Seed File Name), Parm 2 (position of column in gtrsc_reference_ data) and Parm 3 (the column in the table to get the existing zip code from).

If you have a table like in the example below with PARM3 being the column POSTAL_CODE, which is used to reference the rd_ref values 3, 5, and 4 from the gtrsc_reference data seed table using the rd_ref_id of “US_ADDRESSES” and the postal code stored in rd_ref value.

Parm1. This is the category in the seedtable in which your list of values is saved, for example, MY_ADDRESSES containing Address, City and Postcode.

Parm2. This is the column in the seedtable you wish to place the value in, for example, entering 1 would select RD_REF_1, which in this example is Address.

In order for the function to run, you will need to use the following (For more information on this, please go to the Options File section on the manual):

• SEEDTABLECONNECT=connectscramble.txt

• SEEDTABLE=gtsrc_reference_data

• SEEDTABLECOLUMNS=RD_REF_ID, RD_REF_VALUE1, RD_REF_VALUE2 etc

Notes:

1. Do not use the name of an existing seed table.txt file as this will be overwritten each time it is run.
2. This function can only be used on databases not flat files.

Parm1, the SQL function or SQL statement.

Parm1=first_name || ‘ ‘|| last_name. This will concatenate first name, a space, and a last name.

Parm1=mynumberformat(HHNO). This will pass HHNO into the database function. The returned value will populate the masked column.

Column TAXID in table PEOPLE will be masked with a generated US Tax Identification Number.

Parm1 and Parm2.

Parm1 contains the character(s) to be searched in the Column.

Parm2 contains the corresponding character(s) to be replaced.

All instances of ‘a’ in column FIRST_NAME will be translated to ‘x’, and all instances of 1 in column MEMBERSHIP_ID will be translated to 6.

Parm1.

It contains the key of the transposition.

‘ab’ if found in the column value will be translated to ‘ef‘ i.e. every ‘a’ character translated to ‘e’ and every ‘b’ character translated to ‘f’. This will vary depending on the key.

Truncating the data will execute a fast delete of ALL the data in the table.

Column named usphone will be masked by auto-generated 7-digit US Phone numbers.

Column named usphone10 will be masked by auto-generated 10-digit US Phone numbers.

Column named uszip will be masked by auto-generated 5-digit US Zip code.

Column named uszip will be masked by auto-generated 9-digit US Zip code.

Column ID in table PEOPLE will be replaced by a Random Social Security number, if a valid Social Security Number is found in the column.

Column ID in table PEOPLE will be replaced by a Random Social Security number, if a valid Social Security Number is found in the first 9 characters of the column.

None.

Column ID in table PEOPLE will be replaced by a Random Tax Identification Number, if a valid TIN is found in the column.

Parm1, Parm2(Optional), Parm3(Optional).

Parm1 gives percentage variance (1 - 99).

Parm2 is the minimum permitted value.

Parm3 is the maximum permitted value.

If the column value is 100 then Parm1 applies 60% variance and a random number is generated between 40 and 160. However Parm2 (minimum permitted value) and Parm3 (maximum permitted value) ensures that the generated random value lies in the range 50 - 150 instead of 40 – 160.

Parm1.

Parm1 is an SQL WHERE clause. It is worth running the WHERE clause in a standard SQL IDE to validate the syntax is correct.

If the MSP_CODE is like HSD

Note:
SDM currently supports the following operators: <, <=, =, >=, >, and LIKE (LIKE is not supported for flat files. It is only supported for database masking).

If you wish to issue multiple WHERE clauses, repeat the WHERE function. Each WHERE function will refer to the block of masking functions BELOW it in the CSV.

In the above example, all of the columns HELP_TEXT will be masked with random values from companies.txt; the rows with an MSP_CODE starting with ‘HSD’ will have the value huw assigned to CREATED_BY; and the rows with an MSP_CODE starting with ‘OFG’ will have the value fred assigned to CREATED_BY and a random value from the lastnames.txt file assigned to column MESSAGE_TEXT.