Implementing Dynamic Data Masking in SQL Server
Introduction to Dynamic Data Masking (DDM)
Dynamic Data Masking (DDM) is a technique that restricts access to sensitive data by masking it for users without the required privileges. This approach makes it easier to design and implement security measures in your applications, ensuring that sensitive information is protected while allowing authorized users to access necessary data.
Permissions Required for Dynamic Data Masking
To create a table with dynamic data masking, you don’t need special permissions beyond the standard CREATE TABLE and ALTER permissions for the schema. However, there are additional permissions needed for manipulating the data masks.
-
Modifying Masks: You need the
ALTER ANY MASKpermission, in addition toALTERon the table. -
Viewing Data: Users with
SELECTpermissions can see only the masked data. AssignUNMASKpermission to allow viewing unmasked data. -
Control Permissions: The
CONTROLpermission includes bothALTER ANY MASKandUNMASK.
Limitations and Restrictions of Data Masking
There are some column types that cannot be masked, including:
- Encrypted columns (Always Encrypted)
- FILESTREAM columns
- Sparse columns that are part of a column set
- Computed columns: If a computed column depends on a masked column, it will show masked data.
- Columns involved in a FULLTEXT index
Example: Creating a Table with a Mask
You can create a table with dynamic data masking as shown below:
CREATE TABLE MembershipMask ( MemberID int IDENTITY PRIMARY KEY, FirstName varchar(100) MASKED WITH (FUNCTION = 'partial(1,"XXXXXXX",0)') NULL, LastName varchar(100) NOT NULL, Phone varchar(12) MASKED WITH (FUNCTION = 'default()') NULL, Email varchar(100) MASKED WITH (FUNCTION = 'email()') NULL );
Insert sample data:
INSERT MembershipMask (FirstName, LastName, Phone, Email) VALUES
('Vipul', 'Bhatt', '456.123.4567', 'Vipul@Himanshupatel.in'),
('Dev', 'Shah', '456.123.3333', 'Dev@Himanshupatel.in'),
('Raj', 'Patel', '555.123.4569', 'raj@Himanshupatel.in');
Querying the table will display masked data:
SELECT * FROM MembershipMask;
Example output for a user with SELECT permission:
MemberID FirstName LastName Phone Email 1 VXXXXXXX Bhatt xxxx VXXX@XXXX.com 2 DXXXXXXX Shah xxxx DXXX@XXXX.com 3 RXXXXXXX Patel xxxx RXXX@XXXX.com
Viewing the Data Masking Definition
Use the following query:
SELECT TBLS.name AS TableName, MC.NAME AS ColumnName, MC.is_masked AS IsMasked, MC.masking_function AS MaskFunction FROM sys.masked_columns AS MC JOIN sys.tables AS TBLS ON MC.object_id = TBLS.object_id WHERE is_masked = 1;
Dropping a Mask
To remove a mask from a column:
ALTER TABLE Membership ALTER COLUMN LastName DROP MASKED;
Adding or Editing a Mask on an Existing Column
ALTER TABLE Membership ALTER COLUMN LastName ADD MASKED WITH (FUNCTION = 'partial(2,"XXX",0)');
Granting and Revoking Mask Permissions
GRANT UNMASK TO TestUserins; GO REVOKE UNMASK FROM TestUserins; GO
Implementing Partial Masking
To reveal only the last 4 digits of a national ID number:
ALTER TABLE EmpInfo ALTER COLUMN NatID NVARCHAR(15) MASKED WITH (FUNCTION = 'partial(0, "xxxxx", 4)') NOT NULL; -- e.g., 716374314 becomes xxxxx4314
Implementing Random Masking
To mask a monetary value within a range:
ALTER TABLE EmpInfo ALTER COLUMN SalesYTD MONEY MASKED WITH (FUNCTION = 'random(101, 999)') NOT NULL;
Further Reading
For more information, refer to the official documentation:
