SQL - Dynamic Data Masking

 Dynamic Data Masking helps to limit exposure of sensitive data to unprivileged users.

It can be easily implemented along with other security measures like auditing, encryption and row level security.

Dynamic Data Masking only masks the sensitive data. Users who do not have the appropriate access will be able to view only the masked data.

It does not change the original data in the database.

Lets learn how this can be implemented in SQL Server.

We are going to work with the Memberships sample data -

CREATE TABLE Membership (

    MemberID        int IDENTITY(1,1) NOT NULL PRIMARY KEY CLUSTERED,

    FirstName        varchar(100) NULL,

    LastName        varchar(100) NOT NULL,

    Phone            varchar(12) NULL,

    Email            varchar(100) NOT NULL,

    DiscountCode    smallint NULL

);

INSERT INTO Membership (FirstName, LastName, Phone, Email, DiscountCode)

VALUES   

('Roberto', 'Tamburello', '555.123.4567', 'RTamburello@contoso.com', 10),  

('Janice', 'Galvin', '555.123.4568', 'JGalvin@contoso.com.co', 5),  

('Shakti', 'Menon', '555.123.4570', 'SMenon@contoso.net', 50),  

('Zheng', 'Mu', '555.123.4569', 'ZMu@contoso.net', 40); 

We are going to mask separate columns. Since the columns belong to an existing table, we will use the Alter Table statements.

Select * from Membership;

To mask a particular column, use ADD MASKED WITH (FUNCTION = '#option#');

Masking Rules -

Alter Table Membership

Alter Column FirstName ADD MASKED WITH (FUNCTION = 'default()');

Alter Table Membership

Alter Column DiscountCode ADD MASKED WITH (FUNCTION = 'default()');

To change the mask type of an already masked column , use the below syntax - 

Alter Table Membership

Alter Column DiscountCode smallint MASKED WITH (FUNCTION = 'random(20,30)');

To remove a mask on a column, execute the query as below -

Alter Table Membership

Alter Column DiscountCode DROP MASKED;

Alter Table Membership

Alter Column DOB ADD MASKED WITH (FUNCTION = 'default()');

Alter Table Membership

Alter Column Phone ADD MASKED WITH (FUNCTION = 'partial(2,"X.XXX.XX",1)');

Alter Table Membership

Alter Column CCNumber ADD MASKED WITH (FUNCTION = 'partial(0,"XXXXXXXX",4)');

Alter Table Membership

Alter Column Email ADD MASKED WITH (FUNCTION = 'email()');

Create a Test User with only Select Access on the schema -

Create User TESTUSER WITHOUT LOGIN;

GRANT SELECT on SCHEMA::dbo to TESTUSER;

EXECUTE AS USER = 'TESTUSER';

Select * from Membership;

Grant Unmask access to this user to enable him to view all original data -

GRANT UNMASK to TESTUSER;

Learn More

For any questions, reach out to learn@knowstar.org.

Consider expanding your expertise with these certifications:

• Google Data Analytics Professional Certificate: https://imp.i384100.net/OR37oQ
• Google Advanced Data Analytics Professional Certificate: https://imp.i384100.net/eK1WmQ

• Best SQL and Data Analytics Books

• T-SQL Fundamentals (By Itzik Ben-Gan) - https://amzn.to/4koKGdC
• Ace the Data Science Interview - https://amzn.to/3D2ne5n