In release of SQL Server 2016 CTP 2.0 another new feature that is released is Dynamic Data Masking. Using Dynamic data masking we can provide the security to our restrictive data by masking the data from viewing to non-authorized users.
For example, we have credit card information in our table but we don’t want non-privileged users to see the information as a security concerns. In this case we can mask the data of credit card so that we can prevent non-privileged users from viewing the data which may access the data for troubleshooting purpose.
To enable the data masking we need to enable trace flags 209 and 219. Execute the following syntax to enable the data masking:
There are 3 types of masking:
- Use zero for numeric data types.
- Use xxxx for string data types.
- Use 01.01.2000 for date and time data types.
Example: Age INT MASKED WITH (FUNCTION = ‘default()’)
Uses the first and last letter and added a custom padded string in the middle.
Example: PhoneNumber varchar(10) MASKED WITH (FUNCTION = partial(5,”xxxx”,0)
Example: Email varchar(100) MASKED WITH (FUNCTION = ‘email()’)
Let’s take an example to understand this better. First I will create a table and insert some data into it, later on which I will applied the masking.
Create table CardInfo ( CardInfoId INT IDENTITY(1,1) PRIMARY KEY, FirstName varchar(100), LastName varchar(100), CardNumber varchar(100), PhoneNumber varchar(20), Email varchar(100) ) GO INSERT INTO CardInfo(FirstName, LastName, CardNumber, PhoneNumber, Email) SELECT 'Kapil', 'Singh', '1234-5678-2345-6789','123456789','firstname.lastname@example.org' UNION ALL SELECT 'Aditya','Vyas','6789-1234-3456-5678','987654321','email@example.com'
We can apply the masking to the columns to the table definition at the time creation of table or we can apply using ALTER statement. Here we will apply the masking using ALTER statement like this:
ALTER TABLE CardInfo ALTER COLUMN PhoneNumber ADD MASKED WITH (FUNCTION ='default()') GO ALTER TABLE CardInfo ALTER COLUMN CardNumber ADD MASKED WITH (FUNCTION ='partial(2,"xxx",2)') GO ALTER TABLE CardInfo ALTER COLUMN Email ADD MASKED WITH (FUNCTION ='email()') GO
Here I have applied masking to the columns PhoneNumber, CardNumber and Email columns. After this I will create a new user ‘TestUser’ which will have only SELECT permission.
CREATE USER TestUser WITHOUT LOGIN; GRANT SELECT ON CardInfo TO TestUser
Now I will the below script and see that masking is applied to the newly create user ‘TestUser’.
SELECT * FROM CardInfo GO EXECUTE AS USER = 'TestUser' SELECT * FROM CardInfo REVERT;
In output we can see that columns CardNumber, PhoneNumber and Email information is not visible to the user ‘TestUser’.
So datamasking is very helpful in scenarios related to security where we have some confidential information and that information should not be visible to un-authorized person.
We can also unmask the data which I will cover in my next blogs.
Have a happy learning!!
Kapil Singh Kumawat