Data masking is a valuable tool for protecting sensitive data in SQL Server. It involves replacing sensitive data with fake, but realistic, data for the purpose of testing or demonstration. This helps to prevent unauthorized access to sensitive information and ensures the security of your database.
There are two main ways to apply data masking in SQL Server: using the MASKED WITH statement and using data masking functions in a SELECT, INSERT, or UPDATE statement.
Using the MASKED WITH Statement
The MASKED WITH statement is used to apply data masking to a column in a table. It allows you to specify a data masking function that will be used to mask the data in the column when it is selected by a user.
Here is an example of how to use the MASKED WITH statement to mask the credit_card_number column in the customers table:
|
1 2 |
ALTER TABLE customers ALTER COLUMN credit_card_number ADD MASKED WITH (FUNCTION = 'default(####-####-####-1234)'); |
This statement uses the DEFAULT() function to replace the actual credit card numbers with a fake one.
You can also use the MASKED WITH statement to mask multiple columns at once by using the ALTER TABLE statement with multiple ALTER COLUMN clauses. For example:
|
1 2 3 4 5 6 |
ALTER TABLE customers ALTER COLUMN first_name ADD MASKED WITH (FUNCTION = 'partial(2,3,"xxxxx")') ALTER COLUMN last_name ADD MASKED WITH (FUNCTION = 'partial(2,3,"xxxxx")') ALTER COLUMN email ADD MASKED WITH (FUNCTION = 'email("xxxxx","example.com")') ALTER COLUMN phone_number ADD MASKED WITH (FUNCTION = 'random(10000)') ALTER COLUMN address ADD MASKED WITH (FUNCTION = 'partial(2,3,"xxxxx")'); |
In this example, the PARTIAL() function is used to mask the middle of the first and last names and the address, the EMAIL() function is used to mask the username and domain of the email addresses, and the RANDOM() function is used to generate random phone numbers.
It’s important to note that the MASKED WITH statement only applies data masking when the data is selected by a user. The actual data in the database is not changed, so it is still important to use data masking functions or techniques when inserting or updating data in the database.
Using Data Masking Functions
In addition to the MASKED WITH statement, SQL Server provides a number of data masking functions that you can use in SELECT, INSERT, or UPDATE statements. These functions allow you to mask specific values or parts of values in your data.
Here is a list of some of the data masking functions that are available in SQL Server:
DEFAULT(): Returns a default value for a column. This function can be used to replace null or empty values with a specified default value.
PARTIAL(): Returns a partial value for a column. This function can be used to mask part of a column while leaving the rest of the value unchanged.
EMAIL(): Returns a masked email address. This function can be used to replace the username and domain of an email address with fake values, while leaving the rest of the address unchanged.
RANDOM(): Returns a random value for a column. This function can be used to replace the values in a column with random values.
Here is an example of how these functions can be used in a SELECT statement:
|
1 2 3 4 5 6 7 8 |
SELECT customer_id, DEFAULT(first_name, 'xxxxx') AS first_name, PARTIAL(last_name, 3, 4) AS last_name, EMAIL(email, 'xxxxx', 'example.com') AS email, RANDOM(credit_card_number) AS credit_card_number, DEFAULT(phone_number, RANDOM(10000)) AS phone_number FROM customers; |
In this example, the DEFAULT() function is used to replace null or empty first names with a default value of ‘xxxxx’, the PARTIAL() function is used to mask the middle of the last names, the EMAIL() function is used to mask the username and domain of the email addresses, the RANDOM() function is used to generate random credit card numbers, and the DEFAULT() function is used to replace null or empty phone numbers with a random value between 0 and 10000.
It’s important to note that these functions are just a few examples of the many data masking functions that are available in SQL Server. There are many more functions that can be used to mask different types of data, such as dates, times, and addresses. It’s important to choose the right function for the type of data you are masking in order to ensure that the resulting data is realistic and useful for testing or demonstration purposes.
Viewing Masked Data
In order to view masked data in SQL Server, you need to have the necessary permissions. If you are using the MASKED WITH statement to mask a column, you need to have SELECT permissions on the table. If you are using a data masking function in a SELECT, INSERT, or UPDATE statement, you need to have the appropriate permissions for those statements.
Additionally, you need to have the UNMASK database permission set in order to view masked data. This permission allows you to see the actual, unmasked data in the database, regardless of any data masking that has been applied.
You can grant the UNMASK database permission to a user using the following statement:
|
1 |
GRANT UNMASK TO [user_name]; |
You can revoke the UNMASK database permission from a user using the following statement:
|
1 |
REVOKE UNMASK FROM [user_name]; |
It’s important to carefully manage the UNMASK database permission, as it allows users to bypass data masking and access the actual, sensitive data in the database. It’s generally recommended to only grant this permission to users who have a legitimate need to access the unmasked data and who have the necessary permissions to view the data directly.
Some Examples
|
1 2 3 4 5 6 |
CREATE LOGIN MaskedView2 WITH PASSWORD = 'Password1'; CREATE USER MaskedView2 FOR LOGIN MaskedView2; GRANT SELECT ON Examples.DataMasking TO MaskedView2; |
When creating a table it’s worth noting that the Data Masking statement goes between the data type and the nullable statement. As the simple example shows.
|
1 2 3 4 5 6 7 |
CREATE TABLE Examples.DataMasking2 ( email nvarchar(60) MASKED WITH (FUNCTION = 'email()') NOT NULL ); |
If you wish to test this on some of your own tables you can execute a select statement for a user who does not have the UNMASK permission set but has SELECT permissions. The below example shows this.
|
1 2 3 4 5 |
EXECUTE AS USER = 'MaskedView2'; SELECT * FROM Examples.DataMasking REVERT; SELECT USER_NAME(); |
Further Information
- Data Masking in SQL Server Microsoft Docs – This page from the Microsoft SQL Server documentation provides an overview of data masking in SQL Server and explains how to use the
MASKED WITHstatement and data masking functions. - Data Masking Best Practices – This article from Redgate provides best practices for data masking in SQL Server, including tips on how to choose the right data masking technique for your needs and how to manage permissions for accessing masked data.
- Data Masking in SQL Server Using Dynamic Data Masking – This tutorial from C# Corner explains how to use dynamic data masking in SQL Server to mask sensitive data in real-time.
