SQL Server 2016 introduced a powerful feature called Dynamic Data Masking (DDM) that allows developers and administrators to control the visibility of sensitive data with minimal impact on the application layer. This feature simplifies the design and coding of security in your application by making the data masking process a part of the database itself.
Dynamic Data Masking does not modify or change the actual data stored in a table. Instead, it applies masking functions on specific columns at the time of returning query results. There are four data masking functions supported by Dynamic Data Masking:
- Default
- Random
- Custom String
The Default function masks data based on the column’s data type. For example, if the data type is a date and time, it will display the data in the format ‘1900-01-01 00:00:00.000’. If the data type is numeric, it will show a ‘0’. If the data type is a string, it will display the data by adding ‘X’s to the string. The Default function can add a maximum of 4 ‘X’s to a string, and if the string contains fewer than 4 characters, it will show ‘X’ for each character.
Let’s take a look at an example to understand how Dynamic Data Masking works. We will create a table called DDM_Student_Sample and apply the Default DDM function on the Student_DOB column:
Create table DDM_Student_Sample
(
Student_ID int,
Student_DOB datetime masked with (function = 'default()'),
Student_Name varchar(100),
Student_Email_Id nvarchar(100)
)After creating the table, we can insert some data to see how the Default DDM function works:
insert into DDM_Student_Sample values (1234,'05/17/1989','Stuart Little Joe','StuartJoe@DDM.com')
insert into DDM_Student_Sample values (2134,'03/01/1990','Alexa sentmov','AlexaSt@DDM.com')
insert into DDM_Student_Sample values (1324,'06/21/1992','SentLaw Rents','SentLawR@DDM.com')
insert into DDM_Student_Sample values (1254,'10/29/1987','pitterpie Laafte','PetterpieL@DDM.com')Now, if we query the table, we will see that the actual data in the Student_DOB column is masked and displayed as ‘1900-01-01 00.00.00.000’.
To control the visibility of the masked data, we can create a user and grant read permission on the DDM_Student_Sample table. By default, the user will not be able to see the actual data in the masked columns. However, if we grant the UNMASK permission to the user, they will be able to see the actual data.
Dynamic Data Masking also supports other masking functions like Random, Custom String, and Email. The Random function masks numeric data types by displaying random values within a specified range. The Custom String function allows you to define a custom masking pattern by specifying the prefix, suffix, and padding. The Email function masks email addresses by displaying only the first character, followed by ‘XXX@XXXX’ until the suffix ‘.com’.
By applying these masking functions, you can control the visibility of sensitive data for users with different levels of privileges. This feature adds an advantage for database administrators, allowing them to hide sensitive data from users with limited access.
In conclusion, Dynamic Data Masking in SQL Server 2016 is a powerful feature that simplifies the process of masking sensitive data at the database level. It provides control over the visibility of data without altering the actual stored data in a table. This feature saves time and effort by eliminating the need to obfuscate or mask data when dealing with vendors or users with limited privileges.