SQL Server Data Masking - Filtering masked values

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', 'customer1@gmail.com', 10000, 'EX12345678')
 , ('Customer2', 'customer1@company.lk', 120000, 'EX11223344')
 , ('Customer3', 'customer3@abc.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 = 'customer1@company.lk';

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.