Dynamic data masking in SQL Server

Dynamic data masking lets you mask data in a column from the view of the user. when they use the column in a SELECT statement, instead of showing them the actual data, it masks it from their view.                             

Ex: if you have a table that has email addresses, you might want to mask the data so most users can’t see the actual data when they are querying the data

There are four types of data mask functions that we can apply:

Default Takes the default mask of the data type (not of the DEFAULT constraint of the column, but the data type.

Email Masks the email so you only see a few meaningful characters.

Random Masks any of the numeric data types (int, smallint, decimal, etc) with a random value within a range.

Partial Allows you to take values from the front and back of a value, replacing the center with a fixed string value.

Now we try to understand this an example.

CREATE TABLE DataMasking

(

FirstName nvarchar(50) NULL,

LastName nvarchar(50) NOT NULL,

MobileNo nvarchar(10) NOT NULL,

Status varchar(10),

EmailAddress nvarchar(50) NULL,

DOB date NOT NULL,

VihicleCnt tinyint NOT NULL

);

INSERT INTO DataMasking(FirstName,LastName,MobileNo, Status,EmailAddress, DOB,VihicleCnt)

VALUES('Rohit','Kumar','9988811122','Active','rohit@gmail.com','1979-01-12',3),

('Arya','Sharma','9978813322','InActive','arya.sharma007@gmail.com','1985-06-12',1),

('Indy','Bhaskar','9968548266','Active',NULL, '1959-03-30', 2),

('Arpita','Mishra','9868577266','Active','arpita.mishra@yahoo.com', '1990-03-30', 0);

 

select * from DataMasking;

The dbo user always has this right, so to test this, we create a different user to use after applying the masking. The user must have rights to SELECT data from the table.

CREATE USER Otheruser WITHOUT LOGIN;

GRANT SELECT ON DataMasking TO Otheruser;

EXECUTE AS USER = 'Otheruser';

Now lets understand with each type of datamasking 

Note: All the alter commands below should be executed under dbo user.

Default()

ALTER TABLE DataMasking ALTER COLUMN FirstName

ADD MASKED WITH (FUNCTION = 'default()');

ALTER TABLE DataMasking ALTER COLUMN DOB

ADD MASKED WITH (FUNCTION = 'default()');

 select * from DataMasking;

Note : when you use a default that the default value isn’t used for calculations

Email()

ALTER TABLE DataMasking ALTER COLUMN EmailAddress

ADD MASKED WITH (FUNCTION = 'email()');

Partial()

The partial() function is by far the most powerful. It let’s you take the number of characters from the front and the back of the string

ALTER TABLE DataMasking ALTER COLUMN MobileNo

ADD MASKED WITH (FUNCTION = 'partial(2,"*******",1)');

 

ALTER TABLE DataMasking ALTER COLUMN LastName

ADD MASKED WITH (FUNCTION = 'partial(3,"_____",2)');

 

ALTER TABLE DataMasking ALTER COLUMN Status

ADD MASKED WITH (Function = 'partial(0,"Unknown",0)');

Random()

ALTER TABLE DataMasking ALTER COLUMN VihicleCnt

ADD MASKED WITH (FUNCTION = 'random(7,15)');

Run the statement multiple times and you will see the VihicleCnt value changing multiple times

Finally run below

REVERT; SELECT USER_NAME();