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
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)') |
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:
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 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
A masking rule cannot be defined for the following column types:
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.
NOTE: For reference, we are using Adventureworks2016CTP3
database throughout the document.
Download AdventureWorks database and restore it to SQL Server 2016 instance.
Note: The provided link is mapped to SQL 2016 CTP3 version downloads. Look for the latest version.
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.
USE [AdventureWorks2016CTP3]
--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)')
--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.
USE [AdventureWorks2016CTP3]
-- 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;
--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;
--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:
On Lab
USE [AdventureWorks2016CTP3]
--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
CREATE DATABASE RLS
GO
USE RLS
GO
CREATE TABLE dbo.Customers
(
CustomerId int,
CustomerName nvarchar(100),
SalesRepName nvarchar(100)
)
GO
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 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;
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