Dynamic data masking limits sensitive data exposure by masking it to non-privileged users. Dynamic data masking helps prevent unauthorized access to sensitive data by enabling customers to designate how much of the sensitive data to reveal with minimal impact on the application layer. It’s a security feature that hides the sensitive data in the result set of a query over designated database fields, while the data in the database is not changed. Dynamic data masking is easy to use with existing applications, since masking rules are applied in the query results. Many applications can mask sensitive data without modifying existing queries.
Lets create a table with MASKED WITH (FUNCTION)
USE <Database Name> GO CREATE TABLE Membership (MemberID int IDENTITY PRIMARY KEY, FirstName varchar(100) MASKED WITH (FUNCTION = 'partial(1,"-|-|-|",0)') NULL, LastName varchar(100) NOT NULL, Phone# varchar(12) MASKED WITH (FUNCTION = 'default()') NULL, Email varchar(100) MASKED WITH (FUNCTION = 'email()') NULL);
Insert couple of rows
USE <Database Name> GO INSERT Membership (FirstName, LastName, Phone#, Email) VALUES ('Roberto', 'Tamburello', '555.123.4567', 'RTamburello@contoso.com'), ('Janice', 'Galvin', '555.123.4568', 'JGalvin@contoso.com.co'), ('Zheng', 'Mu', '555.123.4569', 'ZMu@contoso.net');
Create a user in database Windows / SQL
CREATE USER test1 FOR LOGIN;
Grant SELECT Permission on Table to User
GRANT SELECT ON Membership TO test1;
Access the table with test1 user
EXECUTE AS USER = 'test1'; SELECT * FROM Membership; REVERT;
You can connect to the SQL Server Database using the database and access the table, in both the case you will see the same masked data
In case user wants to see the unmasked data , GRANT the UNMASK Permission from user
GRANT UNMASK TO test1;
REVOKE the UNMASK Permission to force the Masking
REVOKE UNMASK TO test1;