Masking of two columns with same data in a table

Arun72

Hi all,
I new to this tool and started working on this. I am trying to mask two columns 1)name  2)name Upper in the same table.

So I have the same data in these two columns  one with "Lower case" and the other with "upper case ".I have used the substitution for my first column(name) and tried "Row internal " for the second column and mentioned the first column name under "column replacement values clause". Also made the second column as child rule to the first.

I even tried other ways but did not get synchronization, both columns are getting masked to different values. 

Please suggest me the right way.

Thanks,
Arun

PlantBasedSQL

Hi @Arun72 thank you for your post, sorry to hear you're having some difficulties with the masking rules!

The way I would tackle this is with the following rules:

0 1 -0001 - Substitution Rule (mask both name fields with First Names, for security)
x x -0002 - Row-Internal Rule (set [lower first name] to LOWER itself)
x x -0003 - Row-Internal Rule (set upper first name to UPPER [lower first name])

Note, I've included "xx" for the Row-Internal rules because of the dependencies involved, I don't want to run the Row-Internal rules at the same time as the substitution rule.

If you try this, or it's very similar to what you already have, let me know - you can also send your Masking Set to me (Chris.Unwin@red-gate.com) and I will happily take a look for you!

Thank you very much,
Kindest,
Chris

PlantBasedSQL

Ah! I see now @Arun72 apologies, I didn't realize you had the requirement to always de-identify the same value to the same masked value also (alongside the UPPER requirement you had).

So normally for this operation I would either use a Table-Internal rule if there is some other identifier on the table, like a Customer_ID or such, as this will then sync up the values - so you would do substitution rule 1st, then Table internal, then row-internal.

If you do not have another identifier that could be used then the Sync Manager is the best option, however I am able to convert a Substitution rule to a Sync manager on a column with NULLs with no problems. Are you able to send a screen shot of what you're seeing at all?

I'll show you my set so you can see how I've set it up - this is my Data Set:



I'm going to make the last name match your use case as UPPER(FirstName). So I first create a substitution rule to change all first names to First Names (Male and Female), it keeps it's standard WHERE clause of "WHERE NOT NULL OR EMPTY":





Then I convert it to a Sync Manager rule, but I don't change anything else:



Next I add 2 Row-Internal rules making FirstName lower and the column LastName is now going to be UPPER(FirstName) - I don't change any of the WHERE clauses:





And then I move these into Rule Block 2, make them dependent on one another:



and there we have it:



Let me know if this makes sense or if I've completely missed the mark here - Happy to try and help out as best I can!

Thank you very much - kindest.

PlantBasedSQL

Thanks @Arun72

That makes perfect sense - this is a slightly trickier case than I believed then, apologies for not realizing you have the WHERE clause set like that.

The issue, I believe, is that the Substitution rule is not run against the table in question with the customer IDs on - because we are using a Sync manager to replace all values consistently, they are first copied into a temporary table and then the substitution rule is run against this table, and then synchronized back in. As a result, your WHERE clause simply wouldn't work as the ID wouldn't be available.

The easiest way for you to use the Sync Manager AND also specify which rows get updated would be as follows:

1. Double click on your Sync Manager and open up the "Edit Sync Manager" popup dialogue 
2. Double click on the Table-to-Table rule that is synchronizing the names back in and open up the "Edit Table-To-Table Rule" Dialogue
3. Go to the "Where Clause" tab
4. Select "Enable Where Clause" and "Apply to Target Table"
5. Set your WHERE clause to be something like "WHERE ID IN (1, 2, 3, 4)" etc.
6. Update Table To Table Rule
7. Update Sync Manager

This should then achieve what you want let me know if this helps!

Thank you very much - kindest!

 

PlantBasedSQL

My pleasure @Arun72

Happy I was able to help

The Redgate University Courses and Documentation are the best resources available for this I believe - there are also _some_ videos available on the Redgate YouTube channel (many specifically under Solution Engineer Videos, called "Data Masker Headscratchers"), and on my person blog PlantBasedSQL.com

On your points, XML Masking and Data Insertion both have their own rules available in Data Masker so this should be reasonably straight forward, and multiple table consistency is the purpose of:

- The Table-To-Table Sync rule where some common identifier is available 

- The Sync Manager rules when there is no common identifier available

So you should be good to go! However if you require any further help please do feel free to reach out on the forums again, and we'll be happy to help!

PlantBasedSQL

Hey @Arun72 did the above solve your problem at all?
Hope everything is going well! 
Thank you very much!

Arun72

Hi @PlantBasedSQL,

Thanks for the quick response .It really helped and I was able to get the synchronization.

Arun72

HI @PlantBasedSQL

I have an extended requirement for the above use case. Please see the attached sample data

I have to mask both the columns from the picture. I tried the way you suggest with synch manger rule. Please see the steps I followed to mask the above data.

**The first 3 steps are same as you suggested.
0 1 -0001 - Substitution Rule (mask both name fields with First Names, for security)
x x -0002 - Row-Internal Rule (set [lower first name] to LOWER itself)
x x -0003 - Row-Internal Rule (set upper first name to UPPER [lower first name])

The final step is I have converted the Substitution Rule into "Synch Manager Rule" .

But the issue is I can't have the  "Where clause " before converting the Substitution Rule into "Synch Manager Rule" and after conversion there is no Option to enter the Where Clause.

I tried inserting the where Clause in "Row Internal" but has no effect. What ever the Options I tried under "Where Clause and Sampling " tab in "Row Internal" that has no value in the masking of two columns except maintaining the consistency in masking values.

My motive is to mask the selected records  and also not mask the NULL values as shown in picture. 

Thank You,
Arun.

PlantBasedSQL

Hi Arun,

Thank you for your post on this - I'm a little confused as to why you've converted the Substitution Rule to a Sync Manager? Would you be able to expand on this a little please?

Substitution should securely mask both values, and the WHERE clause can stay the same "WHERE NOT NULL or EMPTY", and then the 2 x Row-Internal rules will both sync the 2 fields together and give you the UPPER/LOWER values that you need in those columns, am I missing something?

If you perhaps show an example of what you're starting with, and what you would like to end up with, I can advise more! Thank you very much.

Kindest.

Arun72

Hi @PlantBasedSQL,

Thanks for the quick response .It really helped and I was able to get the synchronization.

Arun72

Hi @PlantBasedSQL,

I have tried the way you mentioned with Substitution and Row Internal but the same name value in the same column is not getting masked in a consistent way. That is the reason I went with Synch Manager Rule.
Please see the below pic.


And with the synch manager I was not able to enter the Where Clause as I mentioned in my earlier post(please see the picture).

Thank you,
Arun

Arun72

HI @PlantBasedSQL

That was so helpful and thanks for the clear explanation.

Everything is good except that I wanted to perform masking for some specific records(like  "where id in (2,3,4,5,6)").So  my actual scenario is to apply masking to those records ONLY.I wanted to have this where Clause in my substitution rule. When I had this Where Clause, I can't achieve the consistent masking with name because I didn't convert the Substitution into "Synch Manager Rule" .

If I convert the Substitution into Synch Manager I can't insert my where Clause. I tried having Where Clause in command rule before the substitution rule but it did not help.

So in short I wanted to have a where clause in your process which you explained in previous reply.

Thank you so much for your time.

Arun72

Hi @PlantBasedSQL

Thank you so much for the step by step explanation, it worked well and I've got the expected results.
In my Implementation I have so many scenarios that I have to work. Since we have the license for the tool, do you think of any licensed video courses (besides official documentation and 10 courses on Red Gate website) for customers who bought the tool. If there are any that would be helpful.

Below ae some of my scenarios I am going to work.
1)Same data masking in multiple tables with consistency 
2)XML masking
3)Data Insertion 

Thank you,
Arun

Arun72

Hi @PlantBasedSQL,

I was able to perform basic XML masking and working on Insertion and Masking multiple tables with synchronization.

As you know from our previous conversation I have the same data in 2 tables and I wanted to get the synchronized records after masking. I  am able to perform the same in single table but for 2 tables, I even tried multiple ways like having Table-To -Table rules, Synch manager, etc., but couldn't get the results.

I am thinking that I have to add some additional rules to my masking set which I used for single table masking. I might be missing the order of rules.

Below is the sample data in 2 tables(attaching one screen shot since the same data exists in 2 tables)


If you can suggest me a better way that would be great.


Thanks,
Arun

PlantBasedSQL

Thanks @Arun72 for the post - I definitely think I can help you with this.

I first duplicated your issue:



Then I created a substitution rule for the Name-Lower column on Names1:



Then I converted this into a Sync-Manager rule:



Then I edited the Sync manager because at the moment it's only processing a single column, I hit "rule generation by column", search for the affected columns across both tables, tick them and "Add Marked" then update the sync manager:



This results in a Sync manager that looks like this:



You can see the table to table rules fanning back out twice to each affected column across the 2 tables - you don't have to be worried about capitalization at this point as casing isn't considered by Data Masker during this operation, we SHOULD however be careful as all 4 T2T Sync rules will try to run at the same time, which may cause row locking. So we click into the rule block numbers for 2 of the T2T sync rules and change them to 56 instead of 55:



Now we need to make sure Name_Upper gets upper case values, so I now create 2 dependant Row-Internal sync rules which set Name_Upper on both tables to UPPER(itself) - for ease I put these in the following rule block:



Meaning you should end up with a set like this:



and a resulting Data set that looks like this:



Let me know if this makes sense, if you need any further clarification, or if there's anything further I can do to help out! Thank you very much!

Kindest.

Arun72

Hi @PlantBasedSQL

My bad. I didn't get a chance till today and Yes I did exactly as you mentioned and it's working great.

Thanks you soo much:)

 

PlantBasedSQL

Absolutely not a problem @Arun72
I just wanted to check everything was working for you!
Glad to hear it
Let me know if you need anything else at all! Thank you very much.
Kindest