This masking option retrieves and inserts a substitute value using a lookup table. The lookup table can be in any schema (or database, in the case of an SQL Server) that PK Protect is able to connect to. This masking option is designed for use with existing masked data sets. Use it when you have rich sets of realistic but fictitious data that you have organized in relational tables for the express purpose of masking data.
*Note: The lookup table and the table containing the column to be masked must have at least one column in common on which to base a join. Make sure that the data type of the lookup data matches the data type of the column you are masking.
To configure a Custom Lookup mask, you must:
Specify the two columns that will form the join: The Base Reference column (in the table of actual data) and the Lookup Reference column (in the table of fictitious data). These columns need not have the same name, but they must share unique values in common.
Specify the column that contains the fictitious data that will replace the actual data. This is called the Lookup Data column.
To apply Custom lookup Masking, enter the following:
The Schema/User Name field displays the list of all the schemas that is created in the selected database.
The Table Name drop-down displays the list of all the tables which are created within the selected schema.
To select the table, click the three-dot icon next to the Table Name field. This opens the Select Table panel which list down all the names of the table for the selected schema.
The Base Reference Column and Lookup Reference Column displays the list of all the column names within the table.
*Note: The Lookup Reference Column field must have unique data for custom lookup masking to successfully mask the data.
The Lookup Data Column display the list of all the column names. The value of the selected column will replace the actual value of the base table.
Select either Keep Unchanged or Nullify value in the Missing Values field. The Keep Unchanged option keeps the original value unchanged in the base table. The option Nullify will update the value in the masked field with Null if the value in the base field is NULL.
E.g., with reference to below image, you want to mask the data in the COMPANY_NAME column with some fictitious such that the masked value will be picked from another table. In such scenarios, use the Custom Lookup masking which retrieves and inserts a substitute value using a lookup table.
Here, we will mask the data of the COMPANY_NAME column by retrieving data from another table in the same schema.
In the above image, we will mask the values of column COMPANY_NAME in Table 1 with the values of Table 2.
In this example, we’ve have selected the schema DocsDemo which contains a table dbo.T1. Here, COMP_ID and CMP_ID are selected as a Base Reference Column and Lookup Reference Column for both the tables. The IDs in both these columns are same.
The column COMPANY_NAME is selected in Lookup Data Column. The values of this column are used for masking the data in Table 1 column.
In the below image, on executing the task, the data in the target database is masked with values of the column Table 2. For Instance, the company name of COMP_ID = ‘Dg002’ has changed from Accenture to American Express. Similarly, the data for COMP_ID = ‘ Dg008’ is changed to FedEx from NULL post masking.