Dynamic Data Masking

Dynamic Data Masking (DDM) limits sensitive data exposure by masking it to non-privileged users. DDM can be used to define the data that is masked, and the conditions under which it is masked. DDM does not change the data in the database; it only affects how the data is retrieved by queries.

Overview

DDM is a security feature that helps prevent unauthorized access to sensitive data, such as credit card numbers or personally identifiable information (PII), by obscuring it in query results. It's particularly useful for applications where specific users might need access to data schemas but not the actual sensitive values within those columns.

Key Concepts

• Masking Rule: Defines how data in a column is masked.
• Masking Functions: Predefined functions to mask data (e.g., default(), partial(), email(), number()).
• Permissions: The UNMASK permission allows users to see the original data.

Implementing Dynamic Data Masking

Creating a Masked Column

You can apply a masking rule to a column when you create a table or alter an existing table.

Example: Creating a table with a masked email column

CREATE TABLE Customers (
    CustomerID INT PRIMARY KEY,
    FirstName VARCHAR(50),
    LastName VARCHAR(50),
    Email VARCHAR(100) MASKED WITH (FUNCTION = 'email()')
);
            

Example: Applying masking to an existing column

ALTER TABLE Employees
ALTER COLUMN SocialSecurityNumber VARCHAR(11) MASKED WITH (FUNCTION = 'partial(3, "xxx-xx-", 4)');
            

This example masks the middle part of the Social Security Number, showing the first 3 digits and the last 4 digits, with "xxx-xx-" in between.

Supported Masking Functions

• default(): Displays the default value for the data type of the column. For string types, it displays an empty string. For numeric types, it displays 0. For date/time types, it displays the minimum value for the data type.
• partial(, , , ): Exposes a specified number of characters from the beginning and end of a string, filling the middle with a specified character.
• email(): Masks the email address with xxx@example.com.
• number(, , ): Masks numeric data. For example, number(1000, 9999, 'XXXX') could mask a credit card number.

Managing Permissions

Users without the UNMASK permission will see the masked data. Users with the UNMASK permission will see the original, unmasked data.

Granting UNMASK permission

GRANT UNMASK TO UserWithUnmaskPermission;
            

Revoking UNMASK permission

REVOKE UNMASK FROM UserWithUnmaskPermission;
            

Use Cases

• Protecting sensitive customer data (PII, financial information) in non-production environments.
• Enhancing security for applications that display sensitive data to a broad audience.
• Meeting regulatory compliance requirements.

Further Reading

• Microsoft Docs: Dynamic Data Masking
• Learn about Always Encrypted