SQL- Dynamic Masking - MASKED WITH (FUNCTION

 Dynamic Masking

--Creating table

CREATE TABLE emp 

(

id INTEGER IDENTITY(1,1) PRIMARY KEY,

emp_name VARCHAR(100),

Postcode INTEGER, 

emp_email VARCHAR(100),

emp_phone VARCHAR(100),

emp_sal MONEY

)

--Inserting the data before altering  the columns

INSERT INTO emp(emp_name,Postcode,emp_email,emp_phone,emp_sal)

SELECT 'abc','500098','abc@gmail.com','+918008832484',1000000 UNION ALL

SELECT 'bcd','500038','bcd@gmail.com','+918008832484',2000000 UNION ALL

SELECT 'fgh','500038','fgh@gmail.com','+918008832484',2000000

--Verify the data by selecting 

SELECT *  FROM emp

-- ALerting t he colums for dynamic masking 

ALTER TABLE emp

ALTER COLUMN Postcode ADD MASKED WITH (FUNCTION = 'default()')

ALTER TABLE emp

ALTER COLUMN emp_email ADD MASKED WITH (FUNCTION = 'email()')

ALTER TABLE emp

ALTER COLUMN emp_phone ADD MASKED WITH (FUNCTION = 'partial(5,"XXXXXXX",2)')

ALTER TABLE emp

ALTER COLUMN emp_sal ADD MASKED WITH (FUNCTION = 'random(100,200)')

-- Create the login for testing  with login 

USE master 

GO

CREATE USER DynamicMaskUser WITHOUT LOGIN; 

--Giving the permissions

USE DynamicMasktest

GO

GRANT SELECT ON emp TO DynamicMaskUser;

-- Open another session and check the dynamic masking working or not

EXECUTE AS USER = 'DynamicMaskUser'

SELECT * FROM emp

REVERT

GO

--open the previous session and revoke the permissions

--If some user already has unmask permission, you can revoke this permission:

SELECT * FROM emp

REVOKE UNMASK TO DynamicMaskUser 

-check the dynamic masked columns

SELECT * FROM sys.mask