SQL Server 2016: Does Dynamic Data Masking work with Temporal Table?

In the last IT Tagen 2016, I presented the Dynamic Data Masking (DDM) and how it worked.
To add a little fun, I applied the DDM to a temporal table to see if the history table inherits also from DDM’s rules.
In this blog, I explain all the different steps to reproduce my last demo.

Step 1: Create the table and the temporal table in the database DDM_TEST

USE [DDM_TEST]
GO

CREATE TABLE [dbo].[Confidential](
[ID] [int] IDENTITY(1,1) NOT NULL PRIMARY KEY CLUSTERED,
[Name] [nvarchar](70)NULL,
[CreditCard] [nvarchar](16)NULL,
[Salary] [int] NULL,
[Email] [nvarchar](60)NULL,
  [StartDate] datetime2 GENERATED ALWAYS AS ROW START NOT NULL,
  [EndDate] datetime2 GENERATED ALWAYS AS ROW END NOT NULL  
   , PERIOD FOR SYSTEM_TIME (StartDate,EndDate)
)  WITH (SYSTEM_VERSIONING=ON(HISTORY_TABLE = [dbo].[ConfidentialHistory]))

The table has sensitive data like the Salary and the Credit Card number.
As you can see, I add a history table [dbo].[ConfidentialHistory].
I insert 6 rows into my table and select both tables.

insert into [dbo].[Confidential]([Name],[CreditCard],[Salary],[Email]) values (N'Stephane',N'3546748598467584',113459,N'[email protected]')
insert into [dbo].[Confidential]([Name],[CreditCard],[Salary],[Email]) values (N'David',N'3546746598450989',143576,'[email protected]')
insert into [dbo].[Confidential]([Name],[CreditCard],[Salary],[Email])  values (N'Nathan',N'3890098321457893',118900,'[email protected]')
insert into [dbo].[Confidential]([Name],[CreditCard],[Salary],[Email])  values (N'Olivier',N'3564890234785612',98000,'[email protected]')
insert into [dbo].[Confidential]([Name],[CreditCard],[Salary],[Email])  values (N'Alain',N'9897436900989342',85900,'[email protected]')
insert into [dbo].[Confidential]([Name],[CreditCard],[Salary],[Email])  values (N'Fabrice',N'908323468902134',102345,'[email protected]')

select * from [dbo].[Confidential]
select * from [dbo].[ConfidentialHistory]

With just inserts, you have no entries in the history table.
After an update for the Salary of Stephane, you can see now the old value in the history table.
To see both tables I use the new option in the SELECT “FOR SYSTEM_TIME ALL”.

The context is in place. Now I will apply the DDM

Step 2: create the DDM rules

I apply masks on all columns from my table with different function like default, partial or email.

Use DDM_TEST
ALTER Table Confidential
ALTER COLUMN NAME ADD MASKED WITH (FUNCTION='default()')
ALTER Table Confidential
ALTER COLUMN SALARY ADD MASKED WITH (FUNCTION='default()')
ALTER Table Confidential
ALTER COLUMN creditcard ADD MASKED WITH (FUNCTION='partial(1,"XXXX",2)')
ALTER Table Confidential
ALTER COLUMN email ADD MASKED WITH (FUNCTION='email()')

As you can see if I read the table, nothing appends because I’m sysadmin of course!
Now, I begin the tests with a user who can just read the table.

Step 3: Test the case

The user that I create needs to have SELECT permissions on both tables (System-Versioned and History)

USE DDM_TEST;
CREATE USER TestDemo WITHOUT LOGIN
GRANT SELECT ON Confidential TO TestDemo
GRANT SELECT ON ConfidentialHistory TO TestDemo
I execute all SELECT queries as this user:
EXECUTE AS USER='TestDemo'
SELECT * FROM [dbo].[Confidential] 
REVERT
EXECUTE AS USER='TestDemo'
SELECT * FROM [dbo].[ConfidentialHistory]
REVERT
EXECUTE AS USER='TestDemo'
select * from [dbo].[Confidential]  FOR SYSTEM_TIME ALL
REVERT

As you can see, the 3 selects mask data for this user. Nice, isn’t it?
Finally, the Dynamic Data Masking works with Temporal Tables very well and they can be used to mask all data including historic data from users.