What is Dynamic Data Masking?
Dynamic Data Masking is a feature announced in SQL Server 2016. With Dynamic Data Masking, you can restrict unauthorized access to your data.
Microsoft introduced serious innovations about the data security with SQL Server 2016. Two of these features are Always Encrypted and Row-Level Security. You can find detailed information in the following articles.
“What is Always Encrypted in SQL Server“,
“Row Level Security in SQL Server”
What is the difference between masking and encryption?
With DDM, we do not encrypt the data in the database. We ensure that critical columns are not visible to people even if they have select permission on the table. For users who need to read critical columns, extra privileges are required except select permission. I will share the scripts that grant extra permission to read critical columns with DDM.
Before this feature was announced, we were able to do column level encryption. You can find the details in the article “Column Level Encryption On SQL Server“. You can understand the differences by reading both articles.
Let’s continue by making an example. Suppose we have a customer table. In this table there is critical information such as customer’s identitiy number, email address, mobile phone and birthdate. You may not want the customer representative to see this information. In such a case, it is useful to use DDM.
Dynamic Data Masking Functions
With DDM, we can encrypt data with 4 different function.
1) Default
This masking method allows masking according to the data type of the column to be masked.
If column is a data type such as binary, varbinary, image, masking is done with a single byte value of 0.
For date type columns, the value 01.01.1900 00: 00: 00.0000000 is used.
For string type columns, the value XXXX is used.
For Numeric type columns, an empty value is used.
2) Random
Replaces data of the Numeric type with a random number value within the range specified by the random function.
3) Email
It takes only the first letter of the email and changes the rest to “XXX@XXXX.com“.
4) Custom
It allows us to encrypt data with our own encryption function.
SQL Server Data Masking Example
Create a table with the following script and insert sample records into the table. phone numbers was inserted randomly. You should not call the phone numbers. 🙂
|
1 2 3 4 5 6 7 8 9 |
CREATE TABLE [dbo].[Customer]( [IdentityNo] [char](11) NULL, [Name] [varchar](200) NULL, [Email] [varchar](200) NULL, [GSM] [char](11) NULL, [ProductInfo] [varchar](500) NULL, [BirthDate] [datetime] NULL, [NumberofChildren] [smallint] NULL ) ON [PRIMARY] |
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 |
INSERT INTO [dbo].[Customer]([IdentityNo],[Name],[Email],[GSM],[ProductInfo],[BirthDate],[NumberofChildren]) VALUES (16985463254, 'Nurullah ÇAKIR', 'dbtut.com@gmail.com', '09991111111', 'Coat', '1980-01-01', 2), ( 16985463255, 'Hakan GÜRBAŞLAR', 'hakangurbaslar@gmail.com', '09992222222', 'Trousers', '1980-01-01', 1), ( 16985463256, 'Faruk ERDEM', 'farukerdem@gmail.com', '09993333333', 'Shirt', '1990-01-01', 0) |
When we select from our table, the result will be as follows.
Masking by using Default Function
Mask the identity number column with the default method using the following script.
|
1 2 |
ALTER TABLE Customer ALTER COLUMN IdentityNo char(11) MASKED WITH (FUNCTION = 'default()'); |
When we select our table again, we will see all the data unencrypted. Because, this operation is invalid for sysadmins and db_owners.
Create a login and grant db_datareader permission. Then, try to select from this table with this login. You may want to read the article “How To Create a Login On SQL Server(Manage Logins)“.
Instead of granting db_datereader permission, you can also GRANT Select permission as follows.
|
1 |
GRANT SELECT ON dbo.Customer TO DDMLogin |
As you can see below, the login we grant Select permission can read the data as masked.
Masking by using Email Function
Mask it as follows with the email method and select again from the table with DDMUser.
|
1 2 |
ALTER TABLE Customer ALTER COLUMN Email varchar(200) MASKED WITH (FUNCTION = 'Email()'); |
As you can see, it took only the first letter of the Email column and masked the rest as “XXX@XXXX.com”.
Masking by using Random Function
Mask the NumberofChildren column with the random method using the following script. It replaces the specified column values with a random value between 10 and 30.
|
1 2 |
ALTER TABLE Customer ALTER COLUMN NumberofChildren smallint MASKED WITH (FUNCTION = 'random(10,30)'); |
As you can see below, although Faruk is not married, Faruk seems to have 29 children. 🙂
Masking by using Custom Function
Mask the Name column with the Custom method using the following script. In this method, it first lists 2 letters of the relevant column, then XXXX, and finally the last 2 letters.
|
1 2 |
ALTER TABLE Customer ALTER COLUMN Name varchar(200) MASKED WITH (FUNCTION = 'partial(2,"XXXX",2)'); |
Grant Unmask Permission To See Unmasked Data
We can Grant Unmask Permission to logins to see the original version of the masked data with the following script.(This script can be executed only sysadmins or db_owners)
|
1 |
GRANT UNMASK TO DDMLogin |
Revoke Unmask Permission
|
1 |
REVOKE UNMASK TO DDMLogin |
Dropping Masking
We can drop the dynamic data masking on the table with the following script.
|
1 2 |
ALTER TABLE Customer ALTER COLUMN NumberofChildren DROP MASKED; |
We need ALTER ANY MASK permission to perform the masking operations. Right-click the database, click properties, you can then grant permissions for specific users from the permission tab.
SQL Server Data Masking Limitations
You can not mask;
Encrypted columns,
Filestream columns,
Column_set or a sparse column if its a part of a column_set,
Computed Columns(You can mask columns those creates computed columns. Thus computed column can be masked)
You can not specify masked columns as a key for FULLTEXT index.
