Dynamic Data Masking SQL Server 2016

Hi friends, in this post we will discuss a newly added feature by SQL Server team to keep data safe, Dynamic Data Masking SQL Server 2016. Dynamic data masking SQL Server 2016 restrict sensitive data exposure to non-privileged users. Dynamic data masking feature used to hide sensitive data from unauthorised access and the great part is data in database is not changed.

Earlier masking is also done by multiple ways at the application level, but a minor bug leads to exposure of customer information and lead to huge loss. So this feature from SQL Server team is a great news for all of us, as it mask data at database level, and great news is that we can use it with our existing applications without much efforts.

Data is masked with the help of four types of masks available in SQL Server 2016:

Function	Description	Examples
Default	Full masking according to the data types of the designated fields.	Example column definition syntax: Phone# varchar(12) MASKED WITH (FUNCTION = ‘default()’) NULL
	[CTP2.1] For string data types, use XXXX or fewer Xs if the size of the field is less than 4 characters (char, nchar, varchar, nvarchar, text,ntext). The max size is not yet supported.	Example alter syntax: ALTER COLUMN Gender ADD MASKED WITH (FUNCTION = ‘default()’)
	[CTP2.0] String data types supported are: (nchar, nvarchar)
	For numeric data types use a zero value (bigint, bit, decimal, int,money, numeric, smallint, smallmoney, tinyint, float, real).
	For date and time data types use 01.01.2000 00:00:00.0000000 (date,datetime2, datetime, datetimeoffset, smalldatetime, time).
	[CTP2.1] For binary data types use a single byte of ASCII value 0 (binary, varbinary, image).
Email	Masking method which exposes the first letter of an email address and the constant suffix “.com”, in the form of an email address. .aXXX@XXXX.com.	Example definition syntax: Email varchar(100) MASKED WITH (FUNCTION = ’email()’) NULL

		Example alter syntax: ALTER COLUMN Email ADD MASKED WITH (FUNCTION = ’email()’)
Custom String	Masking method which exposes the first and last letters and adds a custom padding string in the middle. prefix,[padding],suffix	Example definition syntax: FirstName varchar(100) MASKED WITH (FUNCTION = ‘partial(prefix,[padding],suffix)’) NULL

	Note: If the original value is too short to complete the entire mask, part of the prefix or suffix will not be exposed.	Example alter syntax: ALTER COLUMN [Phone Number] ADD MASKED WITH (FUNCTION = ‘partial(1,”XXXXXXX”,0)’)

		Additional examples:

		ALTER COLUMN [Phone Number] ADD MASKED WITH (FUNCTION = ‘partial(5,”XXXXXXX”,0)’)

		ALTER COLUMN [Social Security Number] ADD MASKED WITH (FUNCTION = ‘partial(0,”XXX-XX-“,4)’)
Random	A random masking function for use on any numeric type to mask the original value with a random value within a specified range.	Example definition syntax: Account_Number bigint MASKED WITH (FUNCTION = ‘random([start range], [end range])’)

		Example alter syntax: ALTER COLUMN [Month] ADD MASKED WITH (FUNCTION = ‘random(1, 12)’)

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

Limitations of SQL Server Dynamic Data Masking:

Dynamic data masking rule cannot be applied on following column types:

COLUMN_SET
FILESTREM
Encrypted columns

Dynamic Data Masking

To new table:

-----CREATE DATABASE MASKINGDEMO----
CREATE DATABASE MASKINGDEMO
GO
----USE DATABASE MASKINGDEMO----
USE MASKINGDEMO
GO
----CREATE TABLE EMPLOYEE WITH SPECIFYING DYNAMIC DATA MASKING TO COLUMN----
CREATE TABLE EMPLOYEE
(
ID INT IDENTITY(1,1) PRIMARY KEY,
FIRST_NAME VARCHAR(20) MASKED WITH (FUNCTION = 'PARTIAL(0,"XXXXXXX",0)') NULL,
LAST_NAME VARCHAR(20),
RES_ADDRESS VARCHAR(50) MASKED WITH (FUNCTION = 'DEFAULT()') NULL,
SALARY INT MASKED WITH (FUNCTION = 'DEFAULT()') NULL,
PHONE VARCHAR(20) MASKED WITH (FUNCTION = 'DEFAULT()') NULL,
PERSONAL_EMAIL VARCHAR(20) MASKED WITH (FUNCTION = 'EMAIL()') NULL,
OFFICIAL_EMAIL VARCHAR(20),
DOB DATETIME MASKED WITH (FUNCTION = 'DEFAULT()') NULL
)
----INSERT DATA INTO TABLE----
INSERT INTO EMPLOYEE(FIRST_NAME,LAST_NAME,RES_ADDRESS,SALARY,PHONE,PERSONAL_EMAIL,OFFICIAL_EMAIL,DOB) 
VALUES('MANN','SINGH','GURGAON',9000,'9999999999','D@HIGHTECHNOLOGY.COM','M@M.COM','31-MARCH-2000')
----CREATE USER & GRANT SELECT TO USER----
CREATE USER TestUser WITHOUT LOGIN;  
GRANT SELECT ON EMPLOYEE TO TestUser;
----EXECUTE AS USER "TestUser" AND YOU WILL SEE OUR QUERY RESULT SET IS MASKED----
EXECUTE AS USER = 'TestUser';  
SELECT * FROM EMPLOYEE;

To already existing table:

----CREATE TABLE WITHOUT SPECIFYING MASK----
CREATE TABLE EMPLOYEE_OLD
(
ID INT IDENTITY(1,1) PRIMARY KEY,
FIRST_NAME VARCHAR(20) NULL,
LAST_NAME VARCHAR(20),
RES_ADDRESS VARCHAR(50) NULL,
SALARY INT NULL,
PHONE VARCHAR(20)  NULL,
PERSONAL_EMAIL VARCHAR(20)  NULL,
OFFICIAL_EMAIL VARCHAR(20),
DOB DATETIME NULL
)
----INSERT DATA INTO TABLE----
INSERT INTO EMPLOYEE_OLD(FIRST_NAME,LAST_NAME,RES_ADDRESS,SALARY,PHONE,PERSONAL_EMAIL,OFFICIAL_EMAIL,DOB) 
VALUES('JAI','SINGH','GURGAON',90000,'1111111199','DEMO@DEMO.COM','JAI@DEMO.COM','1-APRIL-1980')
----ALTER COLUMN TO ADD MASK----
ALTER TABLE EMPLOYEE_OLD ALTER COLUMN FIRST_NAME ADD MASKED WITH (FUNCTION = 'PARTIAL(0,"XXXXXXX",0)')
ALTER TABLE EMPLOYEE_OLD ALTER COLUMN RES_ADDRESS ADD MASKED WITH (FUNCTION = 'DEFAULT()')
ALTER TABLE EMPLOYEE_OLD ALTER COLUMN SALARY ADD MASKED WITH (FUNCTION = 'DEFAULT()')
ALTER TABLE EMPLOYEE_OLD ALTER COLUMN PHONE ADD MASKED WITH (FUNCTION = 'DEFAULT()')
ALTER TABLE EMPLOYEE_OLD ALTER COLUMN PERSONAL_EMAIL ADD MASKED WITH (FUNCTION = 'EMAIL()')
ALTER TABLE EMPLOYEE_OLD ALTER COLUMN DOB ADD MASKED WITH (FUNCTION = 'DEFAULT()')
 ----CREATE USER & GRANT SELECT TO USER----
CREATE USER TestUser_OLD WITHOUT LOGIN;  
GRANT SELECT ON EMPLOYEE_OLD TO TestUser_OLD;
----EXECUTE AS USER "TestUser_OLD" AND YOU WILL SEE OUR QUERY RESULT SET IS MASKED----
EXECUTE AS USER = 'TestUser_OLD';  
SELECT * FROM EMPLOYEE_OLD;

How to remove mask:

ALTER TABLE EMPLOYEE
ALTER COLUMN Last_Name DROP MASKED;