Launch Deal · 50% off 1st month ·

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_owner see unmasked data.
  • DDM isn't a security boundary: users can infer values with WHERE filters. 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 UPDATE and BEFORE 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() or SUSER_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, EXECUTE permission 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 api schema 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.

See plans

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.