Dynamic Data Masking

Dynamic Data Masking can be used to mask sensitive data for testing and different purpose, It doesn't encrypt the data, It can be controlled from the database level ,Basically DBA can implement the solution with out much complexity and accessed easily by users who has access to see the data

We have different types of data masking techniques used in DDM

1.Deafult Masking

2.Random Masking

3.Partial Masking

1.Default Masking –

We can include default function while creating table  or we can alter table to enable default data masking

ALTER TABLE [Person].[Address]

ALTER COLUMN PostalCode nvarchar(15)

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

Creating user to test

USE Adventureworks;

  GO

  CREATE USER user1 WITHOUT LOGIN;

  GRANT SELECT ON OBJECT:: Person].[Address] TO test;  

  GO

EXECUTE AS USER = 'test';

         select top 10 * from  [Person].[Address]

         revert;

Output will column is totally masked as below

2.Random Masking-

Column will be randomly masked and sql server will take care of this completely if enable random masking and to enable we can use statement as below

MASKED WITH (FUNCTION = 'random(1, 5)') NOT NULL;

3. Partial Masking-

We can partially mask the column according the requirement and we can show first few characters or last few according to the requirement and we can create partial masking as below

ALTER TABLE [Person].[Address]

  ALTER COLUMN PostalCode nvarchar(15)

    MASKED WITH (FUNCTION = 'partial(1, "xxxx", 2)') NOT NULL;

The output of the above statement  in PostalCode column as we can see first character as we passed 1 in the parameter and everything in middle is masked and we are passing 2 display last characters in the column

To see what type of masking is enabled in the column we can query database using below TSQL

 SELECT OBJECT_NAME(object_id) TableName, 

    name ColumnName, 

    masking_function MaskFunction

  FROM sys.masked_columns

  ORDER BY TableName, ColumnName; 

 

Permissions

We can grant permission to users as below

GRANT UNMASK TO test;

  GO

And revoke the permissions to users as below

REVOKE UNMASK TO test;  

GO

We can see the result as below