Here’s a new feature that I like very much: Limiting exposure of sensitive data like user emails, phone numbers, addresses, credit card numbers and so on.

This feature has been available in the Azure SQL Database for a while and now it is included in the new SQL Server 2016 . So let’s see it in action…

Step 1: Defining Masking Rules

We’ll start with a typical Users table:

CREATE TABLE [dbo].[Users](
[UserId] [int] IDENTITY(1,1) NOT NULL PRIMARY KEY,
[Username] [nvarchar](50) NOT NULL,
[FirstName] [nvarchar](50) NOT NULL,
[LastName] [nvarchar](50) NOT NULL,
[Email] [nvarchar](50) ,
[Phone] [nvarchar](15)
)

Suppose that we want to restrict visibility of the Email and Phone columns, we can execute the following statements:

ALTER TABLE dbo.Users
ALTER COLUMN Email ADD MASKED WITH (FUNCTION = 'email()');
ALTER TABLE dbo.Users
ALTER COLUMN Phone ADD MASKED WITH (FUNCTION = 'partial(0,"***-***-****",0)');

The Users table definition now looks like this:

CREATE TABLE [dbo].[Users](
[UserId] [int] IDENTITY(1,1) NOT NULL PRIMARY KEY,
[Username] [nvarchar](50) NOT NULL,
[FirstName] [nvarchar](50) NOT NULL,
[LastName] [nvarchar](50) NOT NULL,
[Email] [nvarchar](50) MASKED WITH (FUNCTION = 'email()') NULL,
[Phone] [nvarchar](15) MASKED WITH (FUNCTION = 'partial(0, "***-***-****",0)') NULL,
)

Now let’s add some data:

INSERT INTO Users
VALUES ('u1', 'John', 'Doe', 'johndoe@contoso.com', '555-6666'),
('u2', 'Don', 'Smith', 'dsmith@contoso.com', '555-7777'),
('u3', 'Samantha', 'Stone', 'sam@contoso.com', '555-8888')
SELECT * FROM Users

So far so good, now let’s move on to …

Step 2: Set Permissions

When non-admin users with SELECT permissions run a query on this table, they will see masked data by default:

CREATE USER user1 WITHOUT LOGIN;
GRANT SELECT TO user1;
EXECUTE AS USER='user1';
SELECT * FROM Users;
REVERT;

Now, in order to allow specific users to see emails and phone numbers, we need to grant them the new UNMASK permission:

CREATE USER user2 WITHOUT LOGIN;
GRANT SELECT TO user2;
GRANT UNMASK TO user2;
EXECUTE AS USER='user2';
SELECT * FROM Users;
REVERT;

There we go, clean and easy. No custom UDFs necessary.

Masking Functions

There are 4 pre-defined masking functions:

EMAIL – exposes only the first letter and the domain, e.g.: sXXX@XXXX.com

PARTIAL – allows definition of custom patterns like in our example above

DEFAULT – masks data by using default characters according to the data type: X for strings, 0 (zero) for numeric values and 01.01.2000 for dates

RANDOM – replaces numeric values with random numbers from a specified range, for example:

ALTER TABLE Users
ADD Income MONEY MASKED WITH (FUNCTION = 'random(1000,2000)') NULL
UPDATE Users SET Income = 100000 WHERE UserName='u1';
UPDATE Users SET Income = 120000 WHERE UserName='u2';
UPDATE Users SET Income = 150000 WHERE UserName='u3';
EXECUTE AS USER='user1';
SELECT * FROM Users;
REVERT;

Further Reading

There’s currently not too many articles about DDM but this one on MSDN is all you need to understand and implement:

MSDN page

Happy coding!