Basics of Using Dynamic Data Masking

When SQL Server 2016 was released, I was interested in finding some of the cool capabilities as part of the release. In this blog, we will look at a simple example. Data Masking is a security feature which hides or obfuscates sensitive data from certain users. Adopting a data masking solution can enable database owners/administrators to raise the level of security and privacy assurance of their database – in addition to meeting compliance requirements with various security and privacy standards.

Dynamic Data Masking or Real Time Masking

This refers to a scenario meant to protect a production database, by returning obscured data on queries performed by certain users, as per defined by the masking policy. The actual data on the database is not changed. This means that ‘privileged’ users still get results with the actual data, while other users only get access to data to which they are entitled – again, based on the defined policies. Naturally, this implies that the determination of what data to return is done on the fly, in real time.

Data Masking in Action

I have started to experiment this feature on a CTP2.2 and I have not used the trace flags that you see on the internet. We will do the following:

1. Create a demo database for use
2. Create a table with a number of columns
3. Create the masking with different options
4. Create a login/user which will have low privileges
5. Check the effect of data masking

SET NOCOUNT ON
GO
-- drop database DataMaskingDB - if already exists
USE [master]
GO
IF DB_ID('DataMaskingDB') IS NOT NULL
BEGIN
 DROP DATABASE [DataMaskingDB]
END
-- create new database called DataMaskingDB
CREATE DATABASE DataMaskingDB
GO
USE DataMaskingDB
GO

CREATE TABLE Employees (
ID INT IDENTITY(1, 1) PRIMARY KEY
,FirstName NVARCHAR(30) NOT NULL
,LastName NVARCHAR(30) NOT NULL
,Credit_Card VARCHAR(20) NULL
,Salary INT NULL
,OffEmail NVARCHAR(60) NULL
,PerEmail NVARCHAR(60) NULL
,DOB DATETIME NULL
)
-- insert a row
INSERT INTO [dbo].[Employees]
(FirstName, LastName, Credit_Card, Salary, OffEmail, PerEmail, DOB)
VALUES('Pinal','Dave','1234-8765-1234-8765',987654,'[email protected]','[email protected]', '30-September-1980')
GO

-- Apply Data Masking techniques
ALTER TABLE Employees
ALTER COLUMN Credit_Card ADD MASKED
WITH (FUNCTION = 'partial(2,"XX-XXXX-XXXX-",4)')

ALTER TABLE Employees
ALTER COLUMN Salary ADD MASKED
WITH (FUNCTION = 'default()')      -- default on int

ALTER TABLE Employees
ALTER COLUMN DOB ADD MASKED
WITH (FUNCTION = 'default()')      -- default on date

ALTER TABLE Employees
ALTER COLUMN FirstName ADD MASKED
WITH (FUNCTION = 'default()')      -- default on varchar

ALTER TABLE Employees
ALTER COLUMN OffEmail ADD MASKED
WITH (FUNCTION = 'email()')
GO

Now that the basic building blocks have been done. We will next create the user for our experimentation and see the effect of data masking on them.

USE DataMaskingDB
GO
CREATE USER StandardUser WITHOUT LOGIN;
GRANT SELECT ON Employees TO StandardUser;
GO

USE DataMaskingDB
GO
SELECT * FROM Employees; -- this would show clear data
GO
EXECUTE AS USER = 'StandardUser';
SELECT * FROM Employees;     -- this should show masked data
REVERT;
GO

The output for above will look like this:

As you can see, SQL Server 2016 introduces a powerful feature where privacy and data leakage of sensitive data for organizations can be taken care. I am sure there are compelling reasons where I see developers use this capability inside their application that it is worth a try.

Stay tuned for upcoming blogs on some of the new features of SQL Server 2016 and my impressions of the sneak peek from Microsoft SQL Server 2016.