Dynamic Data Masking

Dynamic Data Masking

Dynamic data masking feature that Microsoft implemented in SQL Server 2016 version.

Dynamic data masking (DDM) limits sensitive data exposure by masking it to non-privileged users. It can be used to greatly simplify the design and coding of security in your application.

Dynamic data masking helps prevent unauthorized access to sensitive data by enabling customers to specify how much sensitive data to reveal with minimal impact on the application layer. DDM can be configured on designated database fields to hide sensitive data in the result sets of queries. With DDM the data in the database is not changed. DDM is easy to use with existing applications, since masking rules are applied in the query results. Many applications can mask sensitive data without modifying existing queries.

• A central data masking policy acts directly on sensitive fields in the database.
• Designate privileged users or roles that do have access to the sensitive data.
• DDM features full masking and partial masking functions, and a random mask for numeric data.
• Simple Transact-SQL commands define and manage masks.

The purpose of dynamic data masking is to limit exposure of sensitive data, preventing users who should not have access to the data from viewing it. Dynamic data masking does not aim to prevent database users from connecting directly to the database and running exhaustive queries that expose pieces of the sensitive data. Dynamic data masking is complementary to other SQL Server security features (auditing, encryption, row level security...) and it is highly recommended to use it in conjunction with them in order to better protect the sensitive data in the database.

Defining a Dynamic Data Mask

A masking rule may be defined on a column in a table, in order to obfuscate the data in that column. Four types of masks are available.

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, 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 that exposes the first letter of an email address and the constant suffix ".com", in the form of an email address. aXXX@XXXX.com.
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 that exposes the first and last letters and adds a custom padding string in the middle. prefix,[padding],suffix Note: If the original value is too short to complete the entire mask, part of the prefix or suffix will not be exposed.

Limitations and Restrictions

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.

·         A mask cannot be configured on a computed column, but if the computed column depends on a column with a MASK, then the computed column will return masked data.

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

·         A column in a PolyBase external table.

Querying for Masked Columns

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;

Create database

--Create a database

Use master

go

Create database Masking

go

Use Masking

Go

-- schema to contain user tables

CREATE SCHEMA Data;

GO

-- table with masked columns

CREATE TABLE Data.Membership(

    MemberID        int IDENTITY(1,1) NOT NULL PRIMARY KEY CLUSTERED,

    FirstName        varchar(100) MASKED WITH (FUNCTION = 'partial(1, "xxxxx", 1)') NULL,

    LastName        varchar(100) NOT NULL,

    Phone            varchar(12) MASKED WITH (FUNCTION = 'default()') NULL,

    Email            varchar(100) MASKED WITH (FUNCTION = 'email()') NOT NULL,

    DiscountCode    smallint MASKED WITH (FUNCTION = 'random(1, 100)') NULL

    );

-- inserting sample data

INSERT INTO Data.Membership (FirstName, LastName, Phone, Email, DiscountCode)

VALUES  

('Roberto', 'Tamburello', '555.123.4567', 'RTamburello@contoso.com', 10), 

('Janice', 'Galvin', '555.123.4568', 'JGalvin@contoso.com.co', 5), 

('Shakti', 'Menon', '555.123.4570', 'SMenon@contoso.net', 50), 

('Zheng', 'Mu', '555.123.4569', 'ZMu@contoso.net', 40);

-- get records from table

select * from Data.Membership

A new user is created and granted the SELECT permission on the schema where the table resides. Queries executed as the MaskingTestUser view masked data.

-- Creating user without login

CREATE USER MaskingTestUser WITHOUT LOGIN;

-- Grant select AND insert permissions to MaskingTestUser

GRANT SELECT ON SCHEMA::Data TO MaskingTestUser;

GRANT INSERT ON SCHEMA::Data TO MaskingTestUser;

-- impersonate for testing:

EXECUTE AS USER = 'MaskingTestUser'; 

SELECT * FROM Data.Membership; 

REVERT;

--Granting Permissions to View Unmasked Data

Granting the UNMASK permission allows MaskingTestUser to see the data unmasked.

--Granting Permissions to View Unmasked Data

GRANT UNMASK TO MaskingTestUser; 

EXECUTE AS USER = 'MaskingTestUser'; 

SELECT * FROM Data.Membership; 

REVERT; 

-- Removing the UNMASK permission 

REVOKE UNMASK TO MaskingTestUser;

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;

-- Dropping a Dynamic Data Mask

ALTER TABLE Data.Membership  

ALTER COLUMN FirstName DROP MASKED;