Dynamic Data Masking

Dynamic Data Masking limits sensitive data exposure by masking it to non-privileged users. It helps prevent unauthorized access to sensitive data in real-time without modifying the data in the database.

Introduction to Data Masking

Data masking is a security technique used to protect sensitive data. In SQL Server, Dynamic Data Masking allows you to define rules that mask data based on the user's permissions. This is particularly useful for protecting personally identifiable information (PII), financial data, and other sensitive details.

How Dynamic Data Masking Works

Dynamic Data Masking does not change the data stored in the database. Instead, it alters the data returned to a user when they query a masked column. The original data remains unchanged. This is achieved through the use of masking functions applied to columns.

Key Concepts:

  • Masking Policies: Rules that define how data in a column should be masked.
  • Masking Functions: Predefined functions (e.g., `default()`, `email()`, `partial()`, `random()`) used to transform the data.
  • Unmasked Users: Specific users or roles who are granted permissions to see the original, unmasked data.

Implementing Data Masking

You can implement data masking using Transact-SQL (T-SQL) commands.

Creating a Masked Column

To apply a masking rule to a column, use the ALTER TABLE statement with the ADD MASKED clause.


ALTER TABLE Customers
ALTER COLUMN Email ADD MASKED WITH (FUNCTION = 'email()');

ALTER TABLE Employees
ALTER COLUMN Salary ADD MASKED WITH (FUNCTION = 'default()');

ALTER TABLE Orders
ALTER COLUMN OrderDate ADD MASKED WITH (FUNCTION = 'partial(2, "XXXX", 2)');

Granting Unmask Permissions

To allow specific users to see the unmasked data, grant them the UNMASK permission.


GRANT UNMASK TO [UnmaskedUserRole];

Removing a Mask

To remove a masking rule from a column:


ALTER TABLE Customers
ALTER COLUMN Email DROP MASKED;

Masking Functions Explained

  • default(): Masks the data with a default value based on the column's data type (e.g., 'XXXXX' for strings, 0 for numbers, '1900-01-01' for dates).
  • email(): Masks email addresses to the format like xxx@example.com.
  • partial(prefix, padding, suffix): Masks a portion of the data, leaving specified characters at the beginning and end. For example, partial(2, "XXXX", 2) on "1234567890" would result in "12XXXX7890".
  • random(min, max): Masks numeric data with a random number within a specified range.

Note on Data Types

The behavior of masking functions can vary slightly depending on the underlying data type of the column. Ensure you test the masking with your specific data types.

Use Cases and Benefits

  • Compliance: Helps meet regulatory requirements like GDPR and HIPAA by protecting sensitive data.
  • Reduced Attack Surface: Minimizes the risk of data breaches by limiting exposure of sensitive fields.
  • Developer and Tester Productivity: Allows developers and testers to work with realistic data without exposing sensitive production information.
  • Simplified Security Management: Centralizes masking logic within the database, making it easier to manage.

Important Considerations

Dynamic Data Masking is a client-side masking mechanism. It does not encrypt data or protect against attacks that bypass the database server itself. Always use it in conjunction with other security measures like encryption and proper access control.

Related Topics