This is one of the cool feature that introduced in SQL Server 2016, which can enable DBAs to mask data while database is in test\QA stage for testing. It limits the data exposure to the developers, testers and other unintended users, though it has it limitations and restrictions.
Types of Masking:
Types of Masking:
Default Masking: Also
known as full masking according to the data types of the designated
fields.
- For char, nchar, varchar, nvarchar, text, ntext uses XXXX or Fewer Xs depending on the field size.
- For Date types uses 01.01.1900 00:00:00.0000000.
- For bigint, bit, decimal, int, money, numeric, smallint, smallmoney, tinyint, float, real uses a zero value.
- For Binary varbinary and image uses a Single byte of ASCII value 0.
Alter syntax: ALTER COLUMN
Gender ADD MASKED WITH (FUNCTION = 'default()')
Email Masking: This
masking method which exposes the first letter of an email address and the
constant suffix ".com", in the form of an email address.
e.g.: It Shows email addresses like aXXX@XXXX.com.
Syntax: Email varchar(100) MASKED WITH
(FUNCTION = 'email()') NULL
Alter syntax: ALTER COLUMN Email
ADD MASKED WITH (FUNCTION = 'email()')
Random Masking: This
masking function can be used on any numeric type to mask the original value
with a random value within a specified range.
Syntax: Account_Number bigint MASKED WITH
(FUNCTION = 'random([start range], [end range])')
Alter syntax: ALTER COLUMN
[Month] ADD MASKED WITH (FUNCTION = 'random(1, 12)')
Custom Masking: This
masking method which exposes the first and last letters and adds a custom
padding string in the middle.
Syntax: FirstName varchar(100) MASKED WITH
(FUNCTION = 'partial(prefix,[padding],suffix)') NULL
ALTER COLUMN [Phone Number]
ADD MASKED WITH (FUNCTION = 'partial(1,"XXXXXXX",0)')
Masking Hands On:
-- Create a Demo database for Masking
CREATE DATABASE [Masking]
GO
-- Create a Demo table for masking
USE [Masking]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[Masking_Demo](
[ID] [int] NOT NULL,
[Name] [nvarchar](10) NULL,
[DateOfBirth] [date] NULL,
[AccNum] [numeric](18, 0) NULL,
[Gender] [varchar](50) NULL,
[EMailID] VARCHAR(50) NULL,
[City] [nchar](10) NULL,
[State] [nchar](10) NULL
) ON [PRIMARY]
GO
-- Inserting data into the above table
INSERT INTO dbo.Masking_Demo
( ID ,Name ,DateOfBirth ,AccNum ,Gender ,EMailID , City ,State)
VALUES ( '1','Sri','1987-2-2','757676567','Male','Sri.anu@soandso.com', 'Florence','KY'),
( '2','Jer','1967-3-3','764900097','Male', 'Jer.cole@soandso.com', 'Hamilton','OH'),
( '3','Don','1990-4-4','780090909','Male','Don.sl@soandso.com', 'Newyork','NY')
-- Select Data from above table
SELECT * FROM [Masking].[dbo].[Masking_Demo]
Apply Masking to above table:
-- Create a Demo database for Masking -- Alter table to mask each column with different masking functions SET ANSI_PADDING OFF GO Alter Table [Masking].[Masking_Demo] ALTER COLUMN [ID] ADD MASKED WITH (FUNCTION = 'default()') Alter Table [Masking].[Masking_Demo] ALTER COLUMN [Name] ADD MASKED WITH (FUNCTION = 'default()') Alter Table [Masking].[Masking_Demo] ALTER COLUMN [DateOfBirth] ADD MASKED WITH (FUNCTION = 'default()') Alter Table [Masking].[Masking_Demo] ALTER COLUMN [AccNum] ADD MASKED WITH (FUNCTION = 'random(1, 12000)') Alter Table [Masking].[Masking_Demo] ALTER COLUMN [Gender] ADD MASKED WITH (FUNCTION = 'default()') Alter Table [Masking].[Masking_Demo] ALTER COLUMN [EMailID] ADD MASKED WITH (FUNCTION = 'email()') Alter Table [Masking].[Masking_Demo] ALTER COLUMN [City] ADD MASKED WITH (FUNCTION = 'partial(3,"XXXX",2)') Alter Table [Masking].[Masking_Demo] ALTER COLUMN [State] ADD MASKED WITH (FUNCTION = 'partial(1,"XXXXXXX",0)') -- Select Data from table after masking SELECT * FROM [Masking].[dbo].[Masking_Demo]
Permissions:
- Permissions required to create table with dynamic data mask are: CREATE TABLE and ALTER schema.
- Adding, replacing, or removing the mask of a column, requires the ALTER ANY MASK permission and ALTER permission on the table.
- SELECT permission on a table will view masked data only.
- UNMASK permission are needed to retrieve unmasked data from the columns for which masking is defined. e.g.: GRANT UNMASK TO
- The CONTROL permission on the database includes both the ALTER ANY MASK and UNMASK permission.
Best Practices and Common Use Cases:
- Creating a mask on a column does not prevent updates to that column. So users receive masked data when querying the masked column, the same users can update the data if they have write permissions. A proper access control policy should still be used to limit update permissions.
- Using SELECT INTO or INSERT INTO to copy data from a masked column into another table results in masked data in the target table.
- Dynamic Data Masking is applied when running SQL Server Import and Export. A database containing masked columns will result in a backup file with masked data (assuming it is exported by a user without UNMASK privileges), and the imported database will contain statically masked data
Limitations and Restrictions:
A masking rule cannot be defined for the following column types:
- Encrypted columns (Always Encrypted)
- FILESTREAM
- COLUMN_SET or a sparse column that is part of a column set.
- A mask cannot be configured on a computed column, but if the computed column depends on a column with a MASK, then the computed column will return masked data.
- A column with data masking cannot be a key for a FULLTEXT index.
- For users without the UNMASK permission, the deprecated READTEXT, UPDATETEXT, and WRITETEXT statements do not function properly on a column configured for Dynamic Data Masking.
Security Note:
Bypassing masking using inference or brute-force techniques:
Dynamic Data Masking is designed to simplify application development by limiting data exposure in a set of
pre-defined queries used by the application. While Dynamic Data Masking can
also be useful to prevent accidental exposure of sensitive data when accessing
a production database directly, it is important to note that unprivileged users
with ad-hoc query permissions can apply techniques to gain access to the actual
data. If there is a need to grant such ad-hoc access, Auditing should be used
to monitor all database activity and mitigate this scenario.
Note to DBAs and Developers:
Don’t use the design mode to alter any column or table that is participating in Dynamic Data Masking, Design mode force the table to drop and recreate which results in masking to get removed.
Additional Readings:
