Defeating Dynamic Data Masking

I can’t recall exactly where or who said it, but at some point over this past summer I recall someone mentioning that the new dynamic data masking feature in SQL Server 2016 could be easily worked around by selecting the data into a temporary table.  I shelved that thought for several months, and then this past weekend I started playing with it, only to find that the issue had been fixed in CTP 2.4.  However, I wanted to document some of the things that I tried just to show that it isn’t so easy any more to defeat the benefits of this new feature.  These tests were run in CTP 3.2.

Let’s start with the sample table from Books Online:

CREATE TABLE Membership
  (MemberID int IDENTITY PRIMARY KEY,
   FirstName varchar(100) NULL,
   LastName varchar(100) NOT NULL,
   Phone# varchar(12) NULL,
   EMail varchar(100) NULL);
 
ALTER TABLE Membership ALTER COLUMN FirstName ADD MASKED WITH (FUNCTION = 'partial(1,"XXXXXXX",0)');
ALTER TABLE Membership ALTER COLUMN Phone# ADD MASKED WITH (FUNCTION = 'default()');
ALTER TABLE Membership ALTER COLUMN Email ADD MASKED WITH (FUNCTION = 'email()');
 
INSERT Membership (FirstName, LastName, Phone#, EMail) VALUES 
('Roberto', 'Tamburello', '555.123.4567', 'RTamburello@contoso.com'),
('Janice', 'Galvin', '555.123.4568', 'JGalvin@contoso.com.co'),
('Zheng', 'Mu', '555.123.4569', 'ZMu@contoso.net');
 
CREATE USER TestUser WITHOUT LOGIN;
GRANT SELECT ON Membership TO TestUser;

We can test that DDM is working by running:

EXECUTE AS USER = 'TestUser';
SELECT * FROM Membership;
REVERT;

The results are:

ddmResults1

Next, we will try to select the results into a temporary table and then select from the temp table.

EXECUTE AS USER = 'TestUser';
if object_id('tempdb..#Membership') is not null
	drop table #Membership;
SELECT * into #Membership FROM Membership;
select * from #Membership;
REVERT;

The results are identical.

ddmResults2

In some cases attributes of the table get copied as part of a SELECT … INTO statement, so the next test is to explicitly create the temp table:

EXECUTE AS USER = 'TestUser';
if object_id('tempdb..#Membership') is not null
	drop table #Membership;
CREATE TABLE #Membership
  (MemberID int IDENTITY PRIMARY KEY,
   FirstName varchar(100) NULL,
   LastName varchar(100) NOT NULL,
   Phone# varchar(12) NULL,
   EMail varchar(100) NULL);
 
set identity_insert #Membership on;
 
insert #Membership (MemberID, FirstName, LastName, [Phone#], Email)
SELECT MemberID, FirstName, LastName, [Phone#], Email FROM Membership;
 
set identity_insert #Membership off;
 
select * from #Membership;
REVERT;

The results are identical:

ddmResults3

What if we do something very similar, but use one of the columns in an expression?

EXECUTE AS USER = 'TestUser';
if object_id('tempdb..#Membership') is not null
	drop table #Membership;
CREATE TABLE #Membership
  (MemberID int IDENTITY PRIMARY KEY,
   FirstName varchar(100) NULL,
   LastName varchar(100) NOT NULL,
   Phone# varchar(12) NULL,
   EMail varchar(100) NULL);
set identity_insert #Membership on;
 
insert #Membership (MemberID, FirstName, LastName, [Phone#], Email)
SELECT MemberID, FirstName + '', LastName, [Phone#], Email FROM Membership; 
set identity_insert #Membership off;
select * from #Membership;
REVERT;

ddmResults4

Interestingly, this strips the first character from the results, yielding only “xxxx” in the FirstName column!

We can try and see if the “masked” attribute was copied to the temp table created as a SELECT … INTO statement.

EXECUTE AS USER = 'TestUser';
if object_id('tempdb..#Membership') is not null
	drop table #Membership;
SELECT * into #Membership FROM Membership;
 
alter table #Membership alter column FirstName drop masked;
 
select * from #Membership;
REVERT;

Msg 16007, Level 16, State 0, Line 32
The column 'FirstName' does not have a data masking function.

Nope, the masking attribute doesn’t get copied to the temp table.

Next, we try selecting into a variable first, then seeing what is in the variable:

EXECUTE AS USER = 'TestUser';
declare @FirstName varchar(100);
select @FirstName = FirstName from Membership where MemberID = 1;
select @FirstName;
REVERT;

Now we get:

ddmResults5

Let’s select the characters individually. Based on what we’ve seen to this point, this probably isn’t going to work.

EXECUTE AS USER = 'TestUser';
declare @c1 char(1), @c2 char(1), @c3 char(1), @c4 char(1), @c5 char(1), @c6 char(1), @c7 char(1)
select @c1 = substring(FirstName, 1, 1), @c2 = substring(FirstName, 2, 1), @c3 = substring(FirstName, 3, 1), @c4 = substring(FirstName, 4, 1),
	@c5 = substring(FirstName, 5, 1), @c6 = substring(FirstName, 6, 1), @c7 = substring(FirstName, 7, 1)
from Membership where MemberID = 1;
select @c1, @c2, @c3, @c4, @c5, @c6, @c7;
REVERT;

And indeed we only get:

ddmResults6

Finally, we will create another database and create a user as database owner:

create database AnotherDatabase;
GO
use AnotherDatabase;
 
create login AnotherDBUser with password = 'B@dP@$$w0rd';
create user AnotherDBUser for login AnotherDBUser;
 
alter role db_owner add member AnotherDBUser;

Now switch back to the database containing the member table, and grant this login read-only access.

create user AnotherDBUser for login AnotherDBUser;
alter role [db_datareader] add member AnotherDBUser;

And try it out:

execute as login = 'AnotherDBUser';
SELECT * FROM Membership;
REVERT;
 
execute as user = 'AnotherDBUser';
SELECT * FROM Membership;
REVERT;

Both queries return the same masked result set.

ddmResults7