Mask Privacy Data Columns in MSSQL

By Feng Yi.

In our everyday life, we've all probably been bothered by spam phone calls and text messages and emails a number of times. In the worst case scenario, these annoying spam calls and emails lead to your private information being leaked or stolen; whether it is your bank card number and ID number, the results can be downright painful. Given these threats, one may wonder what sorts of technical techniques can be used, or what precautions can be made, to protect our private data as much as possible. Well, in this blog, we'll look at one way to do just that.

Microsoft's SQL Server 2016 introduced dynamic data masking to mask or hide column-level privacy data for the first time. Let's see how to mask privacy data such as phone numbers, ID numbers, and driving license numbers.

Background Principle

Data column masking is actually not new. It is a method to hide privacy data and only allow users with high-level permissions to view complete privacy data. Masking itself does not involve data encryption and decryption of any kind. Rather, strictly speaking, data masking is not a complete data security solution. However, it is nonetheless an important part of the data security strategy to effectively avoid the leakage of privacy columns. Now let's see how dynamic data masking is implemented in SQL Server 2016.

Procedure

1. Create a Test Database

To make the demo test simple, create a test database TestDb first.

2. Create a Test Table

First, create a normal table called CustomerInfo to store customer information, where the CustomerPhone column is a privacy data column that stores customers' phone numbers.

3. Create a Test User

To make it easy to observe and check the test result, we create a test user called DemoUser.

Under normal conditions, this DemoUser can see all the customer information, including critical privacy data like customers' phone numbers. If this DemoUser wants to act maliciously, the customer information can be easily exported and disclosed. In this situation, the data security risk is very high.

4. Mask Customers' Phone Numbers

If we mask customers' privacy data such as phone numbers, ID numbers, and bank card numbers, then the DemoUser cannot see the complete customer data and information. The masking method is as follows:

Because phone numbers in the CustomerPhone column are 11-digit numbers, we can use partial masking to hide the four middle digits of each customer's phone number with asterisks and keep the first three digits and the last four digits.

5. Query the Masked Column

After the column is masked, let's try to view the masked column and the masking function:

The CustomerPhone field in the TestDb.dbo.CustomerInfo table has been masked with the masking function partial(3, "**", 4):

6. View the Data Using the Test User' Account

After masking, use the DemoUser test account to view the masked data again:

The four middle digits of customers' phone numbers have been successfully masked and the DemoUser can no longer obtain the complete phone numbers.

7. Modify the Masking Character

If you do not like asterisks, you can use another character, for example, the letter x. You can use any character to mask the data:

Now, as shown in the following figure, x is used to mask the phone numbers:

8. Add a New Masked Column

Assume that we need to add a new column to store customers' email addresses, which also need to be masked. This can be easily done by using the email masking function when the email column is added:

9. Query a Specific Value in the Masked Column

Some of you may ask if the masked CustomerPhone column will affect your WHERE query statement. The answer is no. This is because data masking itself does not make any modifications to the original data. It only makes partial information masked or hidden when the data is presented.

According to the query results, phone numbers and email addresses are always masked.

10. Copy a Table Containing Masked Columns

As mentioned before, data masking does not encrypt or modify data. So far, the DemoUser cannot obtain the customers' privacy data directly from the original table. However, can the DemoUser indirectly obtain the privacy data by copying or exporting the customer data to a new table? Well, let's do a simple test: Use the DemoUser account to copy the table CustomerInfo to a new table called CustomerInfo_copied.

After the DemoUser copies the customer data to the new table, the data is still masked. The DemoUser cannot export or copy the customers' privacy data. This meets the goal of the security policy to protect customers' privacy data:

11. Query Unmask Data

If the DemoUser owns the highest permission one day and needs to see the customers' privacy data, we can grant the DemoUser the unmask permission so that the DemoUser can see the complete customer data. The method is shown as follows:

In this case, the DemoUser can query the complete customer data.

12. Drop the Mask

Drop the mask and allow all users to see data without being masked

Summary

In this tutorial, you have learned how to use the dynamic data masking feature in SQL Server 2016 to mask customer data, prevent non-privileged users from viewing and exporting critical customer privacy data and ensure the maximal customer data security.

Community

Mask Privacy Data Columns in MSSQL

Background Principle

Procedure

1. Create a Test Database

2. Create a Test Table

3. Create a Test User

4. Mask Customers' Phone Numbers

5. Query the Masked Column

6. View the Data Using the Test User' Account

7. Modify the Masking Character

8. Add a New Masked Column

9. Query a Specific Value in the Masked Column

10. Copy a Table Containing Masked Columns

11. Query Unmask Data

12. Drop the Mask

Summary

Read previous post:

Read next post:

Cherish Wang

You may also like

Comments

Cherish Wang

Related Products

Database Overview

Database Security Solutions

ApsaraDB RDS for SQL Server

Data Security on the Cloud Solution