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 | 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.
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?
