New features in SQL 2016: Dynamic data masking

As a running up to the new release of SQL 2016 I would like to share some research I did about the new features that will be available. Today I want to talk about Dynamic Data Masking. It is a nifty, small but quite interesting new feature.

Books Online describes it as this:
Dynamic data masking limits sensitive data exposure by masking it to non-privileged users. Dynamic data masking helps prevent unauthorized access to sensitive data by enabling customers to designate how much of the sensitive data to reveal with minimal impact on the application layer. It’s a data protection feature that hides the sensitive data in the result set of a query over designated database fields, while the data in the database is not changed. Dynamic data masking is easy to use with existing applications, since masking rules are applied in the query results. Many applications can mask sensitive data without modifying existing queries.

So what does it not do? It’s not an encryption method. It really does what it says.

So masking. What does that mean?

It’s easier to give an example. After saving a credit card into your paypal account, you will never see your full credit card number again on their website.

It will always be shown like XXXX XXXX XXXX 1234, although they know the full number. That is data masking. This method is now also available in SQL 2016. In this short article I would like to describe how you can configure this.

First of all, what are the benefits?

If you are working with sensitive data, like credit card numbers or social security numbers. It’s never a good idea to show them in your application. Since you never know who is watching your screen. A second benefit is that the data that is send over the network is worthless. If a hacker catches the data, he sees a lot of XXXXXXX and a small part of the real data. So the benefits all have to do with Security.
Microsoft has foreseen 4 different masking types:

1. Default
   Everything will be masked depending on your data type Characters will be converted to X’s. Numbers will be converted to 0’s.
   Note: Masking will only be 4 characters long.
2. Email
   First character of the email address will be readable together with the suffix (.com, .co.uk, …)
3. Custom
   This type has 3 parameters.
   i. How many prefix characters need to be readable?
   ii. How many suffix characters need to be readable?
   iii. What do you want to show in between?
4. Random
   Converts any numeric data type into a number in a certain range.

Following example shows the use of all of them. Our data, a table that contains a list of all the employees in a certain company. It includes the account number where their wages are paid on, and there social security number for insurance reason.

CREATE TABLE Employee(
EmployeeID INT IDENTITY PRIMARY KEY,
FirstName NVARCHAR(100),
LastName NVARCHAR(100),
Email NVARCHAR(100),
BankAccountNumber BIGINT,
SocialSecurityNumber NVARCHAR(20)
)

INSERT INTO Employee(FirstName, LastName, Email, BankAccountNumber, SocialSecurityNumber)
VALUES
(‘John’, ‘Doe’,’john.doe@domain.com’, 3219876512, ‘123-456-789’),
(‘Jane’, ‘Doe’,’jane.doe@domain.com’, 9876543210, ‘321-654-987’),
(‘Lewis’, ‘Doe’,’lewis.doe@domain.com’, 3754796548, ‘789-123-456’)

SELECT * FROM Employee

This is what we were used to. Now let’s implement the data masking:

CREATE TABLE Employee(
EmployeeID INT IDENTITY PRIMARY KEY,
FirstName NVARCHAR(100) MASKED WITH (FUNCTION = ‘partial(1, “……”, 1)’),
LastName NVARCHAR(100),
Email NVARCHAR(100) MASKED WITH (FUNCTION = ’email()’),
BankAccountNumber BIGINT MASKED WITH (FUNCTION = ‘random(1000000000,9999999999)’),
SocialSecurityNumber NVARCHAR(20) MASKED WITH (FUNCTION = ‘default()’)
)

INSERT INTO Employee(FirstName, LastName, Email, BankAccountNumber, SocialSecurityNumber)
VALUES
(‘John’, ‘Doe’,’john.doe@domain.com’, 3219876512, ‘123-456-789’),
(‘Jane’, ‘Doe’,’jane.doe@domain.com’, 9876543210, ‘321-654-987’),
(‘Lewis’, ‘Doe’,’lewis.doe@domain.com’, 3754796548, ‘789-123-456’)

SELECT * FROM Employee

As you noticed the result of our query still returns all the data. Since you created the table, you are the owner and you can see everything. We need a user that only has SELECT permissions on that table.

CREATE USER PublicUser WITHOUT LOGIN;
GRANT SELECT ON Employee TO PublicUser;

EXECUTE AS USER = ‘PublicUser’;
SELECT * FROM Employee;
REVERT;

And now we see that the data is masked.

FirstName is replaced by the first letter of the name, the last letter of the name and in between some dots. Email is replaced by the first letter, the suffix and all the rest became X’s. BankAccountNumber has been replaced with a random number in the range we gave and the Social SecurityNumber is fully replaced by XXXX.

If you want to give a user the privilege to unmask data, then you need to grant him UNMASK permissions.

GRANT UNMASK TO PublicUser;

EXECUTE AS USER = ‘PublicUser’;
SELECT * FROM Employee;
REVERT;

Revoking can be done by this:

REVOKE UNMASK TO PublicUser;

Microsoft has foreseen a new system table (sys.masked_columns) to get data about the masked columns.

SELECT t.name, mc.name, mc.masking_function
FROM sys.tables t
JOIN sys.masked_columns mc
ON t.object_id = mc.object_id
WHERE t.name = ‘Employee’

Don’t forget that this does not change anything to how to update or insert data in those columns. You just enter the correct data; SQL will mask it. It’s also good to know that if you do a SELECT INTO or INSERT INTO, the data will be copied masked if you don’t have the UNMASK permissions.

I hope you found this short blogpost interesting. Next time I will write about another new feature namely ‘Row-Level-Security’.