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’,‘billy@fakemail.com’), (‘Hughie’,‘Campbell’,799,‘123-45-6789’,‘1234-5678-9101-1121’,‘hughie@fakemail.com’), (‘Mothers’,‘Milk’,799,‘123-45-6789’,‘1234-5678-9101-1121’,‘mm@fakemail.com’) ###fetching rows select * from SensitiveData
You can see currently there is no masking enabled.
###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).
###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;
