How to use Dynamic Data Masking of SQL Server 2016

Introduction

SQL Server 2016 was released on June 1st, 2016. We can download SQL SERVER 2016 Developer Edition for FREE from Visual Studio Dev Essentials.As like the other versions of it's clan, this time also SQL Server has come up with lot's of new features. In this article we will explore the Dynamic Data Masking feature.

What is the Dynamic Data Masking feature ?

Let's say, we have a database that contains sensitive data and we want to protect those data for security reasons. For this purpose, we can use dynamic data masking. It helps to obfuscate a portion of the data.The masked data can be unmasked for a user for viewing if and only if we grant permission/authorization for that particular user.

Types of Masks

To mask data, we can use one of the following four masking functions:

1. Default:This function is use for fully masking values by
   

   a) Returning a value of XXXX for string data types

   b) 0 for numeric and binary data types

   c) 01.01.2000 00:00:00.0000000 for date and time data types

2. Email:This function is use for partially masking email addresses e.g.: mXXX@XXXX.com. It also masks the length of the email address.


3. Partial:This function is use for partially masking values by using a custom definition. It's syntax is
   

   		partial(Prefix,Padding,Suffix)
   	

   where,

   Prefix - Number of starting characters to display from the first character in the value starting from the left hand side.

   Padding - Value to be displayed between the prefix and suffix characters.

   Suffix - Number of ending characters to display from the last character in the value starting from the right hand side.



4. Random: This function is use for fully masking numeric values by using a random value between a lower and upper limit.

Using the code

Let us now apply the concepts with the below simple experiment. We will first create a table with various fields and insert a record

-- Create table
CREATE TABLE TestMaskingDemotbl (

	EmployeeID INT IDENTITY(1, 1) PRIMARY KEY
	,Name NVARCHAR(50) NOT NULL
	,Age INT
	,Email NVARCHAR(100) NULL
	,DOB DATETIME
	,CreditCard VARCHAR(50) NULL
	,MobileNumber VARCHAR(10) NULL
)
GO
-- insert a row
INSERT INTO TestMaskingDemotbl
VALUES('RNA Team',20,'rnateam@RNA.com','31-Jan-2016','1234-5678-9101-1214','9112113402')
GO
--Project the records without masking
SELECT * FROM TestMaskingDemotbl

The output is as expected

Now we will apply dynamic data masking by using the masking functions as under

--Masking using Random Function
ALTER TABLE TestMaskingDemotbl
ALTER COLUMN EmployeeID ADD MASKED WITH (FUNCTION='random(10000,99999)')

-- For a NVARCHAR datatype
ALTER TABLE TestMaskingDemotbl
ALTER COLUMN Name ADD MASKED WITH (FUNCTION = 'default()') -- default on Name field (It is NVARCHAR)

-- For an INT datatype
ALTER TABLE TestMaskingDemotbl
ALTER COLUMN Age ADD MASKED WITH (FUNCTION = 'default()')-- default on Age field (It is INT)

-- For an DATETIME datatype
ALTER TABLE TestMaskingDemotbl
ALTER COLUMN DOB ADD MASKED WITH (FUNCTION = 'default()')-- default on DOB field (It is DATETIME)

-- Masking for an Email field
ALTER TABLE TestMaskingDemotbl
ALTER COLUMN Email ADD MASKED WITH (FUNCTION = 'email()') -- mask email addresses

--Masking using Partial Function
ALTER TABLE TestMaskingDemotbl
ALTER COLUMN CreditCard ADD MASKED WITH (FUNCTION = 'partial(2,"XX-XXXX-XXXX-X",3)') -- masking with partial

--Masking using Partial Function
ALTER TABLE TestMaskingDemotbl
ALTER COLUMN MobileNumber ADD MASKED WITH (FUNCTION = 'partial(2,"X",1)') -- masking with partial

After execution, the result will be

How to remove masking from a masked field ?

For this we need to use DROP MASKED command as shown under

ALTER TABLE TestMaskingDemotbl
ALTER COLUMN EmployeeID DROP MASKED

After execution, the result will be

References

Conclusion

Hope everyone enjoyed the journey with Dynamic Data Masking of SQL Server 2016 and understood how it works. Thanks for reading.