SETUP ALWAYS ON AVAILABILITY GROUP
How Always on Availability helps disaster recovery for critical user databases.
Hiding the sensitive information is more important for any organization. In an environment where different level people and different working nature will be joined together to achieve their goals.
Even though they are working together, hiding their sensitive information from one another important.
For example, HR department will be hiding Employee details such as Aadhar number, Email ID, Address from Finance team. Both will share some common object.
On Banking, Customer support executive is to resolve the customer query for that, he or she should have details about basic details and sensitive information of customer such as Debit card. Those partially are masked from their view.
For masking or hiding the important details SQL server 2016 has four option:
1. Default()
This Function mask the sensitive data using the data type that has been defined for the column.
For char it uses ‘X’ and int uses ‘0’ (eg. Name Robert will be displayed xxxx and Salary 1,00000 will shown as 0.00)
2. Email()
This function particularly to mask the Email ID’s
Eg. Email ID robert@geopits.com will be displayed as rxxxx@xxx.com
3. Random()
It is to mask the numeric data to random value. It can be used only with numeric data.
(eg. Employee_reward 5000 will be shown as 0746)
4. Partial()
This function provide us option to mask portion which we required, it has the parameter prefix suffix and padding.
Eg. Aadhar card number 555588886666 will be displayed as xx-xx-6666.
How Masking Work?
At first it checks the user account’s unmask privilege, if it has will not apply mask function on that information or else it will be masked. (sysadmin and dbowner by default has that privileges)
During the select operation, it collects the data and mask function will be applied on it. Due to this there may be some performance slow down.
Demo:
I am creating a table called Employee with defining the mask function.
USE [Geopits]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[Employee_details](
[ID] [int] NOT NULL,
[FirstName] [varchar](32) NOT NULL,
[DateOfBirth] [date] MASKED WITH (FUNCTION = ‘default()’),
[Aadhar Number] [char](12) MASKED WITH (FUNCTION = ‘partial(0, “xx-xx-”, 4)’) ,
[Email] [nvarchar](255) MASKED WITH (FUNCTION = ‘email()’),
[Salary] [money] MASKED WITH (FUNCTION = ‘Random(1,9)’)
) ON [PRIMARY]
GO
Inserting the new rows
INSERT [dbo].[Employee_details] ([ID], [FirstName], [DateOfBirth], [Aadhar Number], [Email], [salary]) VALUES (1, N’Gorge’, CAST(N’1980–01–01' AS Date), N’123456780674', N’ Gorge.s@geopits.com ‘, 10000.0000)
GO
INSERT [dbo]. [Employee_details] ([ID], [FirstName], [DateOfBirth], [Aadhar Number], [Email], [salary]) VALUES (1, N’Shakthi’, CAST(N’1980–01–01' AS Date), N’555588886666', N’Shakthi.v@geopits.com’, 10000.0000)
GO
Creating user with only select permission on the Employee_details
Use geopits
Create user Daniel without login;
Grant select on dbo.employee_details to Daniel;
Executing the select statement by using Daniel account
Execute as user = ‘Daniel’
Select top 2 * from dbo.employee_details
Revert;
Execute the below statement to make Daniel to view masked value
Grant unmask to Daniel;
Executing the select statement after given unmask privileges
Execute as user = ‘Daniel’
Select top 2 * from dbo.employee_details
Revert;
Restriction and Limitations On Dynamic Data Masking
It cannot be applied if the Encrypted columns (eg.Always Encrypted)
FILESTREAM is to store data in type of pdf, image. It cannot be applied on FILESTREAM.
It cannot prevent the user from modifying the value, even though they restricted from view the masked value.
Computed column cannot be masked.
Pros and Cons
Further reading:
If you found this article helpful, you might want to check out these related resources,
