Menu Close

How to enable Dynamic Data Masking in SQL Server

Dynamic data masking or DDM was first introduced in SQL Server 2016, which limits sensitive data exposure by masking it to non-privileged users. It is security feature which can be used to greatly secure your application. Please note that DDM is different from data encryption and should not be used as a primary security layer because in DDM data is actually not masked physically in the database. Rather, the data is masked in the query result based on user’s privilege.

Masking can be achievable at the time of table creation or after table creation. In this demo, I will create a table without masking and will enable different type of masking as we proceed, there are 4 types of masking currently available as per official documentation.

1. Create table and add rows.

create table SensitiveData
(
FirstName varchar(20),
LastName varchar(20),
CreditRating int,
AadhaarNo varchar(12),
CreditCard varchar(19),
Email varchar (30)
)

###Adding Rows

insert SensitiveData
values
(‘Billy’,‘Bucther’,799,‘123-45-6789’,‘1234-5678-9101-1121’,‘[email protected]’),
(‘Hughie’,‘Campbell’,799,‘123-45-6789’,‘1234-5678-9101-1121’,‘[email protected]’),
(‘Mothers’,‘Milk’,799,‘123-45-6789’,‘1234-5678-9101-1121’,‘[email protected]’)

###fetching rows

select * from SensitiveData

You can see currently there is no masking enabled.

2. Enable data masking on “SensitiveData” table. I have masked each column separately but can be masked in together also.
###Masking Credit Rating Col
alter table SensitiveData
alter column CreditRating ADD MASKED
with (function='default()')
###Masking AadhaarNo Col
alter table SensitiveData
alter column AadhaarNo ADD MASKED
with (function=‘partial(0,”XXX-XX-“,4)’);

###Masking Email Col
alter table SensitiveData
alter column Email ADD MASKED
with (function=’email()’);

###Masking CreditCard Col
alter table SensitiveData
alter column CreditCard ADD MASKED
with (function=‘partial(0,”CCCC-CCCC-CCCC-C”,3)’);

Now when we query the table we can still see the column values unmasked, that is because we are currently logged in as a privileged user (SYSUSER in my case).

3. Create a non privileged user with select access on “SensitiveData” table and verify masking.
###Created user with select grant.
create user theboys without login
grant select on SensitiveData to theboys;

###Query the table as non–privileged user to verify DDM

execute as user=‘theboys’
select * from SensitiveData
revert;
Similarly you can mask any data to add on additional security layer for you application.
~thankyou for reading

Related Posts

Leave a Reply

Your email address will not be published. Required fields are marked *