Sync. Manager Rules
Published 06 January 2020
- Modifying the primary key of a table all associated columns in other tables.
- To mask unique combinations data consistently, i.e. every "Alan Smith" becomes "Anita Radcliffe" in all tables, regardless whether there is a specific link between the tables.
- When we are masking across multiple tables with no enforced relationship (e.g. no Primary Key-Foreign Key relationship between the tables).
However, the approach to creating a Sync. Manager Rule is always the same:
- Creating either a Substitution Rule, or a Row-Internal Rule, for the column, or columns that you wish to synchronize.
- Ensure that the rule does not have a WHERE clause.
- Go to the Change Managers tab and select Convert to Sync. Manager Rule.
Given name information is scattered among multiple tables (
Customers, Orders, Employees), but most of the information is contained within the
Customers table the best place to start is a Substitution Rule on the
- Mask the
CustomerNamecolumn with Names, First+Last, Male+Female.
- Go to the "Where Clause and Sampling" tab and select "No Where Clause".
- Go to the "Change Managers" tab and select Convert to Sync. Manager Rule.
Open the resulting Sync. Manager Rule and go to the "Rule Generation By Column":
- Search for the column name: %Name.
- Select the
Orders.CustomerNamecolumns and click "Add Marked".
- This will result in new rules in the "Sync. Rules" page.
- Save the Sync. Manager Rule.
After running this masking set the tables might look like:
|676||...||Stephen Smith||Mark Walsh|
|12361||...||Alice Jones||Hannah Fehrs|
|12678||...||Lisa Robertson||Mary Wise|
|123543||...||Stephen Smith||Mark Walsh|
|213123||...||James Stone||Craig Anderson|
|345673||...||Leigh Stephenson||June Gray|
|123||...||James Stone||Craig Anderson|
|167||...||Alice Jones||Hannah Fehrs|
|230||...||George Kirk||Barry Davidson|
The structure of a Sync. Manager Rule
When you create a Sync. Manager Rule you will see that it is a collection of other rules:
- Command Rules that create a populate a temporary table to hold the distinct values to mask. These are not editable.
- Either a Substitution Rule or a Row Internal Rule, based on the rule that was converted into the Sync. Manager Rule.
- This applies the mask that was configured in the original rule but on the new temporary table.
- A Foreign Key Manager rule to disable foreign keys (if the rule is changing part of a primary key)
- A Table-To-Table Synchronization rule that copies the data from the temporary table back to the original table.
- It is not possible to edit the "Columns to Synchronize" or the "Join Columns" in this rule.
- A Foreign Key Manager Rule to enable the keys that were disabled earlier.
- A Command Rule to drop the temporary table at the end. This is not editable.
The temporary table that is constructed consists of:
DMSIdentityCol- a primary key for the table so subsequent rules will work correctly against the table.
- The column name(s) that have been selected from the source table.
- These represent the original values and will not be masked.
- Use these if joining to another table.
- The columns to mask with a suffix of
_DMSTMPwhich contains the data to mask.
Manually adding rules to the Sync. Manager
You can add a Command Rule via the "Add Rules" tab. As with a normal Command Rules, you can add any SQL statements you wish here, including modifying the contents of the temporary table, though it is advised that you do not alter any of the columns that have been created.
If you want to just take the masked data and move it across to a new table (ignoring any unmatched rows in the target table) then add a Table-To-Table Rule. The rules will be restricted so the columns containing the masked data are the source for the "Columns to Synchronize" (and cannot be removed) and the columns containing the original data are the "Join Columns" (and cannot be deleted).
A Table-To-Text Rule will ensure that the contents of text fields do not contain the sensitive data that matches the columns being altered as part of this Sync. Manager Rule. It will modify the contents of a free-text column in the target table by replacing the original data (found in the source columns) with the matching masked value.
Cross DB Table-To-Table Rules
To mask the data in another database consistently, with the changes being made in the Sync. Manager Rule, use the Cross DB Table-To-Table Rule. As with the standard Table-To-Table Rule the source "Sync. Columns" and "Join Columns" have been preset based on the contents of the temporary table and cannot be changed.
Generating rules by foreign key
If the columns that are being masked in the original table would invalidate a Foreign Key relationship the "Rule Generation By FK" tab provides a quick and easy way to generate the rules. The "Tables With Invalid FK Relationships Because of the Actions of This Rule" table contains all of the affected foreign keys, select the appropriate tables and click on the "<<<" button below to add them as "Secondary Fan-Out Tables" and Data Masker will generate the appropriate rules to ensure they are masked properly.
Adding rules by column name
If you are masking only a single column you will see the "Rule Generation by Column" where you can search for matching columns within the current schema and generate rules to correctly mask them.
The search for columns using wildcards:
- The "
%"character matches any number of characters
- The "_" character matches only a single wildcard character
- e.g. _NAME would match
- e.g. _NAME would match
Once you have found the columns you wish to mask select them in the table and click on "Add Marked" to generate appropriate rules within the Sync. Manager.