SQL Server 2016 Dynamic Data Masking and Row-Level Security - Deployment Guide

Overview

Dynamic data masking limits sensitive data exposure by masking it to non-privileged users. It's a data protection feature that hides the sensitive data in the result set of a query over designated database fields, while the data in the database is not changed. Dynamic data masking is easy to use with existing applications, since masking rules are applied in the query results. For example, a call center support person may identify callers by several digits of their social security number or credit card number, but those data items should not be fully exposed to the support person. A masking rule can be defined that masks all but the last four digits of any social security number or credit card number in the result set of any query

Row-level security controls access to data at a more granular level than what simply granting, revoking, or denying permissions provides. For example, a hospital database application may require individual doctors to be restricted to accessing information related to only their patients. SQL Server 2016 provides a Row-Level Security feature that simplifies and centralizes row-level access logic in a security policy

How It Works

Dynamic Data Masking

Dynamic data masking works by adding following functions with columns metadata

Function

Description

Examples

Default

Full masking according to the data types of the designated fields.

Example column definition syntax:

Phone# varchar(12) MASKED WITH (FUNCTION = 'default()') NULL

Example alter syntax: ALTER COLUMN Gender ADD MASKED WITH (FUNCTION = 'default()')

Email

Masking method which exposes the first letter of an email address and the constant suffix ".com", in the form of an email address. .aXXX@XXXX.com.

Example definition syntax:

Email varchar(100) MASKED WITH (FUNCTION = 'email()') NULL

Example alter syntax: ALTER COLUMN Email ADD MASKED WITH (FUNCTION = 'email()')

Custom String

Masking method which exposes the first and last letters and adds a custom padding string in the middle. prefix,[padding],suffix

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

Example definition syntax:

FirstName varchar(100) MASKED WITH (FUNCTION = 'partial(prefix,[padding],suffix)') NULL

Example alter syntax: ALTER COLUMN [Phone Number] ADD MASKED WITH (FUNCTION = 'partial(1,"XXXXXXX",0)')

Random

A random masking function for use on any numeric type to mask the original value with a random value within a specified range.

Example definition syntax:

Account_Number bigint MASKED WITH (FUNCTION = 'random([start range], [end range])')

Example alter syntax: ALTER COLUMN [Month] ADD MASKED WITH (FUNCTION = 'random(1, 12)')

Row-level Filtering

RLS is a form of "predicate-based access control" — it works by automatically applying a security predicate to all queries on a table. The predicate determines which users can access which rows. For example, a simple predicate might be, "WHERE SalesRep = CURRENT_USER", while a complicated predicate might include JOINs to look up information in other tables.

There are two types of security predicates:

  • Filter predicates silently filter SELECT, UPDATE and DELETE operations to exclude rows that do not satisfy the predicate. Note: FILTER predicate will not block the user from INSERTING a record which after insert belongs to some other user. For that purpose we will need block predicate
  • Block predicates explicitly block INSERT, UPDATE and DELETE operations that do not satisfy the predicate. There are four types of BLOCK predicates AFTER INSERT, BEFORE UPDATE, AFTER UPDATE and BEFOR DELETE.
    • The AFTER INSERT BLOCK Predicate blocks user from inserting a record which after insert doesn't satisfy predicate function. In other words, AFTER INSERT BLOCK predicate blocks the user from inserting a record which after insert user doesn't have access to it.

To add security predicate on a table, you first need an inline table-valued function that defines your access criteria. Then, you create a security policy that adds filter and block predicates on any tables you like, using this function. Here's a simple example that prevents sales representatives from accessing rows in a customer's table that are not assigned to them:

The following example describes how to configure row-level filtering based on a user or login name:

  • Create the table, adding a column to store the name.
  • Enable row-level filtering
    • Create a security policy that adds a predicate on the table restricting the rows returned to those that match either the current database user (using the CURRENT_USER() built-in function) or the current login name (using the SUSER_SNAME() built-in function):

CREATE SCHEMA Security
GO
CREATE FUNCTION Security.userAccessPredicate(@UserName sysname)
    RETURNS TABLE
    WITH SCHEMABINDING
AS
    RETURN SELECT 1 AS accessResult
    WHERE @UserName = SUSER_SNAME()
GO
CREATE SECURITY POLICY Security.userAccessPolicy
    ADD FILTER PREDICATE Security.userAccessPredicate(UserName) ON dbo.MyTable,
    ADD BLOCK PREDICATE Security.userAccessPredicate(UserName) ON dbo.MyTable
GO

  • Create stored procedures to select, insert, update, and delete data
  • For stored procedures that insert data, capture the user name using the same function specified in the security policy, and insert that value into the UserName column.
  • Deny all permissions on the tables (and views, if applicable) to the public role. Users will not be able to inherit permissions from other database roles, because the filter predicate is based on user or login names, not on roles.
  • Grant EXECUTE on the stored procedures to database roles. Users can only access data through the stored procedures provided.

Limitations and Restrictions

A masking rule cannot be defined for the following column types:

  • Encrypted columns (Always Encrypted)
  • FILESTREAM
  • COLUMN_SET
  • A mask cannot be configured on a computed column, but if the computed column depends on a column with a MASK, then the computed column will return masked data.

Environment

Prerequisites

  1. SQL Server 2016 CTP 3 should be configured on your server.
  2. Your desired database should be created on the server instance.

    NOTE: If you do not have any database you can use sample database Adventureworks2016CTP3. For downloading sample database, you can refer to steps in 4.1.1.

  3. On the database server, sign in to SQL 2016 Management Studio using the windows credentials (default).

    NOTE: For reference, we are using Adventureworks2016CTP3
    database throughout the document.

Download AdventureWorks database and restore it to SQL Server 2016 instance.

  1. Remote logging to the Server using local administrator credential.
  2. Open Server Manager (located on the task bar near to start button), click on local server and turn off IE Enhanced Security for Administrators (you can turn it on after the downloads)
  3. Go to http://www.microsoft.com/en-us/download/details.aspx?id=49502 , select and download the AdventureWorks2016CTP3 database back up (.bak) file to a folder.

    Note: The provided link is mapped to SQL 2016 CTP3 version downloads. Look for the latest version.

  4. Open SQL Server 2016 Management Studio and connect to the default instance using Windows credentials.
  5. Right click on the Server and click Restore Database, on the wizard select Device and browse for the AventureWorks2016CTP3.bak file downloaded. Click OK to start the restore.
  6. Wait (it will take 1 – 2 minutes) for the "Database Adventureworks2016CTP3 restored successfully" message and click OK
  7. Right click on the database restored and click on New Query, One the SQL Query window issue the below command and verify result set is returned successfully:

Select top 10 * from Sales.Customer

Post Condition

SQL Server 2016 CTP3 is up and running in the Database server.

If you have downloaded the sample database then from SQL management studio, you can see the AdventureWorks2016CTP3 database created on server.

Figure 3 Customer records displayed through the Management Services

Figure 3 executes a simple SELECT query to retrieve data from the restored database.

Implement Dynamic Data Masking

Scenario 1: Configuring Masking Rules and Creating Privileged Logins

  1. Remote to the Database server using local Administrator credential.
  2. Sign in to SQL 2016 Management Studio using the windows credentials (default), right click the instance and open 'New Query'
  3. Issue the below script to change the query context to AdventureWorks2016CTP3 database

USE [AdventureWorks2016CTP3]

  1. Execute the below Script to Alter the table definition [Sales]. [Client] to mask the PII information i.e. LastName/PhoneNumber/Email and table [Sales]. [SalesOrderHeader] to mask AccountNumber using different Masking Functions.

--Adding Masking Rules
ALTER TABLE [Sales].Client ALTER COLUMN [EmailAddress] ADD MASKED WITH (FUNCTION ='EMAIL()')
ALTER TABLE [Sales].Client ALTER COLUMN [lastName] ADD MASKED WITH (FUNCTION ='DEFAULT()')
ALTER TABLE [Sales].Client ALTER COLUMN [PhoneNumber] ADD MASKED WITH (FUNCTION ='PARTIAL(2,"XXXXXX",2)')
ALTER TABLE [Sales].[SalesOrderHeader] ALTER COLUMN [AccountNumber] ADD MASKED WITH (FUNCTION ='partial(2,"XX-XXXX-XXXX-XX",2)')

  1. Execute the below script to create 2 Users, authorized and Unauthorized.

--Create a Login ID in the server
CREATE LOGIN AuthorisedLogin WITH password = 'My#Password123'
CREATE LOGIN UnAuthorisedLogin WITH password = 'Unknown@123'
GO

--Mapping the Authorized User to required Database
CREATE USER AuthorisedUser FROM LOGIN AuthorisedLogin;
CREATE USER UnAuthorisedUser FROM LOGIN UnAuthorisedLogin;
GO

--Mapping to existing database roles
EXEC sp_addrolemember 'db_datareader', 'AuthorisedUser';
EXEC sp_addrolemember 'db_datareader', 'UnAuthorisedUser';
GO

Post Condition

You should be able to see masked data for tables Sales.Client and Sales.SalesOrderHeader once the proper permissions are granted to the users created above.

Scenario 2: Grant/Revoke Different Masking Permissions

  • Remote to the Database server using local Administrator credential.
  • Sign in to SQL 2016 Management Studio using the windows credentials (default), right click the instance and open 'New Query'
  • Issue the below script to change the query context to AdventureWorks2016CTP3 database

    USE [AdventureWorks2016CTP3]

  • Execute the below script to GRANT UNMASK permission to Authorized User.

-- Granting UNMASK permission to AuthorisedUser
GRANT UNMASK To AuthorisedUser;
EXECUTE AS USER = 'AuthorisedUser';
SELECT Top(2) * FROM Sales.Client;
SELECT Top(2) * FROM Sales.SalesOrderHeader;
REVERT;

  • Issue the below script to GRANT only the SELECT permission to Unauthorized User.

--GRANTING SELECT Permission to UnAuthorisedUser
GRANT SELECT ON Sales.Client TO UnAuthorisedUser;
GRANT SELECT ON Sales.SalesOrderHeader TO UnAuthorisedUser;
EXECUTE AS USER = 'UnAuthorisedUser';

SELECT Top(2)* FROM Sales.Client;
SELECT Top(2)* FROM Sales.SalesOrderHeader;
REVERT;

  • Issue the below script to REVOKE UNMASK permission from Authorized User.

--Revoking UNMASK Permission from AuthorisedUser
REVOKE UNMASK TO AuthorisedUser;
EXECUTE AS USER = 'AuthorisedUser'
SELECT Top(2)* FROM Sales.Client;
SELECT Top(2)* FROM Sales.SalesOrderHeader;
REVERT;

Post Condition

You should be able to see following screenshots:

  1. After granting the UNMASK Permission, AuthorizedUser retrieves unmasked data from the columns for which masking is defined.

    On Lab

  1. After granting only the SELECT Permission, UnauthorizedUser retrieves masked data.

  1. After revoking the UNMASK Permission, UnauthorizedUser now retrieves masked data.

Scenario 3: Deleting Masking Rules

  • Remote to the Database server using local Administrator credential.
  • Sign in to SQL 2016 Management Studio using the windows credentials (default), right click the instance and open 'New Query'
  • Issue the below script to change the query context to AdventureWorks2016CTP3 database

    USE [AdventureWorks2016CTP3]

  • Issue the below script to DELETE Masking rules.

--Dropping Masking rules on each column

ALTER
TABLE [Sales].[Client] ALTER
COLUMN [EmailAddress] DROP
MASKED

ALTER
TABLE [Sales].[Client] ALTER
COLUMN [LastName] DROP
MASKED

ALTER
TABLE [Sales].[Client] ALTER
COLUMN [PhoneNumber] DROP
MASKED

ALTER
TABLE [Sales].[SalesOrderHeader] ALTER
COLUMN [AccountNumber] DROP
MASKED

Post Condition

You should be able to see that after deleting masks from each column, UnauthorisedUser retrieves unmasked data:

On Lab

Implement Row Level Security

  1. Create database RLS (to test row level security) and Customer table

CREATE DATABASE RLS

GO

USE RLS

GO

CREATE TABLE dbo.Customers
(
    CustomerId int,
    CustomerName nvarchar(100),
    SalesRepName nvarchar(100)
)

GO

  1. Create user defined function and define security policy

CREATE SCHEMA Security

GO

CREATE FUNCTION Security.userAccessPredicate(@UserName sysname)

    RETURNS TABLE
    WITH SCHEMABINDING AS
    RETURN SELECT 1 AS accessResult
    WHERE @UserName = USER_NAME()
GO

CREATE SECURITY POLICY Security.userAccessPolicy
    ADD FILTER PREDICATE Security.(SalesRepName) ON dbo.Customers,
    ADD BLOCK PREDICATE Security.(SalesRepName) ON dbo.Customers

GO

  1. Create users without login and grant them select, insert, update, and delete access on table

CREATE USER SalesRep1 WITHOUT LOGIN;
CREATE USER SalesRep2 WITHOUT LOGIN;
CREATE USER SalesRep3 WITHOUT LOGIN;
GRANT SELECT, INSERT, UPDATE, DELETE ON dbo.Customers TO SalesRep1;
GRANT SELECT, INSERT, UPDATE, DELETE ON dbo.Customers TO SalesRep2;
GRANT SELECT, INSERT, UPDATE, DELETE ON dbo.Customers TO SalesRep3;

  1. Now insert some data, using context of each Sales Representative. If you try to insert the data without setting the context, you will get an error

EXECUTE AS USER = 'SalesRep1'
INSERT INTO dbo.Customers (CustomerId, CustomerName, SalesRepName)
VALUES (1, 'Customer1', 'SalesRep1'),
    (2, 'Customer2', 'SalesRep1'),
    (3, 'Customer3', 'SalesRep1'),
    (4, 'Customer4', 'SalesRep1')
GO

REVERT

EXECUTE AS USER = 'SalesRep2'

INSERT INTO dbo.Customers (CustomerId, CustomerName, SalesRepName)
VALUES
    (5, 'Customer5', 'SalesRep2'),
    (6, 'Customer6', 'SalesRep2'),
    (7, 'Customer7', 'SalesRep2')
GO

REVERT

EXECUTE AS USER = 'SalesRep3'

INSERT INTO dbo.Customers (CustomerId, CustomerName, SalesRepName)
VALUES (8, 'Customer8', 'SalesRep3')\

GO

REVERT

Post Condition

  1. Once you have inserted the data in the table using the context, Querying the data without setting the context will give the following result:

  1. Now set the context of 'SalesRep1' before running select. You will get data specific to 'SalesRep1'

References