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 MASKplus 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'spartial(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?
Log in to save your progress.
Sources
- Row-level security in Fabric Data Warehouse, Microsoft Learn
- Column-level security in Fabric Data Warehouse, Microsoft Learn
- Dynamic data masking in Fabric Data Warehouse, Microsoft Learn
- Data security in OneLake, Microsoft Learn
- How lakehouse sharing works: folder-level access control, Microsoft Learn