BLOGS

Dynamic Data Masking in SQL Server 2016

It is one of the security feature available in SQL Server 2016. This is applicable in SQL database in Azure as well.

It enables us to handle the sensitive data very securely. By implementing dynamic data masking, we can restrict the users not to see the sensitive data. It will add a mask to the data.

It is easy to implement in new and existing applications. Since it is implementing on database layer, there won’t be any changes required in application.

Let us discuss below topics,

1. Create a table with dynamic data masking

2. Alter or Modify the table to add data masking

3. Granting Permission to Unmask

4. Dropping Data mask

5. Limitations

6. Query Masked columns

 

1. Create a table with dynamic data masking

CREATE TABLE Users

  (UserID int IDENTITY(1,1) PRIMARY KEY,

   FirstName varchar(100) MASKED WITH (FUNCTION = ‘partial(1,”XXXXXXX”,0)’) NULL,

   LastName varchar(100) NOT NULL,

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

   Email varchar(100) MASKED WITH (FUNCTION = ’email()’) NULL);

Insert data into the table

INSERT into Users (FirstName, LastName, Phone, Email) VALUES

(‘Hari’, ‘Haran’, ‘989436878’, ‘rhariharaneee@gmail.com’),

(‘Hariharan’, ‘Rajendran’, ‘9545679781’, ‘test@test.com’),

(‘Raj’, ‘Kumar’, ‘888978562’, ‘sales@test.com’);

SELECT * FROM USERS

Create a user and check the data,

CREATE USER User1 WITHOUT LOGIN;

GRANT SELECT ON Users TO User1;

 

EXECUTE AS USER = ‘User1’;

SELECT * FROM Users;

REVERT;

2. Alter or Modify the table to add data masking

create a table without masking function as like below,

CREATE TABLE Users_Info

  (UserID int IDENTITY(1,1) PRIMARY KEY,

   FirstName varchar(100)  NULL,

   LastName varchar(100) NOT NULL,

   Phone varchar(12)  NULL,

   Email varchar(100)  NULL);

 

INSERT into Users_info (FirstName, LastName, Phone, Email) VALUES

(‘Hari’, ‘Haran’, ‘989436878’, ‘rhariharaneee@gmail.com’),

(‘Hariharan’, ‘Rajendran’, ‘9545679781’, ‘test@test.com’),

(‘Raj’, ‘Kumar’, ‘888978562’, ‘sales@test.com’);

 

SELECT TOP 10 * FROM Users_Info;

Modify the table to add data mask function,

ALTER TABLE Users_Info

ALTER COLUMN FirstName ADD MASKED WITH (FUNCTION = ‘default()’);

ALTER TABLE Users_Info

ALTER COLUMN Phone ADD MASKED WITH (FUNCTION = ‘partial(1,”XXXXXXX”,0)’);

ALTER TABLE Users_Info

ALTER COLUMN Email ADD MASKED WITH (FUNCTION = ’email()’)

 

Check the result with different user

EXECUTE AS USER = ‘User2’;

SELECT * FROM Users_info;

3. Granting Permission to Unmask

GRANT UNMASK TO User1;

EXECUTE AS USER = ‘User1’;

SELECT * FROM Users;

REVERT;

4.Dropping Data Mask

ALTER TABLE Users

ALTER COLUMN FirstName DROP MASKED;

 

5.Limitations

Masking rule is not supported for below data type columns,

Encrypted Columns

FILESTREAM

COLUMN_SET

 

6.Query Masked Columns

It is using “sys.masked_columns” & “sys.tables” system tables.

SELECT c.name, tbl.name as table_name, c.is_masked, c.masking_function

FROM sys.masked_columns AS c

JOIN sys.tables AS tbl

    ON c.[object_id] = tbl.[object_id]

WHERE is_masked = 1;