Launch Deal · 50% off 1st month ·

1.3 — Row-, Column-, Object- and File-Level Access Control

DP-600 Domain 1 · Skill 1.3

What You Need to Know

Workspace roles and sharing decide who can reach an item. Granular controls decide which rows, columns, tables and files they see. Where you enforce them depends on the engine people query through.

Where each control lives

Data is consumed through Row-level Column / object-level File / folder-level
Warehouse or SQL analytics endpoint (T-SQL) Security policy with an inline table-valued function as the predicate GRANT / DENY on columns, tables, views or schemas Not applicable
Lakehouse via Spark or OneLake OneLake security role with a row filter OneLake security role with column rules, or table/schema scope OneLake security role on folders under Files (inherited by subfolders)
Power BI semantic model Model RLS roles with DAX filters (lesson 3.6) Model OLS on tables or columns (lesson 3.6) Not applicable

Warehouse examples

-- Object level: analysts see only the gold schema
GRANT SELECT ON SCHEMA::gold TO [Analysts];

-- Column level: support can read customers without contact details
GRANT SELECT ON dbo.Customer (CustomerID, Segment, City) TO [Support];

-- Row level: each manager sees their own region
CREATE FUNCTION sec.fn_region(@Region varchar(20))
  RETURNS TABLE WITH SCHEMABINDING
AS RETURN SELECT 1 AS ok FROM sec.ManagerRegion m
          WHERE m.Region = @Region AND m.UserUPN = USER_NAME();
CREATE SECURITY POLICY sec.RegionPolicy
  ADD FILTER PREDICATE sec.fn_region(Region) ON dbo.Sales WITH (STATE = ON);
  • RLS in a warehouse applies to everyone, admins included, unless the function allows otherwise.
  • Power BI Direct Lake on SQL models fall back to DirectQuery when the warehouse uses RLS, so that the policy can be enforced. Direct Lake on OneLake doesn't fall back.
  • Dynamic data masking hides values in query results (partial(), email(), default()) but isn't a security boundary. Workspace Admins, Members and Contributors always see unmasked data.

OneLake security (lakehouse)

  • Workspace Admins and Members define roles on a lakehouse. Each role grants Read on tables, schemas or folders, with optional row and column rules.
  • Roles apply to Viewers and users who were shared the item. Admins, Members and Contributors already have full access.
  • Remove or narrow the DefaultReader role, otherwise everyone with ReadAll keeps seeing everything.

Choosing where to secure

  • If the same rule must apply whether people use SQL, Spark or Power BI, enforce it as close to the data as possible: a OneLake security role or warehouse security, plus matching model security when the model uses Import.
  • If users only ever reach the data through one semantic model, model RLS and OLS are often enough.

Key Concept: Find the engine the users query through. T-SQL means GRANT, DENY and security policies. Spark and OneLake mean OneLake security roles, including folder access. Power BI reports mean model RLS and OLS.

Exam Traps

  • Expecting semantic model RLS to protect the same data when users query the warehouse directly
  • Expecting a OneLake security role to restrict a workspace Contributor
  • Using a view per region instead of a security policy for row-level security
  • Forgetting that Direct Lake on SQL falls back to DirectQuery when SQL RLS is used

Check your understanding

Practice Scenario

Analysts query a Fabric warehouse with T-SQL. They must be able to read every column of dbo.Employee except Salary, and a query that asks for Salary must fail rather than return disguised values. What should you implement?

Report issue

Log in to save your progress.