SQL Server 2016 – How to use Dynamic Data Masking (DDM)?

Dynamic Data Masking (DDM) is new feature of SQL Server 2016. DDM helps in preventing unauthorized access to sensitive data. DDM enables only privilege users to have complete data. Un-privilege users will have access of MASKED data only.

As an example, a call center support person may identify callers by several digits of their social security number or credit card number, but those data items should not be fully exposed to the support person. A masking rule can be defined that masks all but the last four digits of any social security number or credit card number in the result set of any query. For another example, by using the appropriate data mask to protect personally identifiable information (PII) data, a developer can query production environments for troubleshooting purposes without violating compliance regulations.

Benefits

1. Limited data access increase data security and reduce possibilities of unauthorized data access

2. No change at Application code.

3. Current complex logic in application to MASK sensitive data is no more needed. It greatly simplify the design and coding of security in your application.

4. Data is not changed at storage, It MASKED at runtime on basis of masked function used.

Limitations and Restrictions

1) A masking rule cannot be defined for the following column types:

· Encrypted columns (Always Encrypted)

· FILESTREAM

· COLUMN_SET or a sparse column that is part of a column set.

· Computed column

· A column with data masking cannot be a key for a FULLTEXT index.

2) Backup taken by user without UNMASKED permission, will have UNMASKED data. This is because data masking is dynamically done at time of data view. To secure backups, We have TDE or backup encryption.

3) Data Export done by user without UNMASKED permission will result MASKED data to be exported.

4) Use of SELECT INTO or INSERT INTO by user with UNMAKED permission to copy data from a masked column into another table will copy MASKED data in new table.

Permissions

1. No additional permission needed to create table with DDM columns. Only the standard CREATE TABLE and ALTER on schema permissions will be enough.

2. To Adding, replacing, or removing the mask of a column, requires the ALTER ANY MASK permission and ALTER permission on the table.

3. User with super permissions (like sysadmin, DB_Owner etc.) or UNMASK permission can view the unmaked data.

We have 4 functions provided by Microsoft to create dynamic masked columns for different data types and purpose.

Function	Description
Default	Full masking according to the data types of the designated fields.
	For string data types, use XXXX or fewer Xs if the size of the field is less than 4 characters (char, nchar, varchar, nvarchar, text, ntext).
	For numeric data types use a zero value (bigint, bit, decimal, int, money, numeric, smallint, smallmoney, tinyint, float, and real).
	For date and time data types use 01.01.1900 00:00:00.0000000 (date, datetime2, datetime, datetimeoffset, smalldatetime, time).
	For binary data types use a single byte of ASCII value 0 (binary, varbinary, image).
Email	Masking method which exposes the first letter of an email address and the constant suffix “.com”, in the form of an email address. . aXXX
Random	A random masking function for use on any numeric type to mask the original value with a random value within a specified range.
Custom String	Masking method which exposes the first and last letters and adds a custom padding string in the middle. prefix,[padding],suffix
Custom String	Note: If the original value is too short to complete the entire mask, part of the prefix or suffix will not be exposed.

How to create table with DDM columns?

CREATE TABLE EMPINFO_DDM

(

EMPID INT IDENTITY(1,1),

FIRSTNAME NVARCHAR(50) NOT NULL,

SALART INT,

EMAIL VARCHAR(100),

JOINDATE DATETIME,

FNAME_DEFAULT NVARCHAR(50) MASKED WITH (FUNCTION = ‘DEFAULT()’) NOT NULL,

SALARY_DEFAULT INT MASKED WITH (FUNCTION = ‘DEFAULT()’) NOT NULL,

EMAIL_DEFAULT NVARCHAR(50) MASKED WITH (FUNCTION = ‘DEFAULT()’) NOT NULL,

JOINDATE_DEFAULT DATETIME MASKED WITH (FUNCTION = ‘DEFAULT()’) NOT NULL,

FNAME_EMAIL NVARCHAR(50) MASKED WITH (FUNCTION = ‘EMAIL()’) NOT NULL,

SALARY_EMAIL NVARCHAR(50) MASKED WITH (FUNCTION = ‘EMAIL()’) NOT NULL,

EMAIL_EMAIL NVARCHAR(50) MASKED WITH (FUNCTION = ‘EMAIL()’) NOT NULL,

JOINDATE_EMAIL NVARCHAR(50) MASKED WITH (FUNCTION = ‘EMAIL()’) NOT NULL,

SALARY_RANDOM INT MASKED WITH (FUNCTION = ‘RANDOM(999, 9999)’) NOT NULL,

FNAME_CUSTOM NVARCHAR(50) MASKED WITH (FUNCTION = ‘PARTIAL(0,”XXX-XXXX-XXXX-“,4)’) NOT NULL,

SALARY_CUSTOM NVARCHAR(50) MASKED WITH (FUNCTION = ‘PARTIAL(1,”XXXXXXX”,1)’) NOT NULL,

EMAIL_CUSTOM NVARCHAR(50) MASKED WITH (FUNCTION = ‘PARTIAL(0,”X-X-X”,14)’) NOT NULL,

JOINDATE_CUSTOM NVARCHAR(50) MASKED WITH (FUNCTION = ‘PARTIAL(1,”XXXXXXX”,1)’) NOT NULL

)

How to check if any columns is masked in table?

SELECT NAME COLUMNNAME, IS_MASKED FROM SYS.COLUMNS WHERE OBJECT_NAME(OBJECT_ID) = ‘EMPINFO_DDM’

How to get masked columns & used masked functions details?

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;

How to check table with DDM column data?

You can read table data using simple SELECT command, No change in select command needed.

1) Data view by User with super permissions

2) Data view by User having only select permission on table

What happen if USER with limited access used “SELECT INTO or INSERT INTO to copy data from a masked column into another table “?

If User with limited access use SELECT INTO or INSERT INTO to copy data from a masked column into another table. New table will get masked data as well.