2.4.3. Dynamic Data Masking
💡 First Principle: Dynamic Data Masking (DDM) obscures sensitive data in query results without modifying stored data—like a privacy screen on a monitor. The underlying data remains unchanged; only the view is altered. Critically, users with UNMASK permission see the original data, so DDM is a display-layer protection, not a security boundary.
Scenario: Customer support representatives need to access customer records but shouldn't see full credit card numbers. DDM shows "XXXX-XXXX-XXXX-1234" while the database stores the complete number.
Masking Functions
| Function | Use Case | Example Output |
|---|---|---|
| default() | Full masking | 0, xxxx, 01-01-1900 |
| email() | Email addresses: first letter plus a constant .com; the real domain isn't shown | aXXX@XXXX.com |
| random(start, end) | Numeric obfuscation | Random number in range |
| partial(prefix, padding, suffix) | Partial reveal | XXXX-XXXX-XXXX-1234 |
Implementing DDM for Credit Cards
-- Add partial mask to credit card column
ALTER TABLE dbo.Customers
ALTER COLUMN CreditCardNumber ADD MASKED
WITH (FUNCTION = 'partial(0,"XXXX-XXXX-XXXX-",4)');
-- Grant unmask permission to authorized role
GRANT UNMASK TO FinanceTeam;
⚠️ Exam Trap: Using default() for credit card masking hides all data, which isn't useful for verification purposes. Use partial(0,"XXXX-XXXX-XXXX-",4) to show the last 4 digits—the industry standard for card verification. Exam questions about "verifying the last 4 digits" require partial masking, not default.
Key Trade-Offs:
- DDM vs. CLS: DDM allows users to see that data exists (masked); CLS hides columns entirely. Choose based on whether users need to know the column exists.
- Security vs. Usability: More masking improves security but may hinder legitimate work processes.
- RLS vs. DDM: RLS decides which rows a user sees; DDM decides how a column's values are displayed in the rows they can see.
Who sees real values: users with UNMASK (or CONTROL) on the warehouse, which includes workspace Admins, Members and Contributors by design. Masks apply to Viewers and users with SQL permissions only. ALTER ANY MASK lets someone add or remove masks; it doesn't unmask.
⚠️ Exam Trap: DDM can be defeated by inference. A user who can query can filter on the real value (WHERE Salary > 99999 AND Salary < 100001) and deduce it. DDM prevents accidental exposure, not a determined analyst. If analysts must never reach identifying values, give them an aggregated view or semantic model with no PII columns and no access to the base table.
Reflection Question: A support representative needs to verify a customer by the last 4 digits of their credit card. Why would default() masking be inappropriate here?
