Data Masking in Sql Server 2016

New Feature of SQL Server 2016 is masking.

It is the  Security Feature introduced in Sql Server 2016. It provides us to  mask the data from non-privileged users. And the users with sufficient permission will have complete access to the actual or un-masked data.

Example if the SSN number is  like 123-321-4567, but the application will mask and display it to the user as XXX-XXX-4567.

 Four masking functions which can be defined on table column

1. Default
2. Email
3. Partial
4. Random

Let us create a sample database as shown below

CREATE TABLE dbo.Employee

(

 EmployeeId INT IDENTITY(1,1), EmployeeName NVARCHAR(100), DOJ DATETIME,

 EmailAddress NVARCHAR(100), Phone Varchar(15), Salary INT

)

GO

INSERT INTO dbo.Employee (EmployeeName, DOJ, EmailAddress,Phone, Salary)

Values

 ('Rajakonda Uday Kumar', '02/20/2001', 'rajakondauday@gmail.com',

   '998-9895-250',100000),

 ('Rajakonda Deevansh', '07/01/2018', 'rajakondadevaansh@gmail.com',

   '123-4567-789',100000)

GO

1. Default()

This is based on the data type of the column 

For Example :

 Data type of the column on which it is applied

• For string types it shows X for each character and max it displays 4 X’s.
• For numeric types it shows 0
• For dates shows 1900-01-01 00:00:00.000

We will apply the DEFAULT dynamic data masking function on the EmployeeName and DOJ columns of the Employee table by executing the following statement

---Add DEFAULT() masking function on the Name column

ALTER Table Employee

ALTER COLUMN EmployeeNAME ADD MASKED WITH (FUNCTION='DEFAULT()')

---Add DEFAULT() masking function on the Name column

ALTER Table Employee

ALTER COLUMN DOJ ADD MASKED WITH (FUNCTION='DEFAULT()')

Now let us grant select  permission on the Employee table by executing the following query.

--Create user uday

CREATE USER uday WITHOUT LOGIN

--Grant select permission to the user: reader

GRANT SELECT ON Employee TO uday

We can give the permissions using UI as shown below

Now we will fetch the Records

EXECUTE AS USER = 'uday'

SELECT * FROM Employee

REVERT

To unmask the user uday

GRANT UNMASK TO uday

Now run the same query

EXECUTE AS USER = 'uday'

SELECT * FROM Employee

REVERT

Output:

To Remove unmask permission to the user uday

-Remove Unmask permission from the user: uday

REVOKE UNMASK TO uday

2. Email()

This data masking function returns first character as it is and rest is replaced by XXX@XXXX.com.

Example :

---Add Email() masking function on the Name column

ALTER Table Employee

ALTER COLUMN EmailAddress 

 ADD MASKED WITH (FUNCTION='Email()')

To Fetch the records

Now, let us check the mask column value using a query whether it returns the masked value or unmasked value

EXECUTE AS USER = 'reader'

SELECT * FROM Employee 

WHERE EmailAddress = 'rajakondauday@gmail.com'

REVERT

Output:

Partial()

If we want to reveal only first and last name only partial data then use partial()

Syntax:

partial (prefix ,padding , suffix)

Where: prefix is the starting number of characters to be revealed and suffix is the last number of characters to be revealed from the column value. Padding is the custom padding string in the middle.

Applying this on Phone Number ALTER Table Employee ALTER COLUMN Phone ADD MASKED WITH (FUNCTION='Partial(2,"-ZZZ-",2)') Fetching the records

 Random()

This masking function can be applied on a column of numeric type. It returns a random value between the specified ranges.

ALTER Table Employee
ALTER COLUMN Salary ADD MASKED WITH (FUNCTION='Random(1,9)')

Fetch the results

EXECUTE AS USER = 'uday'
SELECT * FROM Employee
REVERT

To remove the mask definition we can use the drop command

ALTER TABLE Employee 

ALTER COLUMN Phone DROP MASKED