Skip to main content
Delphix

Algorithm: Casting Values Before Masking (KBA1580)

  1. Last updated
  2. Save as PDF

 

KBA

KBA#1580

At a Glance

Description: This KBA describes how to CAST a value before masking using Custom SQL. The KBA also covers some examples and troubleshooting steps.
Affects versions: This KBA is applicable to all versions of the Masking Engine.
Location: UI: [Environment] > Rule Set > [Edit] > Custom SQL
Custom SQL: The SQL in Custom SQL need to fulfill the following requirements:
  • be a SELECT statement.
  • include the field name for any field cast.
  • (In-Place only) include the Unique Row Identifier (URI) such as ID, ROWID (Oracle), etc.
Troubleshooting: If there is an error, the error either appears in one or both of these areas.

Counting Rows:
  
info.log: SELECT COUNT(*) ...


Masking Operation (reading data):

DelphixTableInput.0
  
Table input.0
More logs: KBA: How to Troubleshoot Masking Jobs and Access Masking Logs (KBA7988)

Issue

This article describes casting values before masking. You may need this:

  • When there is a conversion error in the Masking Job.
  • When the data has leading or trailing white spaces.
  • When the data needs to be standardized before - i.e. all UPPER case.
Note

Note:

The two last issues are changing how data is masked (hence they will not generate an error message). 

 

Applicable Delphix Versions

Click here to view the versions of the Delphix engine to which this article applies
Major Release All Sub Releases
6.0 6.0.0.0, 6.0.1.0, 6.0.1.1, 6.0.2.0, 6.0.2.1, 6.0.3.0, 6.0.3.1, 6.0.4.0, 6.0.4.1, 6.0.4.2, 6.0.5.0, 6.0.6.0, 6.0.6.1, 6.0.7.0, 6.0.8.0, 6.0.8.1, 6.0.9.0, 6.0.10.0, 6.0.10.1, 6.0.11.0

5.3

 5.3.0.0, 5.3.0.1, 5.3.0.2, 5.3.0.3, 5.3.1.0, 5.3.1.1, 5.3.1.2, 5.3.2.0, 5.3.3.0, 5.3.3.1, 5.3.4.0, 5.3.5.0, 5.3.6.0, 5.3.7.0, 5.3.7.1, 5.3.8.0, 5.3.8.1, 5.3.9.0

5.2

5.2.2.0, 5.2.2.1, 5.2.3.0, 5.2.4.0, 5.2.5.0, 5.2.5.1, 5.2.6.0, 5.2.6.1

5.1

5.1.0.0, 5.1.1.0, 5.1.2.0, 5.1.3.0, 5.1.4.0, 5.1.5.0, 5.1.5.1, 5.1.6.0, 5.1.7.0, 5.1.8.0, 5.1.8.1, 5.1.9.0, 5.1.10.0

5.0

5.0.1.0, 5.0.1.1, 5.0.2.0, 5.0.2.1, 5.0.2.2, 5.0.2.3, 5.0.3.0, 5.0.3.1, 5.0.4.0, 5.0.4.1, 5.0.5.0, 5.0.5.1, 5.0.5.2, 5.0.5.3, 5.0.5.4

How to Use Custom SQL

To cast a value in a column:

  1. Open the tab Rule Set
  2. Click on the Edit icon for the table to update:

Masking_UI_-_Rule_Set_-_Edit_Table.png​​​

  1. Select Custom SQL.
  2. Edit the SQL statement.
    An example is highlighted below. For more examples see Cast Examples below.

Masking_UI_-_Rule_Set_-_Edit_Custom_SQL.png

  1. Click Save.

 

Note

Notes:

When editing the statement, keep the following in mind:

  • The SQL statement needs to be a valid SELECT Statement. 
  • If the columns in the table change, then the SQL needs to be updated.
     
  • In-Place Masking:
    • Only the URI and Masking Columns are needed in the SELECT statement.
    • Including other columns could degrade the performance. 
       
  • On-The-Fly Masking:
    • All columns need to be included. 
    • For large tables, the SQL could be very long. 

Examples

Below are some examples of functions that cast (change the data type of) a value. In many cases, this can resolve issues where the algorithm is not supporting a specific data type or how the data is presented - i.e dates, decimal numbers, currencies, etc. 

All examples are In-Place and use ID as URI (ROWID for Oracle). 

Please refer to database documentation for syntax and more examples. 

Value to String 

If you encounter an issue due to a value being a number, then casting the value to a string will likely resolve the issue. When casting, it is possible to specifically change the data as needed using arguments.

Cast Number to a String (str) 

For more information, check product documentation or Google.  

Syntax: 

STR(number[, length[, decimals]])

Example: 

SELECT ID, STR(mask) as mask from myTable;
Cast Number to a String (to_char) 

Oracle example. 

A versatile option to cast a numeric value to a string.

For more information, check product documentation or Google.     

SELECT ROWID, TO_CHAR(mask,'90.99') mask from myTable;
Cast Date to a String

Use this if the date needs to be converted to a special format before masking. 
 

Algorithm example: (any) Date.

Oracle: SELECT ROWID, TO_CHAR(mask, 'YYYY/MM/DD') mask from myTable;
SQL Server: SELECT ID, convert(varchar, mask, 111) as mask from myTable;
Cast using cast

For more information, check product documentation or Google.  

Syntax: 

CAST(expression AS datatype(length))

Example: 

SELECT ID, CAST(mask as varchar) as mask from myTable;

Clean Data

These examples change to data by standardizing it or removing unwanted spaces - cleaning the data - before masking.

Set to UPPER Case 

Use this if the algorithm is CASE sensitive and John and JOHN should be masked to the same masked value.

SELECT ID, UPPER(mask) as mask from myTable;
Trim Whitespaces  

Use this if the algorithm is sensitive to leading/trailing white spaces.

This is especially problematic when the datatype is CHAR or NCHAR as these will by default have trailing spaces. 

Oracle and SQL Server Example.

SELECT ID, LTRIM(RTRIM((mask)) as mask from myTable;

Random Value

Using Newid()

SQL Server example.

Sets all values to a random string like: '25F4A154-9B37-4A51-9B77-57118AF23FD6'.

Can also be used with LEFT() to reduce size.
 

Algorithm example: Secure Lookup.

SELECT ID, Newid() as mask from myTable;
Using random

Oracle example.

Sets all values to a random string like: 'STLHCKIPIA'.
 

Algorithm example: Secure Lookup.

SELECT ROWID, dms_random.string('U', 10) mask from myTable;

Reduce Field Size

If a large text or binary object is masked, it is often not necessary to read and transfer all data to the masking job to mask or redact it.

Set to NULL

Sets all values to NULL.

This results in a very small amount of data transferred which improves performance. 
 

Algorithm example: Use with NULL SL.

SELECT ID, NULL as mask from myTable;
Set to 'x'

Sets all values to 'x'.

This results in a very small amount of data transferred which improves performance. 
 

Algorithm example: User Secure Lookup (one value) 'The data has been masked and redacted'.

SELECT ID, 'x' as mask from myTable;
Set to Checksum

SQL Server example.

Sets all values to a calculated checksum, like '34472462'. 

Two rows with the same value will be masked to the same masked value. 
 

Algorithm example: Secure Lookup. 

SELECT ID, CHECKSUM(mask) as mask from myTable;
Set to Trimmed Hash value

SQL Server example.

Two rows with the same value will be masked to the same masked value. 

Sets all values to a hash value. This also trims the value to 10 chars. Example: 'â<‡ÅߎÀu4'.
 

Algorithm example: Secure Lookup.

SELECT ID, LEFT(HASHBYTES('SHA2_256',mask),10) as mask from myTable;

Related Articles

 

 

Privacy Policy Terms of Use Legal About
© 2022 Delphix Corp