SQL Server 2016 introduced Dynamic Data Masking for obfuscating certain values in records, not exactly as a security measure but for hiding sensitive data. While this was being discussed, a question raised: Can we filter data even when data is masked and no permission to unmask?
Generally, unless you are a member of db_owner role, you cannot read masked data. If need, you should be granted with UNMASK permission. Now can you masked filter data? Can you group masked data? Can you do them even if you have no permission for unmasking?
Yes, it is possible. Let's have a look on below code;
-- Create a test database CREATE DATABASE Sales GO -- Connect with it USE Sales; GO -- Create a sample table with masked columns CREATE TABLE dbo.RetailCustomer ( RetailCustomerId int identity(1,1) PRIMARY KEY , Name varchar(100) NOT NULL , EmailAddress varchar(100) MASKED WITH (FUNCTION = 'email()') NOT NULL , CreditLimit decimal(16,2) MASKED WITH (FUNCTION = 'random(1, 10000)') NOT NULL , SecurityTokenCode char(10) MASKED WITH (FUNCTION = 'partial(2, "xxxx", 0)') NOT NULL ); GO -- Insert set of records INSERT INTO dbo.RetailCustomer (Name, EmailAddress, CreditLimit, SecurityTokenCode) VALUES ('Customer1', 'email@example.com', 10000, 'EX12345678') , ('Customer2', 'firstname.lastname@example.org', 120000, 'EX11223344') , ('Customer3', 'email@example.com', 8000, 'TX11223344') -- Checking records; SELECT * FROM dbo.RetailCustomer;
As you see, I do not see data as masked data because I am a member of db_owner role. But if you check with different account, he will see masked data. See the code below;
-- Creating a user CREATE USER Peter WITHOUT LOGIN; -- Grant read permission to Peter -- Note that this does not mean that he can unmask GRANT SELECT ON dbo.RetailCustomer TO Peter; -- Check with his account EXECUTE AS USER = 'Peter' SELECT * FROM dbo.RetailCustomer REVERT;
We can grant unmask permission to Peter but let's see whether he can do some aggregation and filtering with masked data.
EXECUTE AS USER = 'Peter' -- Can he aggregate masked values SELECT SUM(CreditLimit) FROM dbo.RetailCustomer; -- Can he performed operations against masked values SELECT LEN(EmailAddress) FROM dbo.RetailCustomer; SELECT LEFT(EmailAddress, 2) FROM dbo.RetailCustomer; -- Can he filer records with masked columns SELECT * FROM dbo.RetailCustomer WHERE CreditLimit = 120000 SELECT * FROM dbo.RetailCustomer WHERE EmailAddress = 'firstname.lastname@example.org'; REVERT;
See the result. He cannot do some operations but he can do filtering without any issue. Looks unsecured? Do not worry, that is why I mentioned that Dynamic Data Masking is not for securing or encrypting data, it is for obfuscating values.