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

Sessions in SQL Server 2016

In .Net we have Session object which store and retrieve values for a user as user navigates ASP.NET pages in a Web application for that session. 

With Sql Server 2016 we are getting the similar feature in Sql Server, where we can store multiple key and value pairs which are accessible throughout that session. The key and value pairs can be set by the sp_set_session_context system stored procedure and these set values can be retrieved one at a time by using the 

SESSION_CONTEXT built in function.

SYNTAX of sp_set_session_context:

sp_set_session_context [ @key= ] 'key', [ @value= ] 'value'


    [ , [ @read_only = ] { 0 | 1 } ]


Where: @key parameter is of type SYSNAME, @value is of type 


SQL_VARIANT and @read_only parameter is of type BIT

SYNTAX of SESSION_CONTEXT

SESSION_CONTEXT(N'key')

Where:’key’ parameter to this function is of type SYSNAME and the return type of this function is SQL_VARIANT

 

EXAMPLE 1: This example demonstrates how we can set the session context key named Employee Id with it’s value and retrieving this set keys value.

--Set the session variable EmployeeId value

EXEC sp_set_session_context 'EmployeeId', 1

--Retrieve the session variable EmployeeId value

SELECT SESSION_CONTEXT(N'EmployeeId') AS EmployeeId

ScreenShot:

Note: If we skip the Prefix N then we will get the Error.

From the result it is clear that the SESSION_CONTEXT function always expects the key parameter passed to it should of type NVarchar.

EXAMPLE 2: update the Session variable value

--Set session variable EmployeeName value

EXEC sp_set_session_context @key = 'EmployeeName', 

  @value='Uday'

--Retrieve the session variable EmployeeName value

SELECT SESSION_CONTEXT(N'EmployeeName') EmployeeName

--Re-Set the EmployeeName session variable value

EXEC sp_set_session_context @key ='EmployeeName',

  @value='Rajakonda Uday'

--Retrieve the update session variable EmployeeName value

SELECT SESSION_CONTEXT(N'EmployeeName') EmployeeName

ScreenShot:

Example 3: Read Only Session Variable

--create a readonly session variable Actor

EXEC sp_set_session_context @key = 'Actor', @value='Number 1 Hero of Tollywood', 

  @read_only = 1

--Retrieve the session variable Actor value

SELECT SESSION_CONTEXT(N'Actor') Actor

ScreenShot

Example 4: Session variable can be accessed in stored procedure which is set somewhere outside.

Let us create a stored procedure GetCountry

CREATE PROCEDURE GetCountry AS BEGIN     SELECT SESSION_CONTEXT(N'Country') Country END --create a readonly session variable Country EXEC sp_set_session_context @key = 'Country', @value='India' --Retrieve the session variable Country value SELECT SESSION_CONTEXT(N'Country') Country Now in the same session execute the stored procedure GetCountry and see whether this SP has access to the Session variable Country: RESULT:   when we try to fetch a non-existent session variable  SELECT SESSION_CONTEXT(N'Continent') Continent