Dynamic Data Masking in SQL Server

What is Dynamic Data Masking?

Dynamic Data Masking (DDM) is a security feature in SQL Server that helps protect sensitive data in real time by obfuscating the data from users who do not have the appropriate access rights. It allows the selective masking the sensitive data from users without modifying the underlying data itself. This helps in protecting data privacy and confidentiality in environments where sensitive data is accessed by multiple users with varying levels of clearance.

In this article, we will discuss the concept of Dynamic Data Masking in SQL Server, its benefits, and examples.

How can Dynamic Data Masking be implemented?

DDM can be implemented by using one of four masking functions: default, email, random, and custom. These functions allow you to customize the way in which the data is masked based on the data type and sensitivity level of the data.

Default Masking: The default masking function is used when you want to mask sensitive data with a predefined mask. For example, you can use the default masking function to mask all characters of a Credit Card Number except for the last four digits.
Email Masking: The email masking function is used when you want to mask an email address but still make it recognizable as an email address. For example, you can use the email masking function to mask the domain name of an email address.
Random Masking: The random masking function is used when you want to mask data with a random value. For example, you can use the random masking function to mask a Year in Date of Birth with a random value.
Custom Masking: The custom masking function is used when you want to create a custom mask for sensitive data. For example, you can use the custom masking function to mask a phone number with a custom format.

How to create Masking functions?

Let's see, with an example, create a table with default, email, random, and custom masking functions and insert some records by running the following command.

--Create table with masking functions
CREATE TABLE dbo.DDM_TestTable(
    Id        INT IDENTITY(1,1) NOT NULL PRIMARY KEY CLUSTERED,
    FirstName VARCHAR(100) MASKED WITH (FUNCTION = 'PARTIAL(1, "xxx", 1)') NULL,
    LastName  VARCHAR(100) NOT NULL,
    Phone     VARCHAR(10) MASKED WITH (FUNCTION = 'DEFAULT()') NULL,
    Email     VARCHAR(100) MASKED WITH (FUNCTION = 'EMAIL()') NOT NULL,
    BirthYear SMALLINT MASKED WITH (FUNCTION = 'RANDOM(1000, 9999)') NULL
    );

--Inser sample records
INSERT INTO dbo.DDM_TestTable (FirstName, LastName, Phone, Email, BirthYear)
VALUES ('Naveen', 'Kumar', '9876543210', '[email protected]', 1982),  
('Praveen', 'Kumar', '9128374650', '[email protected]', 1991),  
('Ram', 'Prabhu', '9021873456', '[email protected]', 1989),  
('Karthick', 'M', '8907654321', '@ddm.in">[email protected]', 1985);

Create a User and Grant the SELECT permission on the schema where the table resides by execution the following command.

CREATE USER DDMUser WITHOUT LOGIN;

GRANT SELECT ON SCHEMA::dbo TO DDMUser;

Execute the query as the DDMUser to view masked data from the table.

EXECUTE AS USER = 'DDMUser' 

SELECT * FROM dbo.DDM_TestTable

Output of the query.

Query Output

Benefits of Dynamic Data Masking

Dynamic Data Masking provides several benefits to organizations that need to protect sensitive data. Some of these benefits.

Enhanced Security: Dynamic Data Masking helps protect sensitive data by obfuscating it from unauthorized users. This helps prevent data breaches and unauthorized access to sensitive data.
Improved Compliance: Many organizations are required to comply with data privacy regulations such as GDPR and HIPAA. Dynamic Data Masking helps organizations comply with these regulations by protecting sensitive data from unauthorized access.
Reduced Risk: By masking sensitive data, Dynamic Data Masking reduces the risk of data theft and other security breaches. This helps protect the reputation of the organization and helps maintain customer trust.
Increased Flexibility: Dynamic Data Masking can be used to selectively mask data based on user roles and permissions. This provides greater flexibility in controlling access to sensitive data.

What new feature was added in SQL Server 2022(16.x) for DDM?

New masking function datetime introduced.
Granular permission introduced, we can grant or revoke UNMASK permission at the database level, schema level, table level, or column level to a user or database role.

Conclusion

In conclusion, Dynamic Data Masking is a useful feature in SQL Server that enables you to mask sensitive data and control access to it. By using it in combination with other security features, such as role-based access control, you can create a more secure database environment.