Dynamic Data Masking is a new feature of SQL Server 2016 that obfuscates data values for certain users by replacing characters with a series of Xs or other default numeric values.
- [Instructor] Dynamic data masking is another new feature of SQL server 2016 that helps protect the security of your data. When enabled, it allows users to query information out of the data tables, but it will apply various obfuscation techniques to make the data less readable. This is different from a full encryption in that the values are masked on the SQL server side before being sent to the user. You've probably seen this technique applied when you type your password into a web form where the individual characters that you type get replaced by an asterisk or a bullet character on the screen. Let's take a look at this in action by starting in the wide world importers database.
Now dynamic data masking is applied on specific columns in a data table. I want to start by copying some values from the wide world importers applications dot people table into a new table called new people. We'll select the columns personID, FullName, PhoneNumber, EmailAddress, and IsEmployee into that new table. I'm doing this for a couple of reasons. First, it'll limit the number of columns that we're looking at in this example, but second, it will actually get us around a couple of limitations that dynamic data masking has and we'll come back to look at those in a few minutes.
Now if I select everything out of the NewPeople table, we'll see the five columns of data we can work with. We've had the person ID, a full name, the phone number, an email address, and a numeric indication on whether they're an employee or not. For this example, we also need a new user to work with so I'll create somebody called the sales manager without a log in and we'll go ahead and grant them select permission on that new table that we just created called new people. Okay, now that everything is set up, let's go ahead and turn our attention to dynamic data masking.
I'm going to scroll down a little bit on the script. To apply dynamic data masking we simply run an alter table statement against the table we want to effect and we're also going to alter the specific column. I'll start with the full name column from the new people table. We'll add this line that says add masked with and then a function. Now there's several different functions to choose from. For the full name we're going with the partial function. Partial will allow you to build a custom string and you can specify how many characters at the beginning or end to reveal. Here, I'm specifying that I want to see the first character then three asterisks and then the last three characters of the full name values.
The next column that we're going to mask is the phone number column and we're going to use the default function. Default will replace character strings with X's and numerical values will get replaced with zero. The next column is the email address and we'll use the email function. Email is a special mask and it will obfuscate email addresses so you can see the first character then a bunch of X's and then finally the type of domain that they use, so either .com, .edu, .net, or whatever. And then finally we'll do the is employee value which is a numerical field and we'll again apply the default function there.
Let's go ahead and run all these lines to alter each of those columns in the data table. Next, I'm going to select everything from the new people table. Now I'm running this as the DOB account so I don't expect to see any changes here and indeed I can still see the data values there. Now let's change our security context to the sales manager. We'll execute as user sales manager and we'll just go ahead and select the user name to verify our context, then we'll select everything from the new people table. Down below in the results we can see what happens when the dynamic data masking is applied.
The full name gets just the first letter then three asterisks and then the last three letters. The phone number gets replaced with all X's. The email address we can see the first character and then a bunch of X's and then the domain at the end and is employee that numerical field got replaced with all zeros all the way down. Let's go ahead and revert back into the DBO account and we can grant the unmask permission to specific users so if I grant unmask to sales manager and then try that again, we'll execute as user sales manager. We'll select the user name and then select everything from the new people table, we'll see that that person can now see the unmasked values.
Let's go back into DBO and if we can grant on mask then it also makes sense that we can deny on mask to revert that and so that the user sales manager can no longer see the unmasked values. So this actually exposes the first limitation of dynamic data masking. That it's either enabled or disabled per user for the entire database. Notice in the grant on mask and deny on mask statements that we're not specifying any specific tables. There is no option currently for selecting individual tables to keep masked while revealing others.
It's an all or nothing deal. The second limitation has to do with the structure of the columns themselves. Let's go ahead and revert back to DBO and we'll scroll down a little bit and we'll take a look at the original Application.People table from the wide world importers database. Here we see the original data columns as they appear in that data table. Now if I want to alter the table Application.People and add masking to the full name column that comes out of the original data table here. Let's go ahead and try to run line 56 and 57.
We'll see we get this error message saying that we can't apply the masking because one or more objects access this column. Let's try and apply it to the search name field. When I execute this line we get the message that it can't alter the column search name because it's computed. The search name field actually uses the full name field to calculate it's result. You also can't apply masking to columns that you use always encrypted, are a part of a file stream set, or are columns used in a full text index. Finally, masked values can sometimes be figured out by the application of a creative query or a simple brut force.
So it shouldn't be considered as an alternative for a full encryption strategy. If you want to remove dynamic data masking from individual table, we'll simply alter the table again and this time we'll drop the mask value from the full name column. Since we're done with this example though, instead of going through each individual column that we masked let's just go ahead and drop the new people table and then we'll also drop the sales manager user. So with those limitations in mind, dynamic data masking is an interesting new feature that is very easy to implement. Under the right circumstances, DDM provides something that you can never have too many of, another option in your security tool box.
Adam Wilbert covers four main skill areas required of SQL Server 2016 administrators: configuring access to data, managing backups, monitoring performance, and implementing the high-availability features of the product. Learn about data encryption, access control, role-level security, and dynamic data masking. Use SQL Server auditing to gain insights into the health and performance of your system, and determine upgrade paths. Discover how to back up SQL Server and perform full or partial restores, and monitor activity. Explore indexing and query execution plan management. Plus, learn to configure availability groups to mitigate hardware failure.
- Implementing encryption
- Configuring backup and connection encryption
- Creating and maintaining users
- Managing permissions and roles
- Configuring SQL Server auditing
- Developing a backup strategy
- Restoring databases
- Identifying and recovering from corruption
- Monitoring database activity and queries
- Managing indexes
- Managing statistics
- Monitoring instances
- Implementing log shipping
- Implementing always on availability groups