Using Custom SQL (KBA1764)

select UniqueID, MaskedCol from MaskedTable

Required Columns

1. Unique Identifier for the table.
   This should be either an
   • unmasked unique field in the table
   • unique identifier if added by the masking process. 
2. Masked Columns with the correct column names.
3. Any Other Column specified will be ingested but not used.

 

Note:

 Remove Columns that are not used.

It is highly recommended to remove all columns that are not needed to mask the data as all columns added are loaded into memory and will use job memory.
This will also slightly slow down the masking job. There are many cases where large objects being part of the columns have generated Out of Memory errors.

select MaskedCol, AllOtherCol from MaskedTable

Required Columns

1. Masked Columns with the correct column names. 
2. All Other Columns that are needed in the new table (which is usually all columns). 

Since all columns are generally needed in On-The-Fly masking, this can mean that these Custom SQL can be quite complex. It might also require more memory to hold all columns in memory.

 

Note:

 No ID column(s) are needed in On-The-Fly masking. This is because records are inserted instead of updated.

Oracle - ROWID 

If the database is Oracle, use the ROWID key as identified. Its a good practice to use this all the time for Oracle.

select ROWID, MaskedCol from MaskedTable

MS SQL Server - DMS_ROW_ID 

On MS SQL Server, if there is no Unique Key a key will be temporarily added by the Masking Engine called 'DMS_ROW_ID'. If the Custom SQL is used this needs to be added in the Custom SQL statement even if the Masking Engine is adding it to the Table. 

select DMS_ROW_ID, MaskedCol from MaskedTable

All values in the column to a string constant  

... 'string' AS MaskedCol ...

Example: 

select UniqueID,'Redacted text object' AS LeagalOutcome from MaskedTable

select UniqueID, 'xxxx-xxxx-xxxx-xxxx' AS CreditCard from MaskedTable

All values in the column to a number 

... value AS MaskedCol ...

Example:

select UniqueID, 1 AS Salary from MaskedTable

Cast - number 

• Cast value to a 4 digit decimal number. 
• For example 9.5 to 9.5000.

... CAST(MaskedCol AS decimal(6,4)) AS MaskedCol ...

Convert - number 

• Same as above but with CONVERT.
• Converts a number to 4 digit decimal number.

... CONVERT(decimal(MaskedCol), 9.5) AS decimal AS MaskedCol ...

Parse a string to money 

This example uses parse to convert a string to a value of type money - for example '($1,000.00)'. 

... PARSE(MaskedCol AS MONEY) AS MaskedCol ...

Convert - character Set 

This is shown as an example. It will not resolve character conversion errors. It might though be able to handle some conversions prior to performing masking. 

... CONVERT(MaskedCol, 'US7ASCII', 'WE8ISO8859P1') AS MaskedCol ...

Case  - When - Then - Else 

This can be nested and multiple WHEN clauses can be specified after each other. 

Syntax:

CASE
   WHEN when_expression THEN result_expression [ ...n ]         
   [ ELSE else_result_expression ]
END

Example:

select 
   UniqueID, 
   CASE WHEN MaskedCol is NULL THEN 1000 ELSE MaskedCol END as MaskedCol 
from 
   MaskedTable

IIF  

IIF is a shorter one-liner similar to the Excel function.

MS SQL Server -Syntax:

... IIF ( boolean_expression, true_value, false_value ) AS MaskedCol ...

Example:

select UniqueID, IIF(MaskedCol is NULL, '', MaskedCol) AS MaskedCol from MaskedTable  

COALESCE 

A much better way for these examples though is to use COALESCE. It returns the 'first non-null value in a list of expressions'.  

Syntax:

... COALESCE (expr1, expr2) ...

Example:

select UniqueID, COALESCE (MaskedCol, 'Change Null to this') AS MaskedCol from MaskedTable

Change string to Upper/Lower 

This is a useful function as the masking engine is case sensitive and Secure Lookup will mask 'Peter' and 'PETER' to different values. 

... UPPER(MaskedCol) AS MaskedCol ...

... LOWER(MaskedCol) AS MaskedCol ...

Example:

select UniqueID, UPPER(FirstName) as FirstName from MaskedTable

Concatenate Strings and using Right, Left, SubString 

A specific use case for this is if the data in the column is not adhering to a pattern (for example values 0001 is stored as 1 in the database). It can also be that two values in the database need to be concatenated before masking. 

First the syntax - CONCAT:

... CONCAT ( string_value1, string_value2 [, string_valueN ] ) ...

Syntax - RIGHT, LEFT, SUBSTRING

... RIGHT ( character_expression , integer_expression ) ...

... LEFT ( character_expression , integer_expression ) ...

... SUBSTRING ( expression ,start , length )  ...

How it would be used with MaskedCol:

... CONCAT(MaskedCol, ' added string') AS MaskedCol ...

Examples:

select UniqueID, CONCAT(FirstName, " ", LastName) as FullName from MaskedTable

select 
   UniqueID, 
   RIGHT(CONCAT('0000', CAST( MaskedCol as VARCHAR(10))),4) as MaskedCol
from MaskedTable 

Trimming white spaces 

The last and very useful functions are LTRIM and RTRIM. These will remove any leading or trailing white spaces - which can cause different masking results. 

 

Note:

 All columns of datatype CHAR have trailing white spaces.
A string stored in a column with CHAR and with VARCHAR is different. CHAR is of fixed length and will have trailing spaces while VARCHAR  is of variable length and will usually have no spaces after the string.

For example CHAR(10): "PETER     " and VARCHAR(10): "PETER" 

... LTRIM(MaskedCol) AS MaskedCol ... 

... RTRIM(MaskedCol) AS MaskedCol ...

Example:

These can be added together like in this example.

select ID, LTRIM(RTRIM( MaskedCol )) AS MaskedCol FROM MaskedTable