Introduction To Dynamic Data Masking In SQL Server 2016

In release of SQL Server 2016 CTP 2.0 another new feature that is released is Dynamic Data Masking.  Using Dynamic data masking we can provide the security to our restrictive data by masking the data from viewing to non-authorized users.

For example, we have credit card information in our table but we don’t want non-privileged users to see the information as a security concerns. In this case we can mask the data of credit card so that we can prevent non-privileged users from viewing the data which may access the data for troubleshooting purpose.

To enable the data masking we need to enable trace flags 209 and 219. Execute the following syntax to enable the data masking:

DBCC TRACEON(209,219,-1)

There are 3 types of masking:

1. Default

• Use zero for numeric data types.
• Use xxxx for string data types.
• Use 01.01.2000 for date and time data types.

Example: Age INT MASKED WITH (FUNCTION = ‘default()’)

2. Custom

Uses the first and last letter and added a custom padded string in the middle.

Example: PhoneNumber varchar(10) MASKED WITH (FUNCTION = partial(5,”xxxx”,0)

3. Email

Example: Email varchar(100) MASKED WITH (FUNCTION = ‘email()’)

Let’s take an example to understand this better. First I will create a table and insert some data into it, later on which I will applied the masking.

Create table CardInfo

(

       CardInfoId INT IDENTITY(1,1) PRIMARY KEY,

       FirstName varchar(100),

       LastName varchar(100),

       CardNumber varchar(100),

       PhoneNumber varchar(20),

       Email varchar(100)

)

GO

INSERT INTO CardInfo(FirstName, LastName, CardNumber, PhoneNumber, Email)

SELECT 'Kapil', 'Singh', '1234-5678-2345-6789','123456789','kapil@gmail.com'

UNION ALL

SELECT 'Aditya','Vyas','6789-1234-3456-5678','987654321','aditya@gmail.com'

We can apply the masking to the columns to the table definition at the time creation of table or we can apply using ALTER statement. Here we will apply the masking using ALTER statement like this:

ALTER TABLE CardInfo

ALTER COLUMN PhoneNumber ADD MASKED WITH (FUNCTION ='default()')

GO

ALTER TABLE CardInfo

ALTER COLUMN CardNumber ADD MASKED WITH (FUNCTION ='partial(2,"xxx",2)')

GO

ALTER TABLE CardInfo

ALTER COLUMN Email ADD MASKED WITH (FUNCTION ='email()')

GO

Here I have applied masking to the columns PhoneNumber, CardNumber and Email columns.  After this I will create a new user ‘TestUser’ which will have only SELECT permission.

CREATE USER  TestUser WITHOUT LOGIN;

GRANT SELECT ON CardInfo TO TestUser

Now I will the below script and see that masking is applied to the newly create user ‘TestUser’.

SELECT * FROM CardInfo

GO

EXECUTE AS USER = 'TestUser'

SELECT * FROM CardInfo

REVERT;

In output we can see that columns CardNumber, PhoneNumber and Email information is not visible to the user ‘TestUser’.

So datamasking is very helpful in scenarios related to security where we have some confidential information and that information should not be visible to un-authorized person.

We can also unmask the data which I will cover in my next blogs.

Have a happy learning!!

Regards,

Kapil Singh Kumawat

Like us on FaceBook | Join the fastest growing SQL Server group on FaceBook