2.2 — Dynamic Data Masking, Row-Level Security and Object-Level Permissions
DP-800 Domain 2 · Skill 2.2
What You Need to Know
These three features control what an authenticated user sees: masked values, a subset of rows, or only certain objects. They complement encryption (lesson 2.1).
Dynamic data masking (DDM)
| Function | Shows |
|---|---|
default() |
XXXX, 0, or 1900-01-01, depending on the type |
email() |
aXXX@XXXX.com |
random(start, end) |
A random number in the range |
partial(prefix, "padding", suffix) |
The first prefix and last suffix characters, with padding in between |
ALTER TABLE dbo.Customer ALTER COLUMN Phone
ADD MASKED WITH (FUNCTION = 'partial(0, "XXX-XXX-", 4)');
GRANT UNMASK ON dbo.Customer(Phone) TO SupportLeads; -- column-level UNMASK
- Data is masked in query results only. Stored values are unchanged.
- UNMASK (at database, schema, table or column level) reveals real values. Owners and
db_ownersee unmasked data. - DDM isn't a security boundary: users can infer values with
WHEREfilters. Combine it with permissions, RLS or Always Encrypted for truly sensitive data.
Row-level security (RLS)
CREATE FUNCTION sec.fn_tenant(@TenantID int) RETURNS TABLE WITH SCHEMABINDING AS
RETURN SELECT 1 AS ok WHERE @TenantID = CAST(SESSION_CONTEXT(N'TenantID') AS int);
CREATE SECURITY POLICY sec.TenantPolicy
ADD FILTER PREDICATE sec.fn_tenant(TenantID) ON dbo.Orders,
ADD BLOCK PREDICATE sec.fn_tenant(TenantID) ON dbo.Orders AFTER INSERT
WITH (STATE = ON);
- Filter predicates silently hide rows from SELECT, UPDATE and DELETE.
- Block predicates (SQL Server and Azure SQL) prevent writes that break the rule:
AFTER INSERT,AFTER UPDATE,BEFORE UPDATEandBEFORE DELETE. This stops a tenant inserting rows for another tenant. - Multi-tenant apps that connect with one shared login set
SESSION_CONTEXT(sp_set_session_context @key = N'TenantID', @value = 42, @read_only = 1) after connecting, and the predicate reads it. - Per-user apps use
USER_NAME()orSUSER_SNAME()with a mapping table.
Object-level permissions
| Statement | Effect |
|---|---|
GRANT SELECT ON SCHEMA::sales TO ReportingRole |
Allows reading every object in a schema |
DENY SELECT ON dbo.Salary TO Interns |
DENY overrides any GRANT (except for owners and sysadmin) |
REVOKE |
Removes a previous GRANT or DENY |
GRANT EXECUTE ON dbo.usp_PlaceOrder TO AppRole |
The app calls procedures without having direct table rights |
GRANT SELECT ON dbo.Customer (CustomerID, Name) TO Support |
Column-level permission |
- Grant to database roles, not individual users, and give roles the least privilege they need.
- Ownership chaining: when a procedure and the tables it uses have the same owner,
EXECUTEpermission on the procedure is enough. Users can't touch the tables directly, which is the classic "access only through procedures" pattern. - Schemas are security boundaries: put API objects in an
apischema and grant on the schema.
Key Concept: DDM disguises values, RLS filters and blocks rows (use SESSION_CONTEXT for multi-tenant apps), and GRANT, DENY and REVOKE decide which objects are reachable. DENY beats GRANT.
Exam Traps
- Using only a filter predicate when tenants must also be prevented from inserting rows for other tenants (add a block predicate)
- Treating DDM as protection against users with ad hoc query access
- Granting table permissions to an app that should only call procedures
- Forgetting that DENY overrides a GRANT that comes from another role
Check your understanding
Practice Scenario
🔒 This practice scenario is part of Skillmarked Pro
The lessons stay free. Pro unlocks the other 14 DP-800 practice scenarios and the 50-question timed mock exam, plus everything for every other exam.
Launch Deal: first month €9.95, then €19.90/month. Enter code LAUNCH50 at checkout. Ends 1 December.
Cancel anytime. Or €199 a year, which saves 17%.
Not sure yet? Try 10 free mock exam questions.
Already Pro? Log in.
Sources
- Dynamic data masking, Microsoft Learn
- Row-level security, Microsoft Learn
- SESSION_CONTEXT (Transact-SQL), Microsoft Learn
- GRANT object permissions, Microsoft Learn
- Ownership and user-schema separation (ownership chaining), Microsoft Learn