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
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;
