The 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.

You simply specify the SQL code that you want to return a value.

If your masking function requirement is simple then you can just enter the SQL expression directly into the User SQL Value mask panel, as shown below in the example for masking Email addresses.

Alternatively, if your required masking function is more sophisticated then you can create a User Defined Function 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. An example of this is also shown below.

It is important that the SQL expression that you specify 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 qualify the column name with a table name.

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 us a sample of the before & after masking values. As you can see 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 FirstName, LastName and State.

Therefore, your User SQL Value mask would simply be:

  

Joining to Other Tables


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

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 tab panel

If, in your User SQL code, you need to reference a column that is in a different table then you will need 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 if you want to reference the masked value of that column e.g. OTHER_TABLE_ALIAS.{COLNAME} or without braces if you want to reference the original value of that column e.g. OTHER_TABLE_ALIAS.COLNAME .

 

Referencing Columns in Other Tables from the Where condition tab panel

If, in the mask's Where condition, you need to reference a column that is in a different table then you will need to:

- Do all as stated above for 'Referencing Columns in Other Tables from the SQL Code tab panel' except that a Where condition cannot reference masked values (do not enclose the column name within braces).

 

Synchronizing Column Duplicates


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

Note: If you do use the Duplicates Column Option 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 we were masking the column Email using the original value of FirstName combined with the masked value of LastName as in the User SQL code "FirstName + '.' + {LastName} + '@testdomain.com'" then the Duplicates column list must include both Email and FirstName.

 

The 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 XML_ORIG_VALUE (must be uppercase). This applies to the value(s) matched by the XML mask's XPath and Modify parameters. Therefore XML_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 + XML_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(XML_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 = XML_ORIG_VALUE