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()') |
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 SCHEMABINDINGAS
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.MyTableGO
- 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
SQL Server 2016 CTP 3 should be configured on your server.
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.
On the database server, sign in to SQL 2016 Management Studio using the windows credentials (default).
NOTE: For reference, we are using Adventureworks2016CTP3database throughout the document.
**Download AdventureWorks database and restore it to SQL Server 2016 instance.
**
Remote logging to the Server using local administrator credential.
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)
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.
Open SQL Server 2016 Management Studio and connect to the default instance using Windows credentials.
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.
Wait (it will take 1 – 2 minutes) for the "Database Adventureworks2016CTP3 restored successfully" message and click OK
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
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 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)')
- 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:
After granting the UNMASK Permission, AuthorizedUser retrieves unmasked data from the columns for which masking is defined.
On Lab
- After granting only the SELECT Permission, UnauthorizedUser retrieves masked data.
- 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
- 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
- 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
- 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;
- 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
**
- Once you have inserted the data in the table using the context, Querying the data without setting the context will give the following result:
- Now set the context of 'SalesRep1' before running select. You will get data specific to 'SalesRep1'
