SQL Server 2016 - Dynamic Data Masking

Dynamic Data Masking

DDM is absolutely cool feature which introduced in SQL Server 2016. It is a security feature which hides or obfuscates sensitive data from certain users. Adopting data masking can enable database administrators to raise the level of security and privacy assurance of the database. It is a powerful feature where privacy and data leakage of sensitivity data for organization can be take care.

Dynamic Data Masking or Real Time Masking

It refers to a scenario meant to protect a 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 mean that privileged users still get the results with the actual data, while other users only get determination of what data to return is done on fly, in real time.

Doing Data Masking

It’ll do the followings in my example for data masking:

1. Create a database
2. Create a table with few columns
3. Create the masking with different options
4. Create a user which should have low privilege
5. Checking the effect of data masking

-- create new database

CREATE DATABASE mi_test

GO

USE mi_test

GO

 -- Create table

CREATE TABLE Employee

(

       ID INT IDENTITY(1, 1) PRIMARY KEY,

       First_Name NVARCHAR(50),

       Last_Name NVARCHAR(50),

       Credit_Card VARCHAR(50),

       Salary INT

)

GO

-- insert a row

INSERT INTO Employee

       ( First_Name, Last_Name, Credit_Card, Salary )

       VALUES('Michael','Paul','1234-4567-0007-8945',5000)

GO

-- Enable Data Masking techniques

ALTER TABLE Employee

ALTER COLUMN Credit_Card ADD MASKED

WITH (FUNCTION = 'partial(2,"XX-XXXX-XXXX-",4)')

GO

ALTER TABLE Employee

ALTER COLUMN Salary ADD MASKED

WITH (FUNCTION = 'default()')      -- default on int

GO

ALTER TABLE Employee

ALTER COLUMN First_Name ADD MASKED

WITH (FUNCTION = 'default()')      -- default on varchar

GO

The basic building block is done, now we have to create users to the see effect of data masking on them:

USE mi_test

GO

-- Create user

CREATE USER mi_user WITHOUT LOGIN;

-- Grant select privilege to the user

GRANT SELECT ON Employee TO mi_user

GO

-- shows all the data without data masking because user has the privilege

SELECT * FROM Employee

GO

-- shows all the data with data masking because user has not the privilege

EXECUTE ('SELECT * FROM Employee') AS USER='mi_user';

REVERT;

GO

The below screen shot shows the results:

To know more about Dynamic Data Masking (DDM), please refer the below link:

https://msdn.microsoft.com/en-IN/library/mt130841.aspx