An Example of a Row-Internal Synchronization Requirement

Consider the above example - the FULL_NAME field is composed of data from other columns in the same row. After the data has been masked, the FIRST_NAME and LAST_NAME columns will have been changed to other values. For the information to be secure, clearly the FULL_NAME field must also change. However, it must change to contain values synchronized with the rest of the data in the row so that the masked data reflects the denormalized structure of the row. This type of synchronization is called row-internal synchronization and it is quite distinct from the other two types: Table-Internal and Table-To-Table synchronization.

A Row-Internal Synchronization rule updates a field in a row with a combination of values from the same row. This means that if, after masking, the FIRST_NAME and LAST_NAME change to Albert and Wilson then (in this example) the FULL_NAME column should contain Albert Wilson. Row-Internal Synchronization is a common requirement and the Data Masker software makes it simple to accomplish - just implement the specialized Row-Internal Synchronization rule.

To configure a Row-Internal Synchronization rule it is necessary to know how the column should be built and to be able to express that knowledge in an SQL update statement (this is known in the Data Masker software as a Replacement Clause). The construction of a replacement clause is similar to that which would be used in a conventional SQL UPDATE statement. In the example above, the FULL_NAME field is a concatenation of the FIRST_NAME and LAST_NAME columns and a suitable replacement clause would be: FIRST_NAME+' '+LAST_NAME.

The replacement clause will be built into a statement that updates each row and passed to the SQL Server database. It can be as complex as is required. For example, the following update statement would also be valid:

Last_Name+' '+SUBSTRING(First_Name, 1, 1)+' ('+Title+')' /* build the columns as LASTNAME INITIAL (TITLE) */

It is also possible to dispense with any references to other columns within a row and simply call a function (either system or user defined). For example the expression below would be valid for a target column.

GetDate()+10 /* for a DATETIME datatype target column*/

Another feature of the replacement clause is the ability to incorporate values from datasets into the text. This is done through the use of placeholder variables with names like DMSPARAM? where the ? value is an integer digit. The DMSPARAM? placeholder parameter will have the dataset value substituted at runtime. For example if the DMSPARAM1 was associated with the Numbers integer, sequential then a replacement clause such as

UPPER(SUBSTRING(LAST_NAME,0,3))+DMSPARAM1+'A'

could build a series of values similar to SMI10001A, JON10002A etc (assuming the Numbers integer, sequential dataset was configured to output five digit numbers). An unlimited number of placeholder parameters can be used in a Row-Internal Synchronization rule.

The configuration of a Row-Internal Synchronization rule is simple. The target table and column is chosen and the replacement clause pasted into the field provided. If placeholder parameters are required there is an area in which they can be associated with datasets. Data Masker does the rest of the work - any columns or placeholders referred to by name in the replacement clause will be substituted at run time with the appropriate data values. The help file for the New Row-Internal Synchronization rule form provides more details on the mechanics of this process.

Row-Internal Synchronization rules are created by launching the New Row-Internal Synchronization rule form using the New Rule button located on the bottom of the Rules in Set tab.

Adding a Row-Internal Synchronization Rule