Dynamic database masking (DDM) is used to secure the sensitive data exposure to the non-privileged user by masking it. It is used in-order to obfuscate the data.
Dynamic data masking helps prevent unauthorized access to sensitive data by enabling the user to select how much of the sensitive data to expose with minimal impact on the application layer. It is one type of security that’s why this feature is known as data protection feature. DDM is easy to use in the existing applications since masking rules are applied in the query results.
For example, a call center support person may identify callers by several digits of their social security number or credit card number, but those data items should not be fully exposed to the support person. A masking rule can be defined that masks all but the last four digits of any social security number or credit card number in the result set of any query.
I will demonstrate this feature more in details with sample queries and required screenshots. But before let’s review its main points.
Main features:
· A central data masking policy acts directly on sensitive fields in the database.
· Designate privileged users or roles that do have access to the sensitive data.
· DDM features full masking and partial masking functions, as well as a random mask for numeric data.
· Simple Transact-SQL commands define and manage MASKS.
Different Types of Masks:
Function
|
Description
|
Default
|
The default mask masks the full data according to the assigned datatype.
• For string types (char, nchar, varchar, nvarchar, text) it replaces the data with XXXX or fewer Xs, in a case the size of the field is less than 4. • For numeric datatypes (bigint, bit, decimal, int, money, numeric, smallint, smallmoney, tinyint, float, real) it uses 0 value. • For binary datatypes (binary, varbinary, image) it uses a single byte of binary value 0. |
Email
|
Masking method which exposes the first letter of an email address and the constant suffix ".com", in the form of an email address. [email protected].
|
Random
|
A random masking function for use on any numeric type to mask the original value with a random value within a specified range.
|
Custom String
|
Masking method which exposes the first and last letters and adds a custom padding string in the middle. prefix, [padding], suffix
Note: If the original value is too short to complete the entire mask, part of the prefix or suffix will not be exposed. |
Applying Masks to fields:
I am using SQL Server Management Studio with SQL Server 2016 for demonstrating it. I am logged in with administrative privileges.
First, creating a sample table with some data.
CREATE TABLE [dbo].[EmpInfo](
[Id] [int] IDENTITY(1,1) NOT NULL,
[EmpCode] [int] NULL,
[FirstName] [nvarchar](100) NULL,
[LastName] [nvarchar](100) NULL,
[EmailAddress] [nvarchar](100) NULL,
[Phone] [nvarchar](20) NULL,
CONSTRAINT [PK_EmpInfo] PRIMARY KEY CLUSTERED
(
[Id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
SET IDENTITY_INSERT [dbo].[EmpInfo] ON
GO
INSERT [dbo].[EmpInfo] ([Id], [EmpCode], [FirstName], [LastName], [EmailAddress], [Phone]) VALUES (1, 1001, N'Harsh', N'Gupta', N'[email protected]', N'213-245-2564')
GO
INSERT [dbo].[EmpInfo] ([Id], [EmpCode], [FirstName], [LastName], [EmailAddress], [Phone]) VALUES (2, 1002, N'Jason', N'Murray', N'[email protected]', N'235-256-2465')
GO
INSERT [dbo].[EmpInfo] ([Id], [EmpCode], [FirstName], [LastName], [EmailAddress], [Phone]) VALUES (3, 1003, N'Ravinder', N'Singh', N'[email protected]', N'123-564-5896')
GO
INSERT [dbo].[EmpInfo] ([Id], [EmpCode], [FirstName], [LastName], [EmailAddress], [Phone]) VALUES (4, 1004, N'Nirav', N'Masuria', N'[email protected]', N'123-456-4567')
GO
INSERT [dbo].[EmpInfo] ([Id], [EmpCode], [FirstName], [LastName], [EmailAddress], [Phone]) VALUES (5, 1005, N'Vikram', N'Kumar', N'[email protected]', N'654-456-4569')
GO
INSERT [dbo].[EmpInfo] ([Id], [EmpCode], [FirstName], [LastName], [EmailAddress], [Phone]) VALUES (6, 1006, N'Ankit', N'Gupta', N'[email protected]', N'789-123-6543')
GO
INSERT [dbo].[EmpInfo] ([Id], [EmpCode], [FirstName], [LastName], [EmailAddress], [Phone]) VALUES (7, 1007, N'Nikunj', N'Varu', N'[email protected]', N'123-456-9876')
GO
INSERT [dbo].[EmpInfo] ([Id], [EmpCode], [FirstName], [LastName], [EmailAddress], [Phone]) VALUES (8, 1008, N'Ava', N'Ranara', N'[email protected]', N'478-256-9856')
GO
INSERT [dbo].[EmpInfo] ([Id], [EmpCode], [FirstName], [LastName], [EmailAddress], [Phone]) VALUES (9, 1009, N'Cathy', N'Va', N'[email protected]', N'456-789-6876')
GO
SET IDENTITY_INSERT [dbo].[EmpInfo] OFF
GO
Simple executing the select query.
SELECT * FROM EmpInfo
Now we are implementing the masking on the columns. Below are the syntaxes of altering the table and apply the MASKS.
1. Default:
a. ALTER table EmpInfo alter column FirstName add MASKED WITH (FUNCTION='default()')
2. Email:
a. ALTER table EmpInfo alter column EmailAddress add MASKED WITH (FUNCTION='email()')
3. Random:
a. ALTER table EmpInfo alter column EmpCode add MASKED WITH (FUNCTION='random(100,150)')
4. Custom String:
a. ALTER table EmpInfo alter column Phone add MASKED WITH (FUNCTION='partial(0,"XXX-XXX-XX",2)')
Masking syntax while creating table:
CREATE TABLE [dbo].[EmpInfo](
[Id] [int] IDENTITY(1,1) Primary Key NOT NULL,
[EmpCode] [int] MASKED WITH (FUNCTION='random(100,150)') NULL,
[FirstName] [nvarchar](100) MASKED WITH (FUNCTION='default()') NULL,
[LastName] [nvarchar](100) NULL,
[EmailAddress] [nvarchar](100) MASKED WITH (FUNCTION='email()') NULL,
[Phone] [nvarchar](20) MASKED WITH (FUNCTION='partial(0,"XXX-XXX-XX",2)') NULL,
)
Creating User with required Permission:
We do not need any special permission to create a table with a dynamic data mask, only the standard CREATE TABLE and ALTER on schema permissions.
Users with SELECT permission on a table can view the table data. Columns that are defined as masked, will display the masked data. Grant the UNMASK permission to a user to enable them to retrieve unmasked data from the columns for which masking is defined.
We are going to create a user without login and then grant access to select only on example table. You can grant on all or some table as per your requirements.
CREATE USER MaskedUser WITHOUT LOGIN;
GRANT SELECT ON dbo.EmpInfo TO MaskedUser
EXECUTE AS USER = 'MaskedUser'
SELECT * FROM dbo.EmpInfo
Use REVERT to revert to the main user.
Drop Masks:
It is easy to remove the Mask from column
ALTER TABLE EmpInfo ALTER COLUMN FirstName DROP MASKED
Allow and Revoke Users to see unmasked data:
Admin users always see the original unmasked data because they are privileged. You can allow an unprivileged user to see the unmasked data if it is a business need.
GRANT UNMASK to MaskedUser
You can revoke the unmasked access if no longer need to see the unmasked data.
REVOKE UNMASK TO MaskedUser
Execution Plan:
If we check the execution plan of both queries (with and without MASK) then we will identify that there is no difference. So, MASK doesn’t affect the performance of the query. Use SHOWPLAN to assign permission for the execution plan.
Grant SHOWPLAN to MaskedUser
Querying for Masked Columns:
Use the sys.masked_columns view to query for table and columns that have a masking function applied to them. This view inherits from the sys.columns view. It returns all columns in the sys.columns view, plus the is_masked and masking_function columns, indicating if the column is masked, and if so, what masking function is defined. This view only shows the columns on which there is a masking function applied.
SELECT c.name, tbl.name as table_name, c.is_masked, c.masking_function
FROM sys.masked_columns AS c
JOIN sys.tables AS tbl
ON c.[object_id] = tbl.[object_id]
WHERE is_masked = 1;
Data Masking on Azure:
Above we learned about the data Mask, how to enable DDM in an existing application to restrict the user to access the secure information. The main advantage of data masking is that we don’t need to make any changes in our application. We just must implement it in the database.
Above the same method (using queries) we can create the MASK on Azure SQL server, but on Azure portal, we can create MASK by just a few clicks also.
Here are the steps with screenshots.
Step 1: Open the database on Azure and select Dynamin Database Masking option under security.
Step 2: Click on Add Mask on the right side window. It will open a window where you can define the masking rule.
After selecting the fields, click on add button and it will create a Mask.
Conclusion:
In the above article, we learned about data masking, how to enable it in existing applications. The main advantage of data masking is that we don’t need to make any changes in our application. We just must implement it in the database.
