SQL Server Dynamic Data Masking DDM

Watch the Video

You can watch the short video on Dynamic Data Masking on Youtube. Watch Here

Today, I'm going to walk you through a new feature in SQL Server 2016 called Dynamic Data Masking (DDM). You can read more about it on MSDN for DDM. What is DDM?

Dynamic Data Masking limits the exposure to sensitive data to users that should not see the data being presented. DDM is used in conjunctions with other features in SQL Server such as SQL Auditing and Encryption (TDE and row-level encryption) to provide a completely secure database platform.

Database can be masked in different ways, either Full Masking or Partial Masking and there are functions builtin to provide ease of use like 'email()'.

Keep in mind that although a user may not see all the data, updates to the data (if permission applies to the user) will still be committed.

Permissions
Users with SELECT permission on a table can view the table data. Columns that are defined as masked, will display the masked data. Grant the UNMASK permission to a user to enable them to retrieve unmasked data from the columns for which masking is defined.

Use Cases

  1. Need to mask SSN in a column
  2. Need to mask email address in a column
  3. Need to mask phone number in a column
  4. Need to mask refresh copies of Production database in lower environments
  5. The case for masking data can be endless if you have a over protective Information Security Officer :)

Demo

Our demo today will start with a table in our HR database called employee. The demo will be a small sub-set of data for you to get the general idea on how to use DDM.

Step 1 - Create the employee table with data and mask the email address column

CREATE TABLE DBO.EMPLOYEE(
    EMPLOYEEID INT IDENTITY PRIMARY KEY,
    FIRSTNAME VARCHAR(100),
    LASTNAME VARCHAR(100),
    SSN VARCHAR(20),
    PERSONALEMAIL VARCHAR(100) 
	MASKED WITH (FUNCTION='EMAIL()') NULL);
					


					

					


Step 2.  Insert Sample Data into dbo.employee table






					
INSERT INTO DBO.EMPLOYEE
    SELECT 'JOHN','DOE','123-45-6789','J.DOE@MYCORP.COM'
GO
INSERT INTO DBO.EMPLOYEE
    SELECT 'RALPH','SMITH','123-45-6789','R.SMITH@MYCORP.COM'
GO
					




Step 3.  Create a HRRep User with permission to select from the dbo.employee table







					
CREATE USER [HRREP] WITHOUT LOGIN
GO
GRANT SELECT ON [DBO].[EMPLOYEE] TO [HRREP]
					



Step 4.  Select data from dbo.employee as hrrep and as dbo







					
SELECT *,USER_NAME() [USERNAME] FROM DBO.EMPLOYEE; 
GO 
EXECUTE AS USER = 'HRREP' 
SELECT *,USER_NAME() [USERNAME] FROM DBO.EMPLOYEE 
REVERT;
					











Step 5.  Alter the existing column for SSN data to be masked and only display the last 4 of SSN







					
ALTER TABLE DBO.EMPLOYEE ALTER COLUMN SSN 
	ADD MASKED WITH (FUNCTION='PARTIAL(0,"XXX-XX-",4)')
					



Step 6.  Run our code in Step 4 again







					
SELECT *, USER_NAME() [USERNAME] FROM DBO.EMPLOYEE;
GO
EXECUTE AS USER = 'HRREP'
SELECT *,USER_NAME() [USERNAME] FROM DBO.EMPLOYEE
REVERT
					











Step 5.  Alter the existing column for SSN data to be masked and only display the last 4 of SSN







					
ALTER TABLE DBO.EMPLOYEE ALTER COLUMN SSN 
	ADD MASKED WITH (FUNCTION='PARTIAL(0,"XXX-XX-",4)')
					



Step 6.  Run our code in Step 4 again







					
SELECT *, USER_NAME() [USERNAME] FROM DBO.EMPLOYEE;
GO
EXECUTE AS USER = 'HRREP'
SELECT *,USER_NAME() [USERNAME] FROM DBO.EMPLOYEE
REVERT
					










Step 7.  Drop masked data on personalemail while keeping ssn masked in dbo.employee







					
ALTER TABLE DBO.EMPLOYEE
ALTER COLUMN PERSONALEMAIL DROP MASKED
					


Step 8.  Run Code in Step 4 to verify







					
SELECT *, USER_NAME() [USERNAME] FROM DBO.EMPLOYEE;
GO
EXECUTE AS USER = 'HRREP'
SELECT *,USER_NAME() [USERNAME] FROM DBO.EMPLOYEE
REVERT