Dynamic Data Masking in SQL Server

Dynamic data masking also known as DDM is a security feature in SQL Server which helps you to prevent the access of unauthorize users to sensitive data in the database. It was introduced in SQL Server 2016 version.

Basically, you can use DDM to preventing users who should not have access to the data from viewing it also can be useful to prevent accidental exposure of sensitive data when accessing a production database directly.

• You can define a masking rules on table column that contains a sensitive data, so that the unauthorized user can not see that data on column.
• Enabling Dynamic Data Masking does not change the data in the database. Only the data is masked in the result set for unauthorized users.
• It is very easy to use with an existing applications as it does not require any coding and masking rules are applied in the query results only.

There are four types of data masking functions available as followings:

Default function : It masks the data according to data type of fields.

1. If the data type of masked field is numeric ( smallint, tinyint, int, bigint, numeric, decimal, float, real, money, bit,..) then the 0 value is used to mask that field.
2. If the data type of the masked field is binary ( binary, varbinary, image) then a single byte of binary value 0  is used to mask that field.
3. If the data type of the masked field is a string( char, varchar, text, nchar, nvarchar, ntext) then the XXXX value is used to mask that field, in case if field length is less than 4 characters, then less number of Xs is used to mask that value.
4. If the data type of the masked field is a date and time ( date, time, datetime, datetime2, smalldatetime,..) then the 01.01.1900 00:00:00.0000000 value is used to mask that date field.

Email Function :  It is used to mask the field which contains the email address, It exposes only the first character of an email address and the constant suffix ‘.com’. in the form of an email address.

Random Function : It is used to mask the numeric data type by replacing the original value with random value within a specified range.

Custom function : Custom masking allows you to create a masking which exposes the first and last letters and adds a padding that will be shown in the middle in the form of prefix, [padding value], suffix.

If the original value is too short to complete the entire mask then the part of the prefix or suffix will not be exposed.

Lets implement the dynamic data masking in SQL Server.




First, we will create a sample table named BankCustomers which contains the sensitive data of bank’s customers.

CREATE TABLE dbo.BankCustomers
(CustomerId INT Identity(1,1),
CustomerName Varchar(150),
PhoneNumber Varchar(10),
AccountNumber BigInt,
EmailId Varchar(100)
)

INSERT INTO dbo.BankCustomers
(CustomerName,PhoneNumber,AccountNumber,EmailId)
VALUES
('Rohit Kumar', '7869595959',463636252434, 'rohit4K@yltd.com'),
('Pradeep Raturi', '9686868686', 343424343453, 'praturi000@sqlskull.com'),
('Sumit Viyas', '6758585858', 45875645324, 'sumit_v@gmail.com'),
('Suraj Negi', '555585678', 243567098768, 'surajnegi8886@gmail.com'),
('Manoj Kumar', '4765898765', 8684635336, 'mjk89@yhaoo.com'),
('S ML Kumar', '2547658987', 8984635389, 'smlkumar@outlook.com')

Now we have a table as shown below.

[Also Read: Implementing a ROW_LEVEL security in SQL Server]

You can also see, this table contains some sensitive data on columns PhoneNumber, AccountNumber, and EmailId.

Implementing Dynamic Data Masking

To demonstrate the DDM feature, Lets create a new user login in database first. Then we will see how the dynamic data masking limits the access of unauthorized users to viewing the sensitive data in the database.

Lets create a new user named User1, and grant a select permission on table BankCustomers to this user using below T-SQL.

CREATE USER User1 WITHOUT LOGIN 
GRANT SELECT ON dbo.BankCustomers TO User1

Now, when user User1 tries to fetch the data from table BankCustomers.

You can see User1 is able to see all the sensitive data of customer’s.

EXECUTE AS USER = 'User1' 
SELECT * FROM dbo.BankCustomers 
REVERT

Lets say, you do not want user User1 to see the sensitive data such as AccountNumber, PhoneNumer, and EmailId then you can mask the column data for this user.

Lets mask the data of columns PhoneNumber, AccountNumber, and EmailId using all types of masking function.

Mask a PhoneNumber using the Default function, for this will use alter table statement to alter the column as shown below.

ALTER TABLE dbo.BankCustomers   

ALTER COLUMN PhoneNumber Varchar(10) MASKED WITH (FUNCTION = 'default()')  

You can quickly check whether the masking function is applied on columns or not using below T-SQL.

SELECT tbl.name as TableName, col.name AS ColumnName, col.Masking_function 
  FROM sys.masked_columns AS col
  JOIN sys.tables AS tbl
  ON col.object_id = tbl.object_id
WHERE is_masked = 1

You can see, a default masking function is applied on column PhoneNumber.

Lets see, what happens this time when user User1 tries to fetch the data from BankCustomer table.

EXECUTE AS USER = 'User1'
SELECT * FROM dbo.BankCustomers 
REVERT

You can see, the data in PhoneNumber column is masked for user User1, now user will see XXXX rather the original PhoneNumber data.

As you know DDM does not change the data in database, only the data is changed in result query for unauthorized users that is User1.

If you as an admin try to fetch the data, you will be able to see the data as shown below.

SELECT * FROM dbo.BankCustomers

Lets mask the data in AccountNumber column using Random function.

We will again use alter table statement to alter the column and apply Random function to mask an account number, so value for account number will be masked between any random values between 50 to 100.

ALTER TABLE dbo.BankCustomers   
ALTER COLUMN AccountNumber BigInt MASKED WITH (FUNCTION='random(50,100)')

Lets see, when user User1 tries to fetch the data from table, you will see value in AccountNumber column are shown with random values between 50 to 100.

Lets mask the data in EmailId column using a Email function.

Again, we will alter the table column using alter table statement and apply Email function to mask the data in  EmailId column as shown below.

ALTER TABLE dbo.BankCustomers   
ALTER COLUMN EmailId Varchar(100) MASKED WITH (FUNCTION='Email()')

Lets see, when User1 tries to fetch the data from table.

EXECUTE AS USER = 'User1' 
SELECT * FROM dbo.BankCustomers 
REVERT

You can see, the data in EmailId column are masked for User1.

Now, Lets mask the data in CustomerName column using Custom function.

You can also mask the data using custom masking functions, which exposes the first and last letters of data and adds a padding that will be shown in the middle in the form of prefix, [padding value], suffix.

Lets mask the CustomerName using Custom function, which displays the first two letters of CustomerName and mask the following 4 letters with XXXX value then displays the last four letters.

In case, If the original value is too short to complete the entire mask then the part of the prefix or suffix will not be exposed.

ALTER TABLE dbo.BankCustomers  
ALTER COLUMN CustomerName Varchar(150) MASKED WITH(FUNCTION= 'partial(2,"XXXX",4)');

Lets see, when User1 tries to fetch the data from table, and you can see data in CustomerName column are masked.

EXECUTE AS USER = 'User1' 
SELECT * FROM dbo.BankCustomers 
REVERT

Granting permission to user to view UNMASKED data

User1 can be able to see the unmasked data, if you grant a UNMASKED permission to User1 as shown below.

GRANT UNMASK TO User1
EXECUTE AS USER = 'User1' 
SELECT * FROM dbo.BankCustomers 
REVERT

[Also Read: Column Level Encryption/Decryption Using SYMMETRIC Keys]

You can see User1 is able to see the unmasked data.

UMASKED permission can also be revoked easily as shown below.

REVOKE UNMASK TO User1
EXECUTE AS USER = 'User1' 
SELECT * FROM dbo.BankCustomers 
REVERT

Dropping a Dynamic Data Masking

You can also Drop a masking from masked column using a Alter table statement as shown below.

ALTER TABLE dbo.BankCustomers 
ALTER COLUMN CustomerName DROP MASKED

You can quickly check whether the masking is removed from column CustomerName or not using below T-SQL.

SELECT tbl.name as TableName, col.name AS ColumnName, col.Masking_function 
  FROM sys.masked_columns AS col
  JOIN sys.tables AS tbl
  ON col.object_id = tbl.object_id
WHERE is_masked = 1

You can see the result of above T-SQL, it returns all the columns that have masking function applied to them, and there is no CustomerName column in result set that means masking is removed from CustomerName column.

Lets see, if User1 fetch the data from table, and you can see now User1 can see the customer name.

Creating a new table with Dynamic Data Masks

Lets create a new table named dbo.BankCustomers_New with dynamic data masks.

CREATE TABLE dbo.BankCustomers_New
(CustomerId INT Identity(1,1), 
CustomerName Varchar(150) MASKED WITH(FUNCTION= 'partial(2,"XXXX",4)'), 
PhoneNumber Varchar MASKED WITH (FUNCTION = 'default()'), 
AccountNumber BigInt MASKED WITH (FUNCTION='random(50,100)'), 
EmailId Varchar(100) MASKED WITH (FUNCTION='Email()')
)

Unauthorized user can easily guess the masked data

The idea is to demonstrate that how unauthorized user can guess the masked data with the help of applying some where conditions in select query.

As you know, you have created a new user User1 who does not have unmask permission.

When User1 tries to fetch data data from table BankCustomers the data on columns PhoneNumber, AccountNumber, and EmailId are masked in result set.

Lets say, if user1 tries to fetch the data from table with Where condition , and keeps trying with some wild guess as shown below.




Following T-SQL, returns one record from table for condition where EmailId includes a string ‘Rohit’.

As you can see, Although In the result set EmailId data are masked but this is enough for User1 to make sure on that there is only one record in this table that having an EmailId which contains string ‘Rohit’.

Following same techniques, user can apply multiple where conditions to guess the masked data.

Also Read..

Data discovery and classification

Implementing a ROW_LEVEL security in SQL Server

Column Level Encryption/Decryption Using SYMMETRIC Keys

SQL Vulnerability Assessment

SQL Basics Tutorial	SQL Advance Tutorial	SSRS	Interview Q & A
SQL Create table	SQL Server Stored Procedure	Create a New SSRS Project	List Of SQL Server basics to Advance Level Interview Q & A
SQL ALTER TABLE	SQL Server Merge	Create a Shared Data Source in SSRS	SQL Server Question & Answer Quiz
SQL Drop	SQL Server Pivot	Create a SSRS Tabular Report / Detail Report
..... More	.... More	....More
Power BI Tutorial	Azure Tutorial	Python Tutorial	SQL Server Tips & Tricks
Download and Install Power BI Desktop	Create an Azure storage account	Learn Python & ML Step by step	Enable Dark theme in SQL Server Management studio
Connect Power BI to SQL Server	Upload files to Azure storage container		SQL Server Template Explorer
Create Report ToolTip Pages in Power BI	Create Azure SQL Database Server		Displaying line numbers in Query Editor Window
....More	....More		....More