Dynamic Data Masking

Dynamic Data Masking is a security feature added in SQL Server 2016. Dynamic Data Masking uses functions attached to the column’s definition to obfuscate that column’s data. The data will be obfuscated to all users who are not DBO or do not have the the unmask privilege. There are 4 functions you can use to mask a column: 1. random 2. email 3. default and 4. custom.

Simple DDM demo:

create a database, create a table and insert some fake sensitive data in it.

create database DDMTest
-- put your files, options here if needed
use DDMTest;

CREATE TABLE DDMTest.dbo.PersonalInfo (
	PersonalInfoID int NOT NULL IDENTITY(1,1),
	Fname varchar(50) NOT NULL,
	Lname varchar(50) NOT NULL,
	PhoneNumber char(12),
	email varchar(80),
	SSN char(11) NOT NULL,
	CONSTRAINT PK_PersonalInfoID PRIMARY KEY (PersonalInfoID),
	CONSTRAINT UC_SSN UNIQUE (SSN)
) 

INSERT INTO DDMTest.dbo.PersonalInfo
(Fname, Lname, PhoneNumber, email, SSN)
VALUES('Janet', 'Smith', '320-409-1065', 'Jsmith@hatmail.com', '870-71-6633');

INSERT INTO DDMTest.dbo.PersonalInfo
(Fname, Lname, PhoneNumber, email, SSN)
VALUES('Ron', 'Foo', '555-123-6633', 'FooBar@yakhoo.com', '921-66-2211');

INSERT INTO DDMTest.dbo.PersonalInfo
(Fname, Lname, PhoneNumber, email, SSN)
VALUES('Andrea', 'Erchel', '521-671-3322', 'AErchle@Geemail.com', '521-32-4328');

I could have added masking to the table definition during create but here we will add it afterwards using ALTER statements. I will mask [email] using the “email” masking function and [SSN] using the “custom” masking function so I can specify my own mask format. In this case I want the last 4 digits of the SSN visible so I can specify that with the 3rd parameter of the custom masking function. The custom function’s 1st parameter is how many characters in the front you want unmasked and the 2nd parameter is the custom mask you want between what you choose to have unmasked. If you did not want any of the data unmasked then you could just use the default masking function.

/*example function definition FUNCTION = 'partial(prefix # char unmasked,[padding],suffix # char unmasked)'*/
ALTER TABLE DDMTest.dbo.PersonalInfo
	ALTER COLUMN SSN ADD MASKED WITH (FUNCTION = 'PARTIAL(0,"XXX-XX-",4)')

ALTER TABLE DDMTest.dbo.PersonalInfo
	ALTER COLUMN email ADD MASKED WITH (FUNCTION = 'email()')

Now we need to create a user with select access to the table

CREATE user lowpriv WITHOUT LOGIN	

GRANT SELECT ON DDMTest.dbo.PersonalInfo TO lowpriv

run a select on the table as a lower privileged user.

EXECUTE AS USER = 'lowpriv'
SELECT USER_NAME() AS 'WHO AM I?', * from DDMTest.dbo.PersonalInfo

The results for the non-dbo non-unmasked privileged user.

What if we run as DBO?

REVERT
SELECT USER_NAME() AS 'WHO AM I?', * from DDMTest.dbo.PersonalInfo

We can see that dbo can see the data without the mask.

You can alter the column to unmask if you no longer need it obfuscated. Here we will remove the email mask because we think it is acceptable for users to see email addresses.

ALTER TABLE DDMTest.dbo.PersonalInfo
	ALTER COLUMN email DROP MASKED

If we run as lowpriv user again we will now see the email addresses but the SSN is still obfuscated

DDM is a great tool to keep  users from seeing sensitive data. It should not be considered highly secure, or unbreakable. It is not a substitute for encryption.