Share via

T-SQL: Dynamic Data Masking

  • Article

Introduction

  This is one of the two articles that bring us Dynamic Data Masking solutions: (1) Using Security layer, which is this article, and (2) Using SQL Server 2016 new feature. Both articles use the same introduction and preparation. If you already created all the basic elements from the other article, then you can jump to the next section: Building Security layer. 

Dynamic data masking (DDM) is a solution that aims at real-time data masking of production data. DDM changes the data stream so that the data requester does not get full access to sensitive data, while no physical changes to the original production data take place.

DDM does not intend to fully prevent unauthorized access to sensitive data. For this we can revoke any use of the data, on database level, on elements level (table, view, functions, etc.), on rows level (built-in from SQL 2016 version), on columns level, etc. By revoking permissions to use the data, any attempt of using the unauthorized data will raise an error. Instead of fully preventing unauthorized access to sensitive data, DDM gives us solutions to expose some of the information, while masking the rest. This allows us to determine how and which parts of the information will be exposed. 

For example, a client service operator at a call center may identify callers by the 4 last digits of their Credit Card number. Therefore, we do not want to fully prevent access to the sensitive data (credit card number). We need to expose this part of the data (the 4 last digits), but at the same time, the credit card number should not be fully exposed to the operator. In other words, we want to mask part of the data while exposing other part of the data.

There are several ways we can implement Dynamic Data Masking, for example: (1) creating policy-based security layer at the Database level, (2) using policy-based security Feature at Database level(This feature was added to SQL Server 2016), (3) using external intermediate layer between the server and the client like proxy, and (4) using external security layer in the application level.

Designing external security layer in the application level is a common scenario for application’s architecture. This solution based on the assumption that our data available only throw our application. In reality this is probably not the case! This solution leaves the data unsecured, if users access tables directly rather than through the given application. A security layer best enforce the security rules in the database level. Moreover, forcing the security rules in the database level has minimal impact on the application layer, and it is behave like a “black box”.

In this article we will focus on creating policy-based security layer at the database level. This can be achieved using elements like Views, table-valued functions, Triggers, Stored Procedures, etc.

 You can download the entire code presented in this article, as part of zip file that includes lecture material presented at the Microsoft offices in Israel. The lecture was about "INSTEAD of TRIGGER" and that code was presented towards the end of the lecture.

Preparation

Step 1: Creating new database

Let's create new database named MaskingDemo

/****************************************************  */
/************************************* CREATE DATABASE */
/****************************************************  */
 
-- drop database MaskingDemo - if already exists
IF DB_ID('MaskingDemo') IS NOT NULL
BEGIN
    ALTER DATABASE [MaskingDemo] SET SINGLE_USER WITH ROLLBACK IMMEDIATE
    DROP DATABASE [MaskingDemo]
END
GO
 
-- create new database called MaskingDemo
CREATE DATABASE MaskingDemo
GO

Step 2: Create New Database USER

In order to implement policy-based security we need to create a new database user. It is highly recommended for most cases to use operating system user (WINDOWS USER), but for the sake of this example we will start with creating new CREDENTIAL which use IDENTITY that we did not created.

  If there is no login mapped credential for the provider, the credential mapped to SQL Server service account is used. 

Next, we will create new SQL Server LOGIN, which associated to the CREDENTIAL. And last, we will creates a new Database User for our LOGIN.

/****************************************************  */
/*********************************** SECURITY Elements */
/****************************************************  */
 
/*------------------------------------------- WINDOWS USER */
-- Step 1: create WINDOWS USER named WinUser
-- It is highly recommended to create a specific windows user for each application,
-- in order to isolate the application requirements (isolate operating system resources).
 
/*------------------------------------------- CREDENTIAL */
-- Step 2: create CREDENTIAL
 
-- A credential is a record that contains the authentication information (credentials)
-- required to connect to a resource outside SQL Server.
-- The information stored in a credential enables a user who has connected to SQL Server by way of SQL Server Authentication
-- to access resources outside the server instance.
 
-- If there is no login mapped credential for the provider, the credential mapped to SQL Server service account is used.
CREATE CREDENTIAL MaskingCredential WITH IDENTITY = 'MaskingWinUser',
    SECRET = 'StrongPasswordHere';
GO
 
select * from sys.credentials
GO
 
/*------------------------------------------- LOGIN */
-- Step 3: create SQL Server LOGIN
CREATE LOGIN MaskingLogin
   WITH PASSWORD = 'Strong!Login@Password',
   CREDENTIAL = MaskingCredential;
GO
 
SELECT * FROM sys.server_principals
GO
 
SELECT * FROM syslogins
where loginname = 'MaskingLogin'
GO
 
/*------------------------------------------- USER */
-- step 4: create database USER
USE MaskingDemo;
GO
 
---- Instead of steps 1-2 we can create user which IMPERSONATE to a OS user directly
--CREATE USER MaskingUser WITHOUT LOGIN ;
--GRANT IMPERSONATE ON USER::MaskingUser TO [adventure-works\tengiz0] ;
--GO
 
CREATE USER MaskingUser FOR LOGIN MaskingLogin
    WITH DEFAULT_SCHEMA = dbo;
GO
 
SELECT * from sys.database_principals
where name = 'MaskingUser'
GO

Step 3: Create table and insert some sample data

Now we can create our testing table and insert some sample data

/****************************************************  */
/********************************************* DDL+DML */
/****************************************************  */
 
-- Create table with different data type columns
CREATE TABLE MyContacts (
     ID             INT            IDENTITY(1, 1) PRIMARY KEY
    ,fName          NVARCHAR(30)   NOT NULL
    ,lName          NVARCHAR(30)   NOT NULL
    ,CreditCard     VARCHAR(20)    NULL
    ,SalaryINR      INT            NULL
    ,Email          NVARCHAR(60)   NULL
    ,BirthDate      DATETIME       NULL
)
GO
 
-- insert sample data
INSERT INTO [dbo].[MyContacts]
    ([fName],[lName] ,[CreditCard],[SalaryINR],[Email], [BirthDate])
VALUES
    ('Ronen','Ariely','1111-1234-1234-1111',999999,'Not@MyEmail.com'          , '20150227'),
    ('R'    ,'A'     ,'2222-1234-1234-2222',999999,'DefinitelyNot@MyEmail.com', '20160227')
GO
 
select * from dbo.MyContacts
GO

 

Building Security layer

The primary idea of security layer is not to open any privilege on the data layer. The data layer, which in database includes the tables that stores the data, should not be accessible. Instead we create other elements like Stored Procedures, functions, Views that expose the data. These elements are our security layer and we grants permissions to users to access and to use these elements.

For our demo, we will create two elements in the security layer: (a) an Inline User-Defined Function that select the data from the original table (our DATA layer), but returns the information masked, using any format that we want. (b) A View that use the same query as the function above, and returns the data from the DATA layer after masking, using the same logic.

Next we will create INSTEAD of TRIGGER in order to deal with DML request that come throw the VIEW.

Last step, is to grant the user/s permissions to use the security layer, which means that we expose these elements to external requests.

Let's Execute the code: 

/****************************************************  */
/*********** implementing Masking Using Security layer */
/****************************************************  */
-- Database Architecture, Security layer. Exposing data throw client layer
-- let's assume that my main user name is RonenAriely, and my machine name is MachineName. We can use this information in order to mask the data for any other that is not me
 
IF OBJECT_ID(N'MyContactsF', N'IF') IS NOT NULL DROP FUNCTION MyContactsF;
GO
CREATE FUNCTION MyContactsF() RETURNS table AS
    RETURN (
        SELECT
            CASE WHEN SYSTEM_USER = 'MachineName\RonenAriely' THEN  [fName]       ELSE 'xxxx'                                                               END as [fName],
            CASE WHEN SYSTEM_USER = 'MachineName\RonenAriely' THEN  [lName]       ELSE [lName]                                                              END as [lName],
            CASE WHEN SYSTEM_USER = 'MachineName\RonenAriely' THEN  [CreditCard]  ELSE  (LEFT([CreditCard],2) + N'XX-XXXX-XXXX-XX' + RIGHT([CreditCard],2) ) END as [CreditCard],
            CASE WHEN SYSTEM_USER = 'MachineName\RonenAriely' THEN  [SalaryINR]   ELSE 0                                                                    END as [SalaryINR],
            CASE WHEN SYSTEM_USER = 'MachineName\RonenAriely' THEN  [Email]       ELSE  (LEFT([Email],1) + N'XXX@XXXX' +RIGHT([Email],4) )                  END as [Email],
            CASE WHEN SYSTEM_USER = 'MachineName\RonenAriely' THEN  [BirthDate]   ELSE CONVERT(DATETIME,'20000101')                                         END as [BirthDate]
        FROM MyContacts
    )
GO
 
CREATE VIEW MyContactsV as
    SELECT
        CASE WHEN SYSTEM_USER = 'MachineName\RonenAriely' THEN  [fName]       ELSE  'xxxx'                                                               ENDas [fName],
        CASE WHEN SYSTEM_USER = 'MachineName\RonenAriely' THEN  [lName]       ELSE [lName]                                                              END as [lName],
        CASE WHEN SYSTEM_USER = 'MachineName\RonenAriely' THEN  [CreditCard]  ELSE  (LEFT([CreditCard],2) + N'XX-XXXX-XXXX-XX' + RIGHT([CreditCard],2) ) END as [CreditCard],
        CASE WHEN SYSTEM_USER = 'MachineName\RonenAriely' THEN  [SalaryINR]   ELSE 0                                                                    END as [SalaryINR],
        CASE WHEN SYSTEM_USER = 'MachineName\RonenAriely' THEN  [Email]       ELSE  (LEFT([Email],1) + N'XXX@XXXX' +RIGHT([Email],4) )                  END as [Email],
        CASE WHEN SYSTEM_USER = 'MachineName\RonenAriely' THEN  [BirthDate]   ELSE CONVERT(DATETIME,'20000101')                                         END as [BirthDate]
    FROM MyContacts
GO
 
 
-- * View can have expressions other than simple column name.
--   In these cases, we have to use INSTEAD of TRIGGER, in order to update the table through the view.
 
-- Create INSTEAD of trigger on VIEW
CREATE TRIGGER dbo.MyContactsV_ioTrig ON dbo.MyContactsV
INSTEAD OF INSERT AS BEGIN
    SET NOCOUNT ON
     
    INSERT INTO [dbo].[MyContacts]
        ([fName],[lName] ,[CreditCard],[SalaryINR],[Email], [BirthDate])
    select I.[fName], I.[lName], I.[CreditCard],I.SalaryINR,I.Email,I.BirthDate
    from inserted I
END
GO
 
GRANT SELECT ON MyContactsV TO MaskingUser;
GRANT INSERT ON MyContactsV TO MaskingUser;
GRANT UPDATE ON MyContactsV TO MaskingUser;
GO
 
GRANT SELECT ON MyContactsF TO MaskingUser;
GO

 

  For more information regarding INSTEAD of TRIGGER, you can check this article:  T-SQL: INSTEAD OF Triggers

Play Time, testing :-)

In order to show the power of Dynamic Data Masking you can open two connection to the database, one connection with your main user (the user that have full permissions), and one connection using the new LOGIN that we created in this article. Another option is just using your main user and execute frst test as current user and use impersonating to the new user that we created using "EXECUTE AS" For the second test. 

/************************************* Testing Masking */
-- The application should work with the VIEWs and Functiuons
-- INSTEAD of working dirrectly with the Tabels
 
/*------------------------------------------- Current user */
-- this would show clear data
select CURRENT_USER
SELECT * FROM MyContactsV;
SELECT * FROM MyContactsF();
GO
 
/*------------------------------------------- MaskingUser user */
-- this should show masked data
EXECUTE AS USER = 'MaskingUser';
select CURRENT_USER
SELECT * FROM MyContactsV;
SELECT * FROM MyContactsF();
REVERT;
GO
 
-- Even if we are using View in order to mask the data, and we have no direct permission on the table,
-- we can insert data throw the VIEW
EXECUTE AS USER = 'MaskingUser';
select CURRENT_USER
INSERT INTO [dbo].[MyContactsV]
    ([fName],[lName] ,[CreditCard],[SalaryINR],[Email], [BirthDate])
VALUES
    ('RR','AA','3333-1234-1234-3333',999999,'YetNot@MyEmail.com'          , '20170227')
SELECT * FROM MyContactsV;
REVERT;
GO

 

Comments & Conclusions

 Designing external security layer in the application level based on the assumption that our data available only through our external security layer. This solution leaves the data unsecured, if users access it directly rather than through the security layer!

  SQL Server version 2016 brings us a new feature: Dynamic Data Masking. As mentioned above in the introduction this feature gives us a Database level policy-based security Feature.

  Creating Security layer enable us to expose the data in any format that we want, using any function that we want, including SQLCLR elements. It is much more flexible than the built-in solution in SQL 2016.

  Creating Security layer must expose any DATA that we need to use. This procedure might be time consuming. If our only purpose is masking the data the built-in solution in SQL 2016 gives us much faster and simpler solution. 

 You can download the entire codes presented in this article, as part of zip file that include lecture material that was presented at Microsoft offices in Israel. The lecture was about "INSTEAD of TRIGGER" and these codes were the last example that presented.

 

Summary

 In this article we showed simple way to create a "Dynamic DATA Masking" solution, based on internal security layer. The security layer was achieved by restricting any direct use on the tables, and by granting permissions on the security layer's elements. In this way, the security layer is exposed to external requests while the original data inaccessible. Our security layer based on creating elements like Views, table-valued functions, Triggers, Stored Procedures, etc. These elements uses the original DATA, and returns masked data in any format that we chose.

 

Resources and more information