SQL Server 2016 Helps Us to Protect the Sensitive Data

Introduction

SQL Database Dynamic Data Masking limits sensitive data exposure by masking it to non-privileged users. When we logged into most of the banking site, the account number/credit card number will not be displayed in a clear format. It will display the last four characters, as it can be done with through the application or TSQL Programming. Now SQL server 2016 makes this possible through the database without extra coding.

• Protect sensitive data.
• It will not encrypt your data.
• Choose the mask type which makes the column understable and fit.
• The user should have unmask permission to view the original data.
• The user without unmask permission can still update the original data.
• Data masking will be applied when select into statements is used.

Types

Default

Data Type	Mask
String	XXXX
Numeric	0
Date Time	01.01.1900 00:00:00.0000000
Binary	0

Syntax

ADD MASKED WITH (FUNCTION = 'default()')

Email

It will display the first letter of an email address XXXX followed by @ and suffix with the .com.

Syntax

ADD MASKED WITH (FUNCTION = ‘email()')

Random

A random masking will mask the random value in the numeric data type with the specific range. It will display random values in each statement.

ADD MASK (FUNCTION = 'random(1001,2000)’)   

Partial / Custom Mask

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.

ADD MASKED WITH (FUNCTION = ‘partial(prefix,[padding],suffix)’

Creating a Table with Dynamic Data Masking

CREATE TABLE Customers
    (
      AccountId INT IDENTITY(10000, 1)
                    PRIMARY KEY ,
      FirstName VARCHAR(100) NOT NULL ,
      LastName VARCHAR(100) MASKED WITH (FUNCTION = 'default()' )
                            NOT NULL ,
      Mobile VARCHAR(15) ,
      Email VARCHAR(100) ,
      Balance DECIMAL(10, 2) ,
      CreditCard VARCHAR(20) ,
      DOB DATETIME ,
     
    );
GO

Adding More Masking Using the Alter Statement

ALTER TABLE dbo.Customers ALTER COLUMN Email ADD MASKED WITH(FUNCTION='email()')
ALTER TABLE dbo.Customers ALTER COLUMN Mobile  _
     ADD MASKED WITH(FUNCTION = 'partial(0,"XXXXXX",4)')
ALTER TABLE dbo.Customers ALTER COLUMN CreditCard ADD MASKED WITH(FUNCTION ='default()')
ALTER TABLE dbo.Customers ALTER COLUMN DOB ADD MASKED WITH(FUNCTION='default()')
ALTER TABLE dbo.Customers ALTER COLUMN Balance ADD MASKED WITH(FUNCTION='random(1000,2000)')

INSERT  INTO Customers
VALUES  ( 'Suresh', 'Kumar', '+919123456789', 'suresh@yahoo.com', 105000,
          '4563-2345-7654-7834', '11-jun-1990' ),
        ( 'Deepak', 'Raj', '+919123456789', 'deepak@yahoo.com', 125000,
          '2354-3435-2345-2345', '14-Apr-1989' ),
        ( 'Santhosh', 'Kumar', '+919123456789', 'san@yahoo.com', 125000,
          '1235-5465-5756-7567', '16-Jun-1989' );
		  GO

The records will be displayed without masking for the admin user and unmask permitted users.

    SELECT  * FROM    dbo.Customers;

CREATE USER user1  WITHOUT LOGIN
GO
          
GRANT SELECT ON dbo.Customers TO user1;
GO
          
EXECUTE AS USER='user1';
SELECT  *
FROM    dbo.Customers;
REVERT; 

The random masking for the balance displayed with the random generate values in each select statement.

To view the actual data for the user, the user must provide with unmask permission:

GRANT UNMASK TO user1;
GO

EXECUTE AS USER='user1';
SELECT  *
FROM    dbo.Customers;
REVERT; 

To remove the unmask permission from the user:

REVOKE UNMASK TO user1

EXECUTE AS USER='user1';
SELECT  *
FROM    dbo.Customers;
REVERT;

To remove mask column from a table:

ALTER TABLE dbo.Customers  ALTER COLUMN LastName DROP MASKED

EXECUTE AS USER='user1';
SELECT  *
FROM    dbo.Customers;
REVERT;

Limitation

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

• Always Encrypted columns
• File Stream
• 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
• A column with data masking cannot be a key for a FULLTEXT index

References

• Dynamic Data Masking

History

• 8^th July, 2016: Initial version