How can I write my own custom data masking function?

In some cases you may find that you would like to write your own custom data masking code to mask Column values instead of using one of the built-in DataVeil data masking functions.

If you’re not already a DataVeil user then you can download the DataVeil Platform for free and run unlimited custom masks that you create (described below) on as much data as you need.

To create your custom data masking function you simply write your code in either T-SQL (for SQL Server) or PL/SQL (for Oracle) and perform it using the DataVeil User SQL Value mask. Your two basic options are:

Option 1) Put your custom code directly in the DataVeil User SQL Value mask’s form.

Option 2) Put your custom code in an SQL User Defined Function (UDF) and call it from a DataVeil ‘User SQL Value’ mask.

Each approach is described below in more detail.

Please note that whichever approach you take, this code shall be called once for each original value being masked. Therefore this code must always return exactly one value that is compatible with the data type of the Column being masked.

You can refer to other Column values simply by using the Column name; however, if the referenced Column is also being masked and you wish to use the masked value then you must enclose the Column name within {} braces e.g. {ColumnName}

Please refer to the ‘User SQL Value’ mask topic under the Help menu in the DataVeil software.

Option 1) Put your custom code directly in the DataVeil User SQL Value mask’s form

 

If your code is a simple expression then you could place it directly in the form.

The advantage of this is that your custom code is readily visible and is always guaranteed to be present whenever you perform a masking run.

However, if your code is non-trivial or consists of more than a single expression then you should develop it as an SQL User Defined Function on the DBMS (see Option 2.)

The example below shows how you could mask an Email address by combining a person’s masked first name and masked last name with a dummy domain name ‘new-domain.com’ (The masks for the first name and last name Columns are not shown)

Note: The {} braces around a column name specifies that the masked value of the Column is to be used.

user_sql_mask_inline

 

A preview of the masked values (using DataVeil’s Preview Run feature) showing what the before and after masked values would look like is shown below:

inline_result

Option 2) Put your custom code in an SQL User Defined Function and call it from a DataVeil User SQL Value mask

 

If your custom masking code is non-trivial or lengthy then it may be better to write it as an SQL User Defined Function and maintain it on your DBMS.

You would then use a DataVeil User SQL Value mask to simply call your function.

If you take this approach then you need to consider that you shall have to maintain your masking code in at least two places: the first is your actual DataVeil data masking project (.dvp file) and the second is the User Defined Function(s) on your DBMS that your project shall call.

For example, suppose we create a T-SQL User Defined Function called dbo.my_email_masking_function() that takes a person’s first name and last name as parameters and creates returns an email address using one of five fictitious domains.

The User Defined Function could look something like this..

CREATE FUNCTION dbo.my_email_masking_function(
    @var_p_firstname VARCHAR(100),
    @var_p_lastname VARCHAR(100)
) RETURNS VARCHAR(100)
AS
BEGIN
DECLARE 
    @var_hash INT,
    @new_email VARCHAR(100);    
SET @var_hash = ABS(CHECKSUM(@var_p_firstname + @var_p_lastname)) % 5;    
SET @new_email = @var_p_firstname + ‘.’ + @var_p_lastname 
    + ‘@’ + CASE 
        WHEN @var_hash = 0 THEN ‘new-domain-1.com’
        WHEN @var_hash = 1 THEN ‘new-domain-2.com’
        WHEN @var_hash = 2 THEN ‘new-domain-3.com’
        WHEN @var_hash = 3 THEN ‘new-domain-4.com’
        ELSE ‘new-domain-5.com’
    END;           
RETURN @new_email;  
END;

We would then create a DataVeil ‘User SQL Value’ mask to call the User Defined Function…

user_sql_mask_call_udf

A preview of the masked values is shown below:

udf_result