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

Data Masker for SQL Server and Temporal Tables

A customer had a question recently about using Data Masker for SQL Server and temporal (or system versioned) tables. I decided to make a quick demo that will show how this works.

This is a simple demo, using just a few changes, but the concepts are useful for larger systems.

Setting up a temporal table

In the demo database for Data Masker, there is a dbo.dm_customer table. I’m going to make this a temporal table with a few schema changes. First, I’ll add two columns that will be used to store the valid datetime ranges.

ALTER TABLE dbo.DM_CUSTOMER
ADD StartTime DATETIME2(3) GENERATED ALWAYS AS ROW START
         DEFAULT GETUTCDATE(),
     EndTime DATETIME2(3) GENERATED ALWAYS AS ROW END
         DEFAULT CONVERT(DATETIME2(3), '9999-12-31 23:59:59.999'),
     PERIOD FOR SYSTEM_TIME(StartTime, EndTime);
GO

Next, I’ll enable system versioning, and specify the history table.

ALTER TABLE dbo.DM_CUSTOMER SET (SYSTEM_VERSIONING = ON (HISTORY_TABLE=dbo.DM_Customer_History));
GO

At this point, let’s test. I’ll query the history table and show it’s empty.

2018-07-18 15_49_55-datamask_temporal.sql - dkrSpectre_sql2016.DataMaskerDev_Grant (DKRSPECTRE_way0u

Now, let’s make a couple changes.

UPDATE dbo.DM_CUSTOMER SET customer_firstname = 'Jillian' WHERE customer_id = 1000002
UPDATE dbo.DM_CUSTOMER SET customer_firstname = 'Sian' WHERE customer_id = 1000004
INSERT dbo.DM_CUSTOMER ( customer_id,customer_firstname,customer_lastname, customer_gender, customer_company_name,
     customer_street_address, customer_region, customer_country, customer_email, customer_telephone,
     customer_zipcode, credit_card_type_id, customer_credit_card_number)
VALUES
(   '1000', 'Steve', 'Jhones', 'M', 'Redgate', '123 My St', 'CO', 'US',
     'sjones@sqlservercentral.com', '30333333333', '80014', '1', '12`345566')
GO
    
UPDATE dbo.DM_CUSTOMER
      SET customer_lastname = 'Jones' WHERE customer_id = 1000
UPDATE dbo.DM_CUSTOMER
      SET customer_lastname = 'Jilly' WHERE customer_id = 1000002
UPDATE dbo.DM_CUSTOMER
      SET customer_lastname = 'Jill' WHERE customer_id = 1000002
GO

We requery, and you can see there is data in the history table (at the bottom). All of my changes are captured in a sequence of copies of the row.

2018-07-18 16_09_26-datamask_temporal.sql - dkrSpectre_sql2016.DataMaskerDev_Steve (DKRSPECTRE_way0u

There are a few more rows in the history table, but they got cut off in the image. That isn’t that important. Just note that the names match up, for the most part, between the tables.

Using Data Masker

Now the interesting part. I have a masking set that’s similar to ones I’ve used in other articles. In this case, I’m changing names in two substitution rules. However, these rules are set for the dbo.dm_customer table. What about the dbo.dm_customer_history table?

To change this, I need to do a few things. First, I need a Command rule to disable system versioning. This is the way I disconnect the tables so that I can update data.

2018-07-18 16_10_31-Edit Command Rule

This has to take place before I change dbo.dm_customer, otherwise I’ll insert a bunch of new data in the history table. My ordering looks like this:

2018-07-18 15_58_36-temporal_ Data Masker for SQL Server

Once this is complete, I now need a Table-Table sync rule to ensure data in one table matches the other. When I create the rule, I need to have a way to join the two tables together. In this case, I use the customer_id column.

2018-07-18 15_59_49-Edit Table-To-Table Rule

Note that I’m ensuring the names and credit card numbers match here. This will wreck my history table slightly, in that all of the rows for a customer_id will have the same value, but this does ensure I don’t have sensitive information leaking.

Once I’m done, I make this a child of the other rules. This ensures that the substitutions run on the main table, then are synchronized to the history table.

2018-07-18 16_02_37-temporal_ Data Masker for SQL Server

You’ll notice I also have another command rule at the bottom. The text for this rule is below, and this rule enabled the temporal link again. I’m assuming the application is somehow using this. If not, you can ignore this rule and leave the tables disconnected if that’s Ok in your application.

2018-07-18 16_09_59-Edit Command Rule

All I need to do now is run the rules. Let’s do that.

2018-07-18 16_05_52-

Once the rules are complete, let’s query the tables again. I’m only looking for those rows that were changed previously.

2018-07-18 16_13_14-datamask_temporal.sql - dkrSpectre_sql2016.DataMaskerDev_Steve (DKRSPECTRE_way0u

As you can see, the dbo.dm_customer (top) table and the dbo.dm_customer_history table (bottom) are synchronized. The multiple values I had for different versions of customer 1000002 are now all the same. I’ve lost history, but if this were sensitive information, that works out. I could have used random values instead for the history table, but here I can see linkages if I’m checking for similar data.

This is a Hard Problem

Trying to determine how to rewrite history for audit tables is hard. Data Masker can do pretty much anything you would like, but here we need to be sure that we have an idea of what we want to do.

I’ve presented a simple version of setting all history to the current value, which will work in some cases. If not, let me know what you need to see, and I’ll build you a scenario.

Data Masker for SQL Server is an amazing tool in the SQL Provision bundle. Combined with SQL Clone, you can deploy databases with masked data for dev and test environments in seconds. Give it a try today and see what you think.

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...