Dynamic Data Masking (SQL 2016 new feature)

Another useful feature of SQL server is the introduction of dynamic data masking. The introduction of this feature supports real-time masking of data from non-privileged requesters. Note here, privileged requesters can still access the masked data. Introduction of this feature as part of the database engine will definately boost data security and can be combined with other security features

The use of the feature can be varied.  Masking production data for UAT and QA testing would probably be attractive one. The other use can be in production environment to limit/avoid sensitive data viewing by non-privileged support personnel. 

It is not as sophisticated as some other third party or application masking tools but will do the work if used appropriately. The main issue will be how good basic masking function provided is enough for the business. Dynamic data masking currently supports three types’ of functions

1. Default masking function provides full masking depending on data type of a column. For instance.
   
   • If the data type is integer , money, decimal it will display as zero
   •  If the data type is character it will replace an entire string with four X’s (XXXX)
2.  The second built in function is email.  It exposes the first character of your email address, keeps @ character and replaces the domain name with XXXX and the leading. For instance if your email address is mry@mydomain.[com or .co.uk or .org or .net] will replace with mxx@xxxx.com
3. The third function is partial function. It will allow you to define how many numbers of the first characters/numbers and last characters/numbers you want to be displayed and choose the padding between the exposed characters/numbers.

Now let us see those feature in action

Now let us see those feature in action.

1.       To use this functionality trace flags 219 and 209 needs to be enabled.

So run  DBCC TRACEON(209,219,-1)

2.       Let us create a table that we can use to demonstrate how masking works. I will try to include all the functions provided.  On my table, I created fields with character that I can either

CREATE TABLE customers

       (CustomerId int IDENTITY PRIMARY KEY,

       FirstName varchar(100) MASKED WITH (FUNCTION = 'partial(1,"X*XXXXX",2)') NULL,

       LastName varchar(100) NOT NULL,

       Phone varchar(12) MASKED WITH (FUNCTION = 'default()') NULL,

       EMail varchar(100) MASKED WITH (FUNCTION = 'email()') NULL,

       NI varchar(20) MASKED WITH (FUNCTION = 'partial(3,"X*XXXXX",2)') NULL,

       salary money MASKED WITH (FUNCTION = 'default()') NULL,

       Age int MASKED WITH (FUNCTION = 'default()') NULL);

       go

3.       Insert rows

INSERT customers (FirstName, LastName, Phone, EMail, NI, salary, Age) VALUES

('John', 'Miller', '02079458879', 'John.miller@vic.com', 'PN7383736A', 40000, 20),

('David', 'James', '02089458879', 'djames@djames.com', 'PN9364791B', 50000, 30),

('Yared', 'Allem', '01219348765', 'y.alem@ethionet.net', 'PN7369581C', 60000, 40),

('Genet', 'Mena', '08459297865', 'Menag@bale.co.uk', 'PN8383963D', 70000, 50);

4.       Create non-privileged user

CREATE USER SupportUser WITHOUT LOGIN;

5.       Grant select on customers table to non-privileged user

GRANT SELECT ON customers TO SupportUser;

6.       Execute as supportuser

exec as user = 'SupportUser'

go

select * from customers

REVERT;

go

7.       Execute as privileged user

SELECT * FROM customers

 


So, Looking at the output FirstName, salary, NI and age columns use  partial function, phone column uses default function and email column uses email function to mask the data.