Dynamic Data Masking

Thomas Liddle | January 18th, 2017 | SQL, SQL Server 2016 |

Dynamic Data Masking limits the exposure to sensitive data to users that should not see the data being presented. Dynamic Data Masking 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.

You can read more about Dynamic Data Masking on MSDN.

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 – Dynamic Data Masking

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 Dynamic Data Masking.

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;

Dynamic Data Masking - Step 4 - thomasliddledba.com

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

Dynamic Data Masking - Step 6 - thomasliddledba.com

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

Dynamic Data Masking - Step 8 - thomasliddledba.com