SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 

Randomizing Names with Data Masker

Data Masker for SQL Server is pretty cool. It can reduce your attack surface area and exposure under the GDPR or any other regulation. There are plenty or rules and templates to help you anonymize data, in ways you might not have expected. Data Masker is a part of SQL Provision and the SQL Data Privacy Suite.

In this post, I want to show you a common technique that can be used to anonymize names. That’s a fairly common requirement for many datasets, and Data Masker makes it easy.

Last Names

Since Redgate is a UK company, we call last names, surnames. These might also be family names in your culture, but these are the common names shared by everyone in a family.

In any case, there is a series of datasets for last names/surnames. If I look through the list, I see these:

  • Names, Surname Suffixes
  • Names, Surname Titles
  • Names, Surname Titles (Short List)
  • Names, Surnames, Random (DE)
  • Names, Surnames, Random (PT)
  • Names, Surnames, Random (ES)
  • Names, Surnames, Random (FR)
  • Names, Surnames, Random (Large List)
  • Names, Surnames, Random (NL)
  • Names, Surnames, Random (Short List)

Each of these is a file with a list of names. The encoding is binary, but if you choose one, you can click the “Sample” button to see some of the entries. For example, here are the Last Names (Large List) sample:

2018-05-04 11_07_30-Example Values from Names, Surnames, Random (Large List)

There are country oriented sets and then a large and small list of English names. In this case, I’ll map the large list to my customer_lastname column. I could make these all upper case or choose unique values if I choose. I won’t here.

2018-05-04 11_32_54-New Substitution Rule

I could also use the WHERE Clause and Sampling tab to limit the replacements. The default it to replace rows where the value is NOT NULL or empty.  I won’t bother and I’ll just create this rule.

First Names

Replacing first names is a two stage process, or it can be if you care. Gender can matter with first names, so the process can be one of two methods:

  1. replace gender = ‘F’ with female names
  2. replace gender=’M’ with male names.

This is slower as it requires checking in both passes. A better solution might be:

  1. replace all rows with female names
  2. replace gender=’M’ with male names.

In this case only one set of checks is made in the second step. I’ll do this.

First, I’ll modify the existing substitution rule to add customer_firstname to the list of replacements. In this case, I’ll choose the Names, First Names, Female as the data set.

2018-05-04 11_52_57-Edit Substitution Rule 

I’ll leave the WHERE clause alone and save this rule.

Once this is done, I’ll create a new substitution rule. For the dataset, I’ll choose male first names.

2018-05-04 11_54_14-New Substitution Rule

Once this is done, I want to limit replacements, so I’ll go to the WHERE clause tab and enter a valid T-SQL clause:

2018-05-04 11_54_40-New Substitution Rule

I’ll save this rule and then I see this:

2018-05-04 11_55_59-Data Masker for SQL Server

Both rules are listed in order, but there is no linkage. My data masking can potentially run these two rules in parallel. I don’t want that. The second rule depends on the first one having run.

I can add a dependency by dragging the lower rule onto the type of the upper rule. The object behavior is a little funny here, so experiment with “grabbing” the lower rule by clicking on it and holding, them moving it up. You should end up with an indent, signifying dependency.

2018-05-04 11_56_08-Data Masker for SQL Server

Now I’ll run the masking set, after querying the table in a window. Once the masking is run, I’ll open a second window in a vertical tab and re-run the same query. You can see the before and after below:

2018-05-04 11_59_40-SQLQuery2.sql - (local)_SQL2016.DataMaskerDemo (PLATO_Steve (65))_ - Microsoft S

This is a basic look at a multi-stage process to anonymize data in a way that makes sense to your application. Using this technique, I can get production like data in lower environments, without using production data.

Data Masker does some really interesting things as far as cleaning and masking data, so I’d suggest you give this a try as a way to process your lower environments.

If you have questions or want to see other masks, let me know. I also have other articles on Data Masker if you’re interested.

The Voice of the DBA

Steve Jones is the editor of SQLServerCentral.com and visits a wide variety of data related topics in his daily editorial. Steve has spent years working as a DBA and general purpose Windows administrator, primarily working with SQL Server since it was ported from Sybase in 1990. You can follow Steve on Twitter at twitter.com/way0utwest

Comments

Leave a comment on the original post [voiceofthedba.com, opens in a new window]

Loading comments...