In your SQL table ‘Employee’, you may have sensitive information such as credit card, passport no, mobile phone…etc and you don’t want to expose the sensitive information to non-privileged users but at the same time you wish to have access to remaining column such as EmployeeID, location, Employee name to every user having database/table level permission.

A new feature “Dynamic Data Masking (DDM)” introduced with SQL 2016 may solve above requirement without having complex set of front end OR DB level coding. DDM limits sensitive data exposure by masking it to non-privileged users.

There are four type of masking can be configure during table creation.

Default Masks the data according to the field data type; if the field data type is binary, varbinary or image, a single byte of binary value 0 will be used to mask that field. For the date and time data types, the 01.01.1900 00:00:00.0000000 value will be used to mask that date field. If the data type of the masked field is one of the numeric data types, a zero value will be used to mask that field.

Partial Define your own mask for the specified field by exposing the first and last letters defined by the prefix and suffix and add a padding that will be shown in the middle in the form of prefix

Email Mask the fields that store the email address.

Random Mask any numeric data type by replacing the original value with a random value within the range specified in that function.

A table definition will look like below.

/*First create a dedicate database*/
USE [master]
CREATE DATABASE DDMDEMO
GO

/*Create table with Dynamic Data Masking enabled*/
USE [DDMDEMO]
CREATE TABLE EmployeeData
(
EMPID INT   MASKED WITH (FUNCTION ='random(1, 12)') IDENTITY(1,1), ---Randam mask, int will be replace by any random valube between 1 to 12
FIRSTNAME VARCHAR(100), --Leave this column without masking. 
LASTNAME VARCHAR (100) MASKED WITH (FUNCTION='partial(2, "...", 2)') NULL, ---Example of random mask, first two character ofstart & end will show and middle characters will be masked.  
PHONENo VARCHAR(50) MASKED WITH (FUNCTION='default()') NULL, ---Example of default mask.
EMAIL VARCHAR(100) MASKED WITH (FUNCTION = 'email()') NULL ---Example of email masking function
)
GO

Populate table with few rows.

USE [DDMDEMO]
INSERT INTO EmployeeData 
 VALUES
('Pradeep', 'Papnai', '919595852246', 'papnai.pradeep@gmail.com'),
('Darhan', 'Bandodkar','91959568524','darshan.bandokar@hotmail.com'),
('Vikram', 'Jadhav', '915682147562', 'Vikram.jadhav@hotmail.com'),
('Ajay', 'Jain', '9545684575','ajay.jain@yahoo.com'),
('Yogesh','Ghute', '959586575826', 'yogesh.ghute@city.com')
GO

By default, DBO will have unmask permission and he can retrieve all columns.

USE [DDMDEMO]
SELECT EMPID, FIRSTNAME, LASTNAME, PHONENo, EMAIL FROM EmployeeData 
GO

Now create a ‘TestUser’ without login and give ‘Select’ permission on the table.

CREATE USER TestUser WITHOUT LOGIN
GRANT SELECT ON EmployeeData TO TestUser
GO

Retrieve rows using ‘Testuser’ credential.

EXECUTE AS USER = 'TestUser'
USE [DDMDEMO]
SELECT EMPID, FIRSTNAME, LASTNAME, PHONENo, EMAIL FROM EmployeeData 
REVERT
GO

The output would be same as you might have expected while defining masking for columns ‘LastName’, ‘Phoneno’ & ‘Email’.

By default, DBO have un-mask privilege and this permission can be assign to any other legitimate user. Let’s do the same using query.

GRANT UNMASK TO TestUser
EXECUTE AS USER = 'TestUser'
USE [DDMDEMO]
SELECT EMPID, FIRSTNAME, LASTNAME, PHONENo, EMAIL FROM EmployeeData 
REVERT
GO

dboretrive

Few things to note:-

Creating a mask on a column does not prevent updates to that column. So although users receive masked data when querying the masked column, the same users can update the data if they have write permissions.
A masking rule cannot be defined for the column types such as Encrypted columns OR FILESTREAM
Use the masked_columns view to query for table-columns that have a masking function applied to them

USE DDMDEMO
SELECT c.name, tbl.name as table_name, c.is_masked, c.masking_function  
FROM sys.masked_columns AS c  
JOIN sys.tables AS tbl   
    ON c.[object_id] = tbl.[object_id]  
WHERE is_masked = 1;

More details about DDM

#ddm, #dymamic-data-masking, #sql, #sql-mask, #sql2016