User SQL Value Mask

The User SQL Value mask provides a great deal of flexibility because it gives you the ability to define your own masking function if none of the built-in DataVeil masking functions are suitable.

Simply specify the custom SQL code to return a value.

If the masking function requirement is simple then the SQL code can be entered directly into the User SQL Value mask panel as shown below in the example for masking email addresses.

Alternatively, if the required masking function is more sophisticated then you can create a User Defined Function (UDF) on your DBMS (e.g. using PL/SQL on Oracle or Transact-SQL on SQL Server) and call it using a DataVeil User SQL Value mask. You could also use DataVeil's Pre-Masking and Post-Masking scripting capabilities to upload your UDF during the masking setup phase and to drop the UDF after masking completion. You could also define the UDF as a reusable Macro Component and/or a reusable User SQL Value Mask Component.

It is important that the SQL code returns only a single value for each row otherwise you will likely get an SQL runtime error.

This mask can also be used within an XML mask in which case there are limitations. Please refer to The XML Mask for details.

Referencing Masked Values of Other Columns

When referencing other columns that are being masked be sure to specify whether you want the User SQL Value mask to reference the original or the masked values in those other columns.

If you want to use the masked values of a column then you must enclose that column name within curly braces { } otherwise the original values shall be assumed.

If you are including the DBMS identifier delimiter characters (SQL Server [ ], Oracle " ") then these must also be specified within the curly braces (not on the outside of the curly braces).

Example: SQL Server:    {firstName} or {[firstName]}

Example: Oracle:            {firstName } or {"firstName"}

Only the column name must appear within the braces. i.e. Do not include the table name or table alias within the braces..

If you want to reference columns in a different table to where the mask is defined then you will need to specify a Join and qualify the column reference using the Join's Table Alias. See the section "Joining to Other Tables" below.

Using an Inline SQL Expression

For example, let's construct email addresses using a simple SQL expression.

Suppose that you have a table containing columns firstName, lastName and email and that we have already defined masks for firstName and lastName.

Now, we would like to create an email address using the masked values of firstName and lastName and with our own dummy domain '@testdomain.com' appended.

However,  we must indicate to DataVeil that we want to use the masked values of the person name columns and so we enclose those column names within {and } characters.

The exact SQL expression syntax depends on the DBMS:

SQL Server:  {firstName} + '.' + {lastName} + '@testdomain.com'
Oracle :       {firstName} || '.' || {lastName} || '@testdomain.com'
MySQL:        CONCAT( {firstName}, '.', {lastName}, '@testdomain.com' )

The screen capture below shows the User SQL Value mask for SQL Server:
 

After performing a Preview Run the Data Browser shows a sample of the before & after masking values. As shown below, the masked email addresses (in the "email (Preview)" column) have picked up the masked values of the firstName and lastName columns to form a new email address.

Calling a User-Defined Function

The User SQL Value mask actually gives you unlimited flexibility because you can call your own User Defined Functions on your DBMS.

For example, suppose that you have some special logic that you would like to use in order to create the email address value and that you already have (or will write) such a function on your database server called dbo.OurEmailBuilder. This function requires parameters from other columns such as the masked values of firstName and lastName, and the original value of country.

Therefore, the User SQL Value mask would be:
 

Joining to Other Tables

If the SQL code or Where condition reference columns that are in other tables (different tables to where the User SQL Value mask is defined) then Joins to those tables shall need to be defined.

This is done under the "Join" tab. Please refer to The Join Option for more information.
 

Referencing Columns in Other Tables from the SQL Code

If the User SQL code needs to reference a column that is in a different table then it will be required to:

- Define a Join to the other table
- Qualify the column reference using only the table alias value from the Join (shown on the Join panel under the "Alias" column)
- Enclose the column name within braces to reference the masked value of that column e.g. OTHER_TABLE_ALIAS.{COLNAME} or without braces to reference the original value of that column e.g. OTHER_TABLE_ALIAS.COLNAME .
 

Referencing Columns in Other Tables from the Where Condition

If the mask's Where condition needs to reference a column that is in a different table then it will be required to:

- Define a Join to the other table
- Qualify the column reference using only the table alias value from the Join (shown on the Join panel under the "Alias" column)

Synchronizing Column Duplicates

If it is required that the User SQL Value mask will mask duplicates consistently then you could either ensure that the SQL code is designed to produce consistent results (i.e. always generates the same masked value for the same input value) or the DataVeil Duplicates Column Option could be used.

Note: If Duplicates Column Option is used then every column whose original value (i.e. column name is not enclosed within {} braces) is referenced from the User SQL code must be included in the Duplicates column option's list of columns on which to identify duplicates.

For example, if the column email was masked using the original value of firstName combined with the masked value of lastName using the User SQL code...

    firstName + '.' + {lastName} + '@testdomain.com' 

...then the Duplicates column list would need to include both email and firstName.
 

User SQL Value Mask within an XML Mask

The User SQL Value mask can be used within an XML mask. In this case, the User SQL Value mask is limited to processing literal expressions, calling other functions and SQL queries that return a single value.

It is possible to access the current XML element's original value by specifying the identifier PATH_ORIG_VALUE (must be uppercase). This applies to the value(s) matched by the XML mask's XPath and Modify parameters. Therefore PATH_ORIG_VALUE will represent a node's Attribute value when the Modify parameter is 'Attribute' or a node's Value value when the Modify parameter is 'Value'. Other normal features of the User SQL Value mask, such as accessing other columns, the masked values of other columns and accessing the XML mask's Join to other tables, will not work.

It is not possible to reference other columns directly except if part of a SELECT query that returns a single value as shown in the third example below.

Example: To add 1000 to the current element's value, the User SQL Value mask's code section should contain:

1000 + PATH_ORIG_VALUE

Example: To call your own user defined function on the DBMS called myFunc(inputVal), the User SQL Value mask's code section should contain:

myFunc(PATH_ORIG_VALUE)

Example: To lookup another table based on the current element's original value, the User SQL Value mask's code section should contain something like:

select my_col from my_table where my_pk_col = PATH_ORIG_VALUE