Obfuscate / Mask / Scramble personal information

Question

I'm looking for a homegrown way to scramble production data for use in development and test. I've built a couple of scripts that make random social security numbers, shift birth dates, scramble emails, etc. But I've come up against a wall trying to scramble customer names. I want to keep real names so we can still use or searches so random letter generation is out. What I have tried so far is building a temp table of all last names in the table then updating the customer table with a random selection from the temp table. Like this:

DECLARE @Names TABLE (Id int IDENTITY(1,1),[Name] varchar(100))

/* Scramble the last names (randomly pick another last name) */
INSERT @Names SELECT LastName FROM Customer ORDER BY NEWID();
WITH [Customer ORDERED BY ROWID] AS
(SELECT ROW_NUMBER() OVER (ORDER BY NEWID()) AS ROWID, LastName FROM Customer)
UPDATE [Customer ORDERED BY ROWID] SET LastName=(SELECT [Name] FROM @Names WHERE ROWID=Id)

This worked well in test, but completely bogs down dealing with larger amounts of data (>20 minutes for 40K rows)

All of that to ask, how would you scramble customer names while keeping real names and the weight of the production data?

UPDATE: Never fails, you try to put all the information in the post, and you forget something important. This data will also be used in our sales & demo environments which are publicly available. Some of the answers are what I am attempting to do, to 'switch' the names, but my question is literally, how to code in T-SQL?

Answer 1

A very simple solution would be to ROT13 the text.

A better question may be why you feel the need to scramble the data? If you have an encryption key, you could also consider running the text through DES or AES or similar. Thos would have potential performance issues, however.

Answer 2

I use generatedata. It is an open source php script which can generate all sorts of dummy data.

Answer 3

Why not just use some sort of Random Name Generator?

Answer 4

Frankly, I'm not sure why this is needed. Your dev/test environments should be private, behind your firewall, and not accessible from the web.

Your developers should be trusted, and you have legal recourse against them if they fail to live up to your trust.

I think the real question should be "Should I scramble the data?", and the answer is (in my mind) 'no'.

If you're sending it offsite for some reason, or you have to have your environments web-accessible, or if you're paranoid, I would implement a random switch. Rather than build a temp table, run switches between each location and a random row in the table, swapping one piece of data at a time.

The end result will be a table with all the same data, but with it randomly reorganized. It should also be faster than your temp table, I believe.

It should be simple enough to implement the Fisher-Yates Shuffle in SQL...or at least in a console app that reads the db and writes to the target.

Edit (2): Off-the cuff answer in T-SQL:

declare @name varchar(50) set @name = (SELECT lastName from person where personID = (random id number) Update person set lastname = @name WHERE personID = (person id of current row)

Wrap this in a loop, and follow the guidelines of Fisher-Yates for modifying the random value constraints, and you'll be set.

Answer 5

When doing something like that I usually write a small program that first loads a lot of names and surnames in two arrays, and then just updates the database using random name/surname from arrays. It works really fast even for very big datasets (200.000+ records)

Answer 6

Use a temporary table instead and the query is very fast. I just ran on 60K rows in 4 seconds. I'll be using this one going forward.

DECLARE TABLE #Names (Id int IDENTITY(1,1),[Name] varchar(100))

/* Scramble the last names (randomly pick another last name) */ INSERT #Names SELECT LastName FROM Customer ORDER BY NEWID(); WITH [Customer ORDERED BY ROWID] AS (SELECT ROW_NUMBER() OVER (ORDER BY NEWID()) AS ROWID, LastName FROM Customer) UPDATE [Customer ORDERED BY ROWID] SET LastName=(SELECT [Name] FROM #Names WHERE ROWID=Id) DROP TABLE #Names