How to mask multiple tables at the same time without primary key and foreign key relationships?

KBSingh

Hello -- I'm new to using SQL Data masker by Red Gate. I have multiple tables that have same column names and data types. But they do not have Primary key foreign key relationships established.

So instead of masking one table at a time or using table to table rule that only allows to mask 2 tables at a time, is their a way to apply the same rule to multiple tables even though their is no Primary key foreign key relationship?

The similar columns for example in all tables are BashQualifyID, WTN, FirstName, LastName, StreetAdrress, City, State and Zip. 

Alex B

Hi @KBSingh,

If you are creating row internal rules on columns which have the same name, the "Bulk Rule Generator" tool is available on the bottom of the Rules in Set tab. Just select a Template rule and it will auto generate multiple identical rules.

You can also use the Sync Manager rule to accomplish something like this where you create a substitution rule and convert it into a Sync Manager rule, then add similarly named columns to be included in the sync manager.  This will make all distinct entries in a column map to the same value so "Bob" will turn into "Sam" in all tables for example.


If you don't need that and just need a way to quickly duplicate rules to mask same named columns in different tables, you can also manually duplicate the XML of the rules and change the rule numbers and the tables involved.

The trick is to create an empty masking set with a rule controller and all of the schema information. Then create one Substitution rule to use as a template. Edit the Masking Set - it is XML so any text editor will do the job and copy out the section that describes the masking rule. The tags in the XML are readily understandable so it is usually not a problem to see what is going on in a simple masking set.

Then use the clipped out section as a template in a scripting language and replace the Table and column name as appropriate. Also change the rule number - this is very important - do not let the rule numbers be duplicates. Once the template is adjusted, just append it to the bottom of a temporary file. When all the replacements have happened just copy and paste the entire contents of the temporary file into the masking set in the place of the template rule in the masking set.

When the masking set is opened the rules should all appear as if they were created manually.

I hope this helps!

Kind regards,

Alex

evb

Hi,

To continue on the accepted answer of this question.

I want to do the same. Each table in our database has the fields CreatedBy and ModifiedBy (nvarchar fields), I want to mask that.

I have more then 80 tables in the database, so taking the first option, it will generate 80 internal-row rules. I have two fields to mask this way, so 160 rules.

• Can we group these rules so that we can collapse these rule into one line? It is easier to manage and to retain the overview of the rules.

Not able to do the grouping, I did take the second option: creating a normal substitute rule and then convert it to a sync rule.

A sync rule is collapsible, so I retains my overview.

But in my substitute rule I had a Where clause: replace only if the text is an email address (the text can also be a name of a automatic tool that I don't want to mask)

How to do that in a sync rule?

evb

To answer my own question.

• Via a work-around we can group these rules : take the first rule and drag and drop all the other under this rule. But than you change the dependency...

There is no where clause in a sync rule, so you must do it in the resulting Table-To-Table rule for each table.

Pay attention in the current version 7.0.4.5445 of Data Masker when writing your where clause. It must contain the alias for the target table, not only the fieldname or you will get an ambiguous fieldname error.

Example : where DMTGT.CreatedBy LIKE '%_@__%.__%'

DMSRC: the source table

DMTGT: the target table