SQL Server 2016: Dynamic Data Masking

SQL Server 2016: Dynamic Data Masking

Share on Facebook0Share on Google+0Tweet about this on TwitterShare on LinkedIn0Share on Reddit0
Dynamic Data Masking is another data protection feature in SQL Server 2016. It limits sensitive data exposure by masking it to non-privileged users. The masking rules are applied in the query results and you designate how much of the sensitive data to reveal and to who. The Dynamic Data Masking feature provides four types of masking methods:
  • Default: Full masking according to the data types of the designated fields.
  • 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.
  • Custom String: 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.
  • Random: A random masking function for use on any numeric type to mask the original value with a random value within a specified range.

A simple example of Dynamic Data Masking is to display only the last 4 digits of a phone number to the support personnel of an IT Department or don’t show the email address to unauthorized users, etc.

Now let’s proceed with a demo in order to see some examples of using Dynamic Data Masking.

/*
Demo: Dynamic Data Masking
*/

–Create a sample database
IF DB_ID(‘SampleDB_DataMasking’) IS NULL
BEGIN
CREATE DATABASE SampleDB_DataMasking;
END
GO

–Use sample database
USE SampleDB_DataMasking;
GO

–Create sample table
CREATE TABLE tblEmployeesMasking
    (
      empName VARCHAR(250) ,
      empPhoneNo VARCHAR(20) ,
      empEmailAddress VARCHAR(100)
    );
GO

–Populate sample table with data
INSERT  INTO dbo.tblEmployeesMasking
VALUES  ( ‘John Clarck’, ‘545-5478-1234’, ‘john.clark@example.com’ ),
        ( ‘Kostas Andreou’, ‘878-8888-5678’, ‘kostas.andreou@example.com’ ),
        ( ‘Wei Xing’, ‘673-5123-9101’, ‘wei.xing@example.com’ );

–Check records (unmasked)
SELECT * FROM tblEmployeesMasking



Output



–Now let’s mask the email address
ALTER TABLE dbo.tblEmployeesMasking
ALTER COLUMN empEmailAddress VARCHAR(100) MASKED WITH (FUNCTION = ’email()’);
Let’s check again the records of the table, this time using a non-sysadmin user.
–Create user (non-sysadmin) with GRANT SELECT permission on the sample table
CREATE USER TestUser WITHOUT LOGIN;
GRANT SELECT ON dbo.tblEmployeesMasking TO TestUser;
GO

–Retrieve the table contents by running the query with TestUser
EXECUTE AS USER = ‘TestUser’;
SELECT * FROM dbo.tblEmployeesMasking;
GO
Output
As you can see in the above screenshot, the column’s ’empEmailAdress’ values are presented as masked.
OK. So now let’s switch the execution context back our original login’s context in order to proceed with masking the ’empPhoneNo’ column:
REVERT;
GO

–Let’s mask the phone number with default
ALTER TABLE dbo.tblEmployeesMasking
ALTER COLUMN empPhoneNo VARCHAR(100) MASKED WITH (FUNCTION = ‘default()’);
Let’s check the records of the table using a non-sysadmin user.
–Retrieve the table contents by running the query with TestUser
EXECUTE AS USER = ‘TestUser’;
SELECT * FROM dbo.tblEmployeesMasking;
GO

Output:
Now you can see that the column ‘EmpPhoneNo’ is also masked.
The next test is to remove the ’empPhoneNo’ column’s masking and set a custom masking function:
REVERT;
GO

–Remove masking 
ALTER TABLE dbo.tblEmployeesMasking
ALTER COLUMN empPhoneNo VARCHAR(100) 

–Let’s mask the phone number with custom function
ALTER TABLE dbo.tblEmployeesMasking
ALTER COLUMN empPhoneNo VARCHAR(100) MASKED WITH (FUNCTION = ‘partial(3,”-XXXX-XX”,2)’);
Let’s check the records of the table using a non-sysadmin user.
–Retrieve the table contents by running the query with TestUser
EXECUTE AS USER = ‘TestUser’;
SELECT * FROM dbo.tblEmployeesMasking;
GO

Output:
As you can see from the above screenshot, now the empPhoneNo column is masked based on our custom masking function.
Last, if you want to allow ‘TestUser’ to be able to see the masked data, you can grant her or him the UNMASK permission:
REVERT;
GO

— Granting the UNMASK permission
GRANT UNMASK TO TestUser;
GO
Let’s check the records of the table within the context of ‘TestUser’:
–Retrieve the table contents by running the query with TestUser
EXECUTE AS USER = ‘TestUser’;
SELECT * FROM dbo.tblEmployeesMasking;
GO

Output:
Because TestUser was granted the UNMASK permission, it was possible to see the unmasked data.

Similarly, if REVOKE the UNMASK permission, TestUser will only see the masked data:

Dynamic Data Masking is a significant feature in SQL Server 2016 and Azure SQL Database. As you can see from the above examples it is very easy to use it and it can really help you when it comes to Security and Compliance regarding yours or your organization’s most valuable asset: the data.

References: 
MSDN Library Article: What’s New in SQL Server 2016 Release Candidate (RC2)
MSDN Library Article: Dynamic Data Masking
Microsoft Azure Document: Get started with SQL Database Dynamic Data Masking (Azure Portal)


Reference: The SQL Server and .NET Hub (http://www.sqlnethub.com)


Recommended eBooks on SQL Server:

Tuning SQL Server: eBook by SQL Server MVP Artemakis Artemiou
Tuning SQL Server: eBook by SQL Server MVP Artemakis Artemiou
Administering SQL Server: eBook by SQL Server MVP Artemakis Artemiou
Administering SQL Server: eBook by SQL Server MVP Artemakis Artemiou
Artemakis Artemiou
Artemakis Artemiou is a Senior SQL Server Architect, Author, Software Developer and a Microsoft Data Platform MVP. He has over 15 years of experience in the IT industry in various roles. Among other, via his initiative SQLEBooks.com, Artemakis authors and publishes eBooks on different topics on SQL Server. Artemakis currently serves as the President of the Cyprus .NET User Group (CDNUG) and the International .NET Association Country Leader for Cyprus (INETA). Additionally he is the founder of the SQLArtBits initiative that aims to provide the technical community with simple, yet powerful and high-quality SQL Server tools. Currently, the highlights of these tools are DBA Security Advisor and In-Memory OLTP Simulator. Artemakis's official website can be found at aartemiou.com. Artemakis's blogs can be found at: SQLNetHub.com and TechHowTos.com.