Masking Your Data with SQL Server 2016

There are times when you want some users to see part of a column value but not the whole thing, but you want other users to see the complete column value without any masking.  For example you may want to only allow your call center staff to only see the last 4 digits for a credit card number, with the rest of the credit card number masked with 9’s.  Whereas you want your managers to see column values in an unmasked format.   Or you want the same call center employees to only see email addresses where the middle of an email address is masked with X’s, but the first character and the domain suffix contain unmasked character values.  The new SQL Server 2016 Dynamic Data Masking feature will allow you to define these different kinds of masking rules.

Using Dynamic Data Masking obfuscates column values at the database layer prior to returning the column values to the client application.   Therefore no application code is needed to mask data.  SQL Server has four different masking options: default, email, random, and custom.  The different masking options have different masking characteristics, and support different data types.  By using one of these different masking options you will be able to mask your data any way you want in order to support your masking requirements.

Returning Masked and Unmasked Data

Masking rules are defined as part of the table metadata.  The data is masked as it is returned to the client application based on the database rights of the user reading the data.  The following code defines and populates a table that contains masking rules. This code is split into five parts.  The first part creates a table that contains masking rules in tempdb.  The second part creates two database users: CallCenter, and Manager.  The third part provides access rights to the two database users.  The fourth part shows how the CallCenter user sees masked data, whereas the Manager user can see all the data.  Lastly this code cleans up objects from tempdb.

USE tempdb;

GO 

-- Part 1: Create Sample data

CREATE TABLE MaskingDemo

  (EmailAddr varchar(100) 

      MASKED WITH (FUNCTION = 'email()'),

   CreditCardNum varchar(19) MASKED 

      WITH (FUNCTION = 'partial(0,"9999-9999-9999-",4)'),

  );

INSERT INTO MaskingDemo values 

       ('Greg@DatabaseJournal.com', '1234-5678-9012-3456');

-- Part 2: Create Database User 

CREATE USER CallCenter WITHOUT LOGIN;

CREATE USER Manager WITHOUT LOGIN;

-- Part 3: Provide Access Rights

GRANT SELECT ON MaskingDemo TO CallCenter, Manager;

GRANT UNMASK TO Manager;

-- Part 4: Display Data for Different Users

EXECUTE AS USER = 'CallCenter';

SELECT EmailAddr, CreditCardNum FROM MaskingDemo;

REVERT;

EXECUTE AS USER = 'Manager';

SELECT * FROM MaskingDemo;

REVERT; 

-- Part 5: Cleanup

DROP TABLE MaskingDemo;

DROP USER CallCenter;

DROP USER Manager;

See all articles by Greg Larsen