There are many new features in SQL Server 2016, but the one we will focus on in this post is:
- Dynamic Data Masking
Have you ever been on a website, where your personal information, ie. Social Security number or Credit Card number shown in clear text, ready for everyone to have a look at. Would it not be cool if your information was somehow masked by default, and not needed to rely on the application to mask the data before displaying it on the screen?
Examples of Datamasking
| Type | Value | Masked |
| Social Security number | 320474-2345 | 320474-**** |
| Credit Card | 4566-6546-6546-7897 | 4566-****-****-**** |
| Telephone | +45 45454545 | +45 45****** |
| kenneth@mydomain.com | K*******@*********.com |
If you look at the above, then it is clear that it would be really nice to have your sensitive data somewhat masked when displayed on a monitor, that other people can look at the same time.
Now with the introduction of SQL Server 2016 we have the possibility to mask data in the database, and the data is masked when queried as well – this means that the application developers should not worry about this little security issue any more.
How to use it
While it is a feature in CTP 2.1 and enabled by default in this build, you should enable a few trace flags to use it, of you are using CTP 2.0 build. The trace flags is as follows.
|
1 2 3 4 5 |
-- To enable Dynamic Data Masking in CTP 2.0 DBCC TRACEON(209,219,-1) |
When this is done, you are ready to work with Dynamic Data Masking.
There are three types of masks available from SQL server as default.
| Default |
Can be used on Nchar and Nvarchar, remember that MAX size is not supported Use XXXX for char fields and a zero value for numeric datatypes Can also mask datetypes, date, datetime2, datetime, smalldatetime, time and datetimeoffset |
| When using email mask the first letter in the email will be shown, and the domain | |
| String | Allows you to mask strings with you own masking string in the middle |
Let’s create a table for data masking
The following table will contain information that we would like to mask later.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 |
-- Create a table that we need masking on CREATE TABLE [dbo].[Client] ( [ClientID] int identity(1,1) not null, [Firstname] nvarchar(50) null, [Lastname] nvarchar(50) null, [Birthdate] datetime null, [Email] nvarchar(128) null, [PhoneNumber] nvarchar(50) null, [Birthplace] nvarchar(50) null, [SocialSecurityNumber] nvarchar(12) null ) |
At the moment, the table is not enabled for masking, and every column in the table will be showing all the data it is storing. Let us also insert some data that we can work with.
|
1 2 3 4 5 6 7 8 9 10 |
-- Insert test data INSERT INTO [dbo].[Client] (Firstname, Lastname, Birthdate, Email, Phonenumber, SocialSecurityNumber, Birthplace) VALUES ('Kenneth', 'Nielsen', '1974-05-19', 'Kenneth@funkylab.com', '+45-12345678', 1905741234, 'Copenhagen') , ('Peter', 'Nielsen', '1953-05-19', 'Peter@funkylab.com', '+45-23456789', 1901531234, 'Stockholm') , ('Lotte', 'Nielsen', '1965-01-06', 'Lotte@funkylab.com', '+45-87654321', 01061234, 'Aalborg') |
Can I mask data already in a table?
Unlike the other security feature “Always Encrypted”, we have the possibility to mask data that is already in a table without the hassle of creating a new table and importing data into that one. We simply have to alter the table and columns that we need masked data in.
To do so, we will issue the following statement.
|
1 2 3 4 5 6 |
-- To alter a table already in the database ALTER TABLE <TableName> ALTER COLUMN <ColumnName> ADD MASKED WITH (FUNCTION = '<FunctionName>') |
The functions that can be used is the ones stated above, so if the column was holding ie. Email addresses, the alter function could be this. The following script will enable data masking on the data in the dbo.client table.
|
1 2 3 4 5 6 7 8 9 10 11 |
-- To alter a table already in the database ALTER TABLE [dbo].[Client] ALTER COLUMN [EMAIL] ADD MASKED WITH (FUNCTION = 'EMAIL()') ALTER TABLE [dbo].[Client] ALTER COLUMN [BIRTHDATE] ADD MASKED WITH (FUNCTION = 'DEFAULT()') ALTER TABLE [dbo].[Client] ALTER COLUMN [Firstname] ADD MASKED WITH (FUNCTION = 'PARTIAL(1, "XXXXXXXXXX", 0)') ALTER TABLE [dbo].[Client] ALTER COLUMN [Lastname] ADD MASKED WITH (FUNCTION = 'DEFAULT()') ALTER TABLE [dbo].[Client] ALTER COLUMN [PhoneNumber] ADD MASKED WITH (FUNCTION = 'PARTIAL(4, "XXXXXX", 2)') ALTER TABLE [dbo].[Client] ALTER COLUMN [SocialSecurityNumber] ADD MASKED WITH (FUNCTION = 'PARTIAL(6, "XXXX", 0)') ALTER TABLE [dbo].[Client] ALTER COLUMN [BirthPlace] ADD MASKED WITH (FUNCTION = 'PARTIAL(1, "XXXXXXXXXX", 0)') |
Now data should be masked when we query it, but if we just make a SELECT * FROM [dbo].[Client] we will still be able to see the data – this is because we are logged into the database as DBO, and therefore have all the rights needed to see and manipulate data.
To test if our datamask is working, we have to create a new login and user, that is only member of the Data_Reader role, this role have rad permissions, and data will be masked dynamically.
Create Logins
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 |
-- Create login and user with only Read Permissions USE master GO CREATE LOGIN [DynamicMaskReader] WITH PASSWORD = 'DynamicMask', DEFAULT_DATABASE = [master], CHECK_POLICY = OFF, CHECK_EXPIRATION = OFF GO USE DynamicDataMasking GO CREATE USER [DynamicMaskReader] FOR LOGIN [DynamicMaskReader] WITH DEFAULT_SCHEMA = dbo GO ALTER ROLE db_datareader ADD MEMBER [DynamicMaskReader] GO |
Connect as Reader
When this user is created, you have to open a new Query and connect to the database using the new user.
- CTRL+N (opens a new query window)
- Right click on the query window
Select Connection ➜ Disconnect
- Right click on the query window
Select Connection ➜ Connect
- Select SQL Server Authentication
- Type in the name “DynamicMaskReader” in login
Type in the password “DynamicMask” in password
- And click Connect
Now we are ready to query the data that we have put a dynamic datamask on.
Query the Data
In the query window, you can now issue statements where you select data from the table [dbo].[client] and you will see that the data is masked as by the function we defined earlier.
- Firstname is masked so that the first letter is visible and all remaining letters is replaced by X
- Lastname is masked by default, leaving all traces of a name masked and replaced by 4 X
- Birthdate is masked to be 2000-01-01 for all records
- Email is masked by function Email and therefore only showing first letter and top-level domain.
- Phonenumber is masked to only show country code and last number in this number format.
- Birthplace is masked to show only first letter and all remaining letters is replaces by X
- SocialSecurityNumber is masked to show only the first 6 digits and masking the last 4 digit
| ClientID | Firstname | Lastname | Birthdate | PhoneNumber | Birthplace |
SocialSecurity Number | |
| 1 | KXXXXXXXXXX | xxxx | 2000-01-01 00:00:00.000 | KXXX@XXXX.com | +45-XXXXXX78 | CXXXXXXXXXX | 190574XXXX |
| 2 | PXXXXXXXXXX | xxxx | 2000-01-01 00:00:00.000 | PXXX@XXXX.com | +45-XXXXXX89 | SXXXXXXXXXX | 190153XXXX |
| 3 | LXXXXXXXXXX | xxxx | 2000-01-01 00:00:00.000 | LXXX@XXXX.com | +45-XXXXXX21 | AXXXXXXXXXX | 106123XXXX |
Conclusion
This post should have given you a basic understanding of the concept of Dynamic Data Masking, and should enable you to use it on your own data/tables when you make the shift to SQL Server 2016.
I hope you have enjoyed reading and testing, and remember it is very easy to set up a test SQL Server 2016 in AZURE. Perhaps I should make a post on that ;o)
He really likes to advise the customers to take the right decisions, but also maintains a high technical knowledge, so he can act as both architect and developer.
Over the last years, he has become a highly-rated international speaker at various SQL events. Organizing the Danish SQLSaturday and member of the board in SQLSUG.dk
View all posts by Kenneth M. Nielsen
- Using SQL Server 2016 CTP3 in Azure - November 6, 2015
- New Features in SQL Server 2016 – Dynamic Data Masking - July 23, 2015
- New Features in SQL Server 2016 – Always encrypted - July 8, 2015