SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 

Dynamic Data Masking

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

s1

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;

s2

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;

s1

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;

s3

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!

 

 


Gorandalf's SQL Blog

Goran is a SQL Server/Microsoft Data Platform professional. He started working with the SQL version 6.5 many years ago and has worked in various DB-related roles from database administrator to database developer to Data Warehouse and BI architect. He's a member of the local chapter of PASS in in Munich, Germany and often attends SQL Saturdays and similar events in the region.

Comments

Leave a comment on the original post [gorandalf.wordpress.com, opens in a new window]

Loading comments...