Dynamic data masking (DDM) limits sensitive data exposure by masking it to non-privileged users. It can be used to greatly simplify the design and coding of security in your application.
Permission
You 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.
Adding, replacing, or removing the mask of a column, requires the ALTER ANY MASK permission and ALTER permission on the table. It is appropriate to grant ALTER ANY MASK to a security officer.
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.
The CONTROL permission on the database includes both the ALTER ANY MASK and UNMASK permission.
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.
Create Table with mask
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 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');
SELECT * FROM MembershipMask; /* MemberID FirstName LastName Phone Email 1 Vipul Bhatt 456.123.4567 Vipul@Himanshupatel.in 2 Dev Shah 456.123.3333 Dev@Himanshupatel.in 3 Raj Patel 555.123.4569 raj@Himanshupatel.in */ --Create user CREATE USER TestUser WITHOUT LOGIN; GRANT SELECT ON MembershipMask TO TestUser; GO EXECUTE AS USER = 'TestUser'; SELECT * FROM MembershipMask; REVERT; /* 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 */
To view definitaion
SELECT TBLS.name as TableName,MC.NAME ColumnName , MC.is_masked IsMasked, MC.masking_function MaskFunction FROM sys.masked_columns AS MC JOIN sys.tables AS TBLS ON MC.object_id = TBLS.object_id WHERE is_masked = 1; /* TableName ColumnName IsMasked MaskFunction MembershipMask FirstName 1 partial(1, "XXXXXXX", 0) MembershipMask Phone 1 default() MembershipMask Email 1 email() */
How to drop mask
--Dropping a Dynamic Data Mask ALTER TABLE Membership ALTER COLUMN LastName DROP MASKED;
How to add mask in existing column
--Adding or Editing a Mask on an Existing Column ALTER TABLE Membership ALTER COLUMN LastName ADD MASKED WITH (FUNCTION = 'partial(2,"XXX",0)');
Grant and revoke mask
GRANT UNMASK TO TestUserins; GO REVOKE UNMASK TO TestUserins; GO
Partial masking
--To implement a partial masking rule, we must specify three arguments: how many opening characters to reveal, how many middle characters to mask and how to mask them, and how many trailing characters to reveal. In this case, we are revealing no opening characters (0), masking the first five characters with x values (xxxxx), and revealing the last four characters (4).
ALTER TABLE EmpInfo
ALTER COLUMN NatID NVARCHAR(15)
MASKED WITH (FUNCTION = 'partial(0, "xxxxx", 4)') NOT NULL; --ex 716374314 = xxxxx4314
Random masking
--This time we want the returned values to fall between 101 and 999. ALTER TABLE EmpInfo ALTER COLUMN SalesYTD MONEY MASKED WITH (FUNCTION = 'random(101, 999)') NOT NULL;
