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.
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.
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
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:
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
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
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
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.