Launch Deal · 50% off 1st month ·

1.5 — Row, Column, Object and File-Level Security, and Dynamic Data Masking

DP-700 Domain 1 · Skill 1.5

What You Need to Know

Once someone can reach an item, these controls decide which data they see. The method depends on the engine: T-SQL security inside a warehouse or SQL analytics endpoint, or OneLake security roles for lakehouse data that every engine reads.

T-SQL security in a warehouse or SQL analytics endpoint

Level Mechanism Example
Object-level GRANT / DENY / REVOKE on tables, views, procedures or schemas GRANT SELECT ON SCHEMA::gold TO [Analysts];
Column-level GRANT SELECT on a list of columns GRANT SELECT ON dbo.Customer (CustomerID, City) TO [Support];
Row-level (RLS) An inline table-valued function as the predicate, enforced by CREATE SECURITY POLICY See below
CREATE FUNCTION sec.fn_rep(@SalesRep AS varchar(60))
    RETURNS TABLE WITH SCHEMABINDING
AS RETURN SELECT 1 AS ok WHERE @SalesRep = USER_NAME();
GO
CREATE SECURITY POLICY sec.SalesFilter
    ADD FILTER PREDICATE sec.fn_rep(SalesRep) ON dbo.Sales
    WITH (STATE = ON);
  • Fabric Data Warehouse supports filter predicates, which silently remove rows from SELECT, UPDATE and DELETE.
  • Policies apply to everyone, including workspace Admins. If admins must see all rows, write that into the function.
  • Each table needs its own policy. A table without one is unfiltered.
  • Power BI Direct Lake queries on a warehouse with RLS fall back to DirectQuery.

OneLake security roles (lakehouse data)

  • Workspace Admins and Members create roles on a lakehouse (or other data item). Each role grants Read (or ReadWrite) on chosen tables, schemas or folders, with optional row-level and column-level rules on tables.
  • Roles restrict only Viewers and users with Read access on the item. Admins, Members and Contributors can already read and write everything.
  • Folder access is inherited by subfolders. That's how you control file-level access to the Files area.
  • The built-in DefaultReader role gives everyone with ReadAll access to all data. To restrict people, edit or delete DefaultReader and grant narrower roles instead.
  • The rules are enforced consistently across Spark, the SQL analytics endpoint (in user identity mode) and Direct Lake on OneLake.

Dynamic data masking (DDM)

DDM hides values in query results only. The stored data doesn't change.

Function Result Example
default() Full mask: XXXX for strings, 0 for numbers, 1900-01-01 for dates BirthDate date MASKED WITH (FUNCTION = 'default()')
email() First letter plus XXX@XXXX.com aXXX@XXXX.com
random(start, end) A random number in the range (numeric columns) random(1, 100)
partial(prefix, "padding", suffix) Keeps prefix characters at the start and suffix at the end partial(0, "XXX-XX-", 4) shows only the last 4
ALTER TABLE dbo.Member
ALTER COLUMN PostCode ADD MASKED WITH (FUNCTION = 'partial(3, "***", 0)');
  • Workspace Admins, Members and Contributors have CONTROL on the warehouse, so they always see unmasked data. Viewers and shared users see masked data unless they're granted UNMASK.
  • Adding or changing a mask needs ALTER ANY MASK plus ALTER on the table.
  • DDM isn't a security boundary: a user who can query the data can infer values with range filters (WHERE Salary > 99999). Combine it with column-level security for genuinely sensitive data.

Key Concept: To make data disappear entirely, use RLS, CLS or OLS. To show it in a disguised form, use DDM. For lakehouse files and tables read by Spark, use OneLake security roles.

Exam Traps

  • Building RLS with a stored procedure, view or constraint (it needs an inline table-valued function and a security policy)
  • Expecting a mask to hide data from a workspace Contributor
  • Using email() when the requirement says how many characters to keep at each end (that's partial(prefix, "…", suffix))
  • Applying a OneLake security role to restrict a Contributor
  • Leaving the DefaultReader role in place when only some folders should be readable

Check your understanding

Practice Scenario

A warehouse table dbo.Claims has a Region column. The table dbo.AdjusterRegion maps each adjuster's user principal name to the regions they handle. Adjusters query the warehouse with T-SQL and must see only the claims for their regions. What do you implement?

Report issue

Log in to save your progress.