Dynamic Data Masking and Row-Level Security

Hiding data in plain sight

Dynamic Data Masking (DDM)

DDM masks column values in query results. The actual data is not changed — only how it appears to users without the UNMASK permission.

Masking functions

Function	Example Input	Masked Output	Use For
default()	”John Smith"	"XXXX”	General text
email()	”john@bank.com"	"jXXX@XXXX.com”	Email addresses
partial(2, 'XXX', 2)	”0274551234"	"02XXX34”	Phone numbers, account numbers
random(1, 100)	85000	47	Numeric columns (randomised)

CREATE TABLE Employees (
    EmployeeId INT NOT NULL PRIMARY KEY,
    FullName NVARCHAR(200) MASKED WITH (FUNCTION = 'default()') NOT NULL,
    Email NVARCHAR(320) MASKED WITH (FUNCTION = 'email()') NOT NULL,
    Phone NVARCHAR(20) MASKED WITH (FUNCTION = 'partial(2, "XXX-XXX-", 2)'),
    Salary DECIMAL(10,2) MASKED WITH (FUNCTION = 'random(1000, 9999)')
);

-- Grant unmask to specific users
GRANT UNMASK TO [HR_Manager];

💡 Exam tip: DDM is not a security boundary

DDM is a convenience feature, not a security feature. A determined user can infer masked values through clever queries (e.g., binary search with WHERE clauses). For true data protection, use Always Encrypted or column-level encryption.

The exam may present DDM as “sufficient for GDPR compliance” — that is FALSE. DDM is suitable for reducing casual exposure (like a support agent seeing partial credit card numbers) but not for meeting regulatory encryption requirements.

Row-Level Security (RLS)

RLS transparently filters rows based on who is executing the query. It uses a security predicate function that returns 1 (visible) or 0 (hidden) for each row.

-- Step 1: Create a predicate function
CREATE FUNCTION dbo.fn_SecurityPredicate(@Region NVARCHAR(50))
RETURNS TABLE
WITH SCHEMABINDING
AS
RETURN SELECT 1 AS Result
WHERE @Region = USER_NAME()
   OR USER_NAME() = 'dbo'
   OR IS_MEMBER('GlobalManagers') = 1;

-- Step 2: Create a security policy
CREATE SECURITY POLICY RegionFilter
ADD FILTER PREDICATE dbo.fn_SecurityPredicate(Region) ON dbo.Sales
WITH (STATE = ON);

Now when a user queries the Sales table, they only see rows matching their region — automatically, transparently, on every query.

Filter vs Block predicates

Predicate Type	What It Does	Applies To
FILTER	Silently hides rows (WHERE clause injection)	SELECT, UPDATE, DELETE
BLOCK	Prevents operations on unauthorised rows (raises error)	INSERT (AFTER INSERT), UPDATE (AFTER/BEFORE)

DDM vs RLS — different tools for different problems

Feature	Dynamic Data Masking	Row-Level Security
What it hides	Column values (partial or full)	Entire rows
Data changed?	No — presentation only	No — transparent filtering
Bypass risk	High — clever queries can infer values	Low — enforced at engine level
Performance	Minimal — applied on output	Moderate — predicate runs per row
Use case	Reduce casual exposure (support agents, reports)	Multi-tenant data isolation, regional access control

💡 Scenario: Ingrid's multi-region access at Nordic Shield

Nordic Shield Insurance operates across Scandinavia. Each regional office should only see claims from their own country. Ingrid implements RLS with a predicate that checks the user’s country against the claim’s country. Global managers bypass the filter. Now a Norwegian claims officer querying the Claims table automatically sees only Norwegian claims — no code changes needed in any application.

Question

Is Dynamic Data Masking a security feature?

Click or press Enter to reveal answer

Answer

No — DDM is a convenience feature that reduces casual data exposure. It masks column values in query results but the underlying data is unchanged. Clever queries can infer masked values. For true security, use encryption. DDM alone is NOT sufficient for regulatory compliance.

Click to flip back

Question

What is the difference between FILTER and BLOCK predicates in RLS?

Click or press Enter to reveal answer

Answer

FILTER predicates silently exclude rows from query results (like an invisible WHERE clause). BLOCK predicates prevent INSERT/UPDATE operations on unauthorised rows and raise an error. Use FILTER for read isolation, BLOCK for write protection.

Click to flip back

Question

What does an RLS security predicate function return?

Click or press Enter to reveal answer

Answer

It is an inline TVF with SCHEMABINDING that returns 1 (row is visible/allowed) or 0 (row is hidden/blocked). It takes the row's column values as parameters and checks them against the current user context (USER_NAME(), IS_MEMBER(), SESSION_CONTEXT).

Click to flip back

Knowledge Check

Ingrid at Nordic Shield needs to ensure that claims officers can only see claims from their own region. The filtering must be automatic and cannot be bypassed by direct SQL queries. Which approach?

ADynamic Data Masking on the Region columnBRow-Level Security with a filter predicate on the Region columnCA view with WHERE Region = USER_NAME()DApplication-level filtering in the API layer

Check Answer

🎬 Video coming soon

Next up: Permissions, Auditing, and Passwordless Access — control who can do what, track everything, and eliminate passwords.