Black Friday50% off your first month on any monthly plan with EARLY50 See plans
All 35 lessons
  1. Start here: cloud, data, Fabric and Power BI basics

    1. 1. Start here: the exam, the plan and what you already know
    2. 2. Cloud in plain words: SaaS, PaaS, IaaS and where Fabric fits
    3. 3. Data basics: files, tables, formats and the lakehouse idea
    4. 4. A tour of Microsoft Fabric: OneLake, workspaces and items
    5. 5. Power BI in plain words: semantic models, reports and DAX
  2. Prepare data: get data in (exam area 2, 45-50%)

    1. 6. Connect to data and find it: connections, gateways, OneLake catalog and Real-Time hub
    2. 7. Choosing a data store: lakehouse, warehouse or eventhouse
    3. 8. Use data without copying: shortcuts, mirroring and OneLake integration
    4. 9. Ingest data: Dataflow Gen2, pipelines, Copy job and notebooks
    5. 10. Power Query in Dataflow Gen2: filter, data types, merge and new columns
    6. 11. Messy data: duplicates, missing values and nulls
  3. Prepare data: transform and query (exam area 2)

    1. 12. Views, functions and stored procedures in T-SQL
    2. 13. Build a star schema in a lakehouse or warehouse
    3. 14. Aggregate, join and enrich: SQL and PySpark side by side
    4. 15. Query with the Visual query editor and SQL
    5. 16. Query with KQL
    6. 17. Query with DAX: DAX query view and EVALUATE
  4. Design and build semantic models (exam area 3, 25-30%)

    1. 18. Choose a storage mode: Import, DirectQuery, Direct Lake and Dual
    2. 19. Relationships: star schemas, bridge tables and many-to-many
    3. 20. DAX foundations: measures, filter context, CALCULATE and variables
    4. 21. DAX functions the exam tests: iterators, table filters, windows and information functions
    5. 22. Calculation groups, dynamic format strings and field parameters
    6. 23. Composite models and large semantic model storage format
  5. Optimize models, then secure them (exam areas 3 and 1)

    1. 24. Direct Lake in depth: fallback, refresh and the two flavors
    2. 25. Incremental refresh for semantic models
    3. 26. Faster reports and DAX: finding and fixing slow queries
    4. 27. Who can see what: workspace roles and item permissions
    5. 28. Fine-grained security: rows, columns, objects and files
    6. 29. Governance: sensitivity labels and endorsement
  6. Lifecycle, then exam practice (exam area 1)

    1. 30. Version control: Git integration and Power BI projects (.pbip)
    2. 31. Deployment pipelines and impact analysis
    3. 32. XMLA endpoint and reusable assets: .pbit, .pbids and shared semantic models
    4. 33. Mock exam 1
    5. 34. Final review and exam day: every decision on one page
    6. 35. Mock exam 2
See the leaderboard

24 min

Fine-grained security: rows, columns, objects and files

Restrict rows, columns, tables and files in warehouses, lakehouses and semantic models.

By the end of this lesson you can

  • Build static and dynamic row-level security roles in a semantic model
  • Hide tables and columns from a role with object-level security
  • Secure a Warehouse with GRANT, column and row security, and masking
  • Pick the right layer, and predict how Direct Lake treats each rule

From earlier lessons · 1 of 2

A Juniper analyst must build new reports on the sales semantic model but never change it. Which permission fits?

Pick one answer.

From earlier lessons · 2 of 2

A partner must read one Lakehouse's tables from a Spark notebook. Which share option do you tick?

Pick one answer.

Juniper Coffee's store managers now open the sales report. Then the North manager spots the South region's numbers. Finance asks that supplier cost columns stay hidden from managers altogether. And the loyalty team's T-SQL users must never see full customer emails. Workspace roles can't do any of this. You need security inside the items.

[6]

Which layer to secure

Exam depth: know it cold.

A rule protects data only on the path it sits on. So choose the layer by who reads the data, and how.

ConsumerSecure it inControls
Report users only.The semantic model.RLS roles with DAX, OLS.
T-SQL users of a Warehouse or SQL endpoint.Warehouse T-SQL.GRANT and DENY, column-level security, RLS policy, masking.
Spark, SQL and Direct Lake users of a Lakehouse.OneLake security.Roles on tables and folders, row and column rules.

Row-level security (RLS) limits rows. Column-level security (CLS) limits columns. Object-level security (OLS) hides whole tables or columns.

Decision rule: rules for one model go in the model; rules for every engine go in OneLake security.

[151][247][278]

Rendering diagram…
Model rules guard the model's door. Warehouse rules guard the SQL door. OneLake security guards the data for every engine.

[247][277]

Semantic model RLS: roles with DAX filters

Exam depth: know it cold.

In a semantic model, RLS lives in roles. A role holds one DAX filter per table. The filter is tested on every row and returns TRUE or FALSE. Rows that return FALSE disappear for the role's members.

In Power BI Desktop, select Modeling > Manage roles > New. Pick a table and write the filter. Rules that use DAX functions need the DAX editor.

A static rule compares to a fixed value, such as [Region] = "North". You need one role per region.

Filters flow from that table along active relationships, so put them on a dimension table. A person in two roles sees the union of both.

[209][280]

The Manage security roles window in Power BI Desktop. A role named Example is selected, the Sales Territory table has a filter icon, and the DAX editor shows a filter on Region, SalesTerritoryKey and Group.
Microsoft Learn, CC BY 4.0

[209]

Dynamic RLS with USERPRINCIPALNAME

Exam depth: know it cold.

Dynamic RLS uses one role for everyone. The filter compares a column to the signed-in user. USERPRINCIPALNAME() returns that user's sign-in name (UPN), such as [email protected].

The pattern needs a security table: one row per user and region, such as UserRegion(ManagerEmail, Region). Hide it from report view.

  1. Filter the security table: [ManagerEmail] = USERPRINCIPALNAME().
  2. Relate it to the Region dimension. The filter must flow from the many side to the one side. So set cross-filter direction to Both and tick Apply security filter in both directions.
  3. Region then filters Store, and Store filters Sales.

[209][280]

dax
-- Static role "North", filter on table Region:
[Region] = "North"
-- Dynamic role "Store managers", filter on table UserRegion:
[ManagerEmail] = USERPRINCIPALNAME()
-- Relationships the dynamic role relies on:
-- UserRegion[Region] *-1 Region[Region] (Both, apply security filter in both directions)
-- Region[Region] 1-* Store[Region]
-- Store[StoreKey] 1-* Sales[StoreKey]
One role, Store managers, gives every manager only their regions.

[209]

[209][280]

You are an analytics engineer at Juniper Coffee. A role filters UserRegion with [ManagerEmail] = USERPRINCIPALNAME(). UserRegion is related many-to-one to Region, single direction. Region filters Store and Sales. Managers in the role still see every region. You need each manager to see only their regions, with one role for all. What should you do?

Pick one answer.

[209][280]

Assign members, test, and who bypasses RLS

Exam depth: know it cold.

  • Assign members in the service, not in Desktop. Open the model's ... menu > Security, pick the role and add people. Only workspace Contributors or higher see Security and can assign members. Use Entra security groups, mail-enabled groups or distribution groups. Microsoft 365 groups aren't supported.
  • Test in Desktop with Modeling > View as. Pick a role. For dynamic rules, also tick Other user and enter a UPN.
  • Test in the service with the role's ... > Test as role.

Who bypasses RLS: anyone with Write on the model. That includes every workspace Admin, Member and Contributor. RLS filters Viewers, and people with Read or Build, even in Analyze in Excel. When RLS exists, those users see no data until they join a role.

[209][273][280]

You are an analytics engineer at Juniper Coffee. Dynamic RLS works for most managers. The West manager is in the Store managers role and has the Contributor role in the workspace. She sees every region. She must see only West. What should you do?

Pick one answer.

[209][273]

RLS worked perfectly in testing. Then you remembered the regional manager is a Contributor. Picture: Claymation-style scene: a small clay barista proudly shows a clay chart with one region highlighted.

Object-level security: hide tables and columns

Exam depth: know it cold.

RLS can't hide columns. A user who sees a row sees all its columns. For that you need object-level security (OLS): it hides whole tables or columns from a role, names included.

OLS also lives in roles. You set it in TMDL view or an external tool such as Tabular Editor. Set the table's or column's permission to None to hide it, or Read to show it. Then publish and assign members as for RLS.

For a role member, secured objects act as if they don't exist. A visual that uses one shows an error that the field can't be found. Like RLS, OLS applies to Viewers only, not to Admin, Member or Contributor.

[209][279]

text
createOrReplace
role 'Store managers'
modelPermission: read
tablePermission Product
columnPermission UnitCost
metadataPermission: none
TMDL view script: managers can't see the supplier cost column.

[279]

You are an analytics engineer at Juniper Coffee. Store managers must not see the Product[UnitCost] column in the sales semantic model. They must not even learn that the column exists. Every other column must stay visible. What should you configure?

Pick one answer.

[209][279]

Warehouse and SQL endpoint: T-SQL security

Exam depth: know it cold.

  • Objects: share with Read only, then GRANT what's needed. DENY blocks; REVOKE removes a grant or deny.
  • CLS: GRANT SELECT with a column list. A query that touches any other column fails, so SELECT * fails too.
  • RLS: a predicate function returns a row for allowed rows. A security policy binds it to a table as a filter predicate. Unlike model RLS, the policy applies to everyone, workspace Admins included.
  • Dynamic data masking: shows a disguised value, such as [email protected]. Stored data doesn't change. Functions: default(), email(), random(), partial(). UNMASK shows real values. Workspace Admins, Members and Contributors always see real values.

Masking isn't encryption: a clever WHERE clause can still guess values. If someone must not reach a column at all, use CLS.

[150][152][281][283]

sql
GRANT SELECT ON dbo.Supplier (SupplierID, Name, Region) TO [Analysts]; -- CLS
GO
CREATE FUNCTION sec.fn_region(@ManagerEmail AS varchar(100))
RETURNS TABLE WITH SCHEMABINDING
AS RETURN SELECT 1 AS ok WHERE @ManagerEmail = USER_NAME();
GO
CREATE SECURITY POLICY sec.RegionFilter -- RLS
ADD FILTER PREDICATE sec.fn_region(ManagerEmail) ON dbo.RegionSales
WITH (STATE = ON);
GO
ALTER TABLE dbo.Loyalty ALTER COLUMN Email
ADD MASKED WITH (FUNCTION = 'email()'); -- masking
Column, row and masking rules in the Juniper Warehouse. The sec schema already exists.

[152][282][283]

OneLake security: tables, folders and files

Exam depth: know it cold.

OneLake security puts roles on a Lakehouse's data. Admins and Members create them under Manage OneLake security. Older pages call them OneLake data access roles and mark them preview. Enforcement is now GA in Spark, the Lakehouse, Direct Lake on OneLake and the SQL endpoint.

A role grants Read (or ReadWrite) on chosen schemas, tables or folders, plus optional row and column rules. Folder access is how you secure files. Row rules are fixed SQL filters; unlike model RLS, they can't be dynamic per user.

  • Roles narrow access for Viewers and people with Read only. Admins, Members and Contributors already see everything.
  • Remove narrowed users from DefaultReader, or they keep full access.
  • The SQL endpoint enforces the roles only in User's identity access mode.

[268][276][277][278]

Direct Lake and data security

Exam depth: know it cold.

By default Direct Lake reads as the report user, through single sign-on (SSO). You can bind it to a cloud connection with a fixed identity instead.

Rule lives inDirect Lake on SQL analytics endpointDirect Lake on OneLake
Semantic model RLS or OLS.Works, stays in memory.Works, stays in memory.
SQL endpoint RLS, or a view.Query falls back to DirectQuery.Not applicable.
SQL endpoint CLS on a used column.Visual shows an error.Not applicable.
OneLake security roles.Not applicable.Enforced for the user, stays in memory.

Direct Lake on OneLake never falls back. With SSO, users can query the source directly and skip model rules. So pair model-only rules with a fixed identity.

[247]

You are an analytics engineer at Juniper Coffee. Managers read sales through a Direct Lake model and data scientists through Spark. Both must see only their region, from one set of rules. Report queries must stay in memory. What should you do?

Pick one answer.

[247]

Try it in Power BI Desktop

Use any model with a Region column. Publishing needs a workspace on a trial capacity.

  1. Select Modeling > Manage roles > New. Name the role North.
  2. Select the Region table. Select Switch to DAX editor and enter [Region] = "North". Select Save.
  3. Select Modeling > View as, tick North, and select OK. Check that visuals show only North.
  4. Publish the model to your workspace.
  5. In the workspace, select ... next to the model, then Security. Add a test user to North and select Add.

[209][280]

Exam-style practice

You are an analytics engineer at Juniper Coffee. Loyalty-team users query the Warehouse with T-SQL. They must see the first letter of each member's email to spot typos. They must never read the PaymentToken column. What should you configure?

Pick one answer.

[152][281]

FlashcardsIndex cardsFlashcards ⚡

Card 1 of 15

[209][247][278][279][281][283]

Worth keeping

  1. Model RLS: roles with DAX filters; dynamic RLS uses USERPRINCIPALNAME() and a security table.
  2. Write on the model bypasses RLS and OLS, so Admins, Members and Contributors see everything.
  3. OLS hides tables and columns; RLS only hides rows.
  4. Warehouse: GRANT and CLS, a predicate function plus policy for rows, masking for hints.
  5. Rules for every engine go in OneLake security; Direct Lake on OneLake enforces them in memory.

Each manager now sees only their region. But how does anyone browsing the catalog know the sales model holds customer data, or that it's the trusted copy? Next lesson: sensitivity labels and endorsement.

Leaderboard

Loading…

50 XP for each lesson you finish, 10 for each right answer. Names show only for learners who turn on Show me on leaderboards.

Sources for this lesson (17)

Each fact in the lesson is marked with the number of the page it comes from.

  1. [6]Microsoft Learn: Study guide for Exam DP-600: Implementing Analytics Solutions Using Microsoft Fabric
  2. [150]Microsoft Learn: SQL granular permissions in Microsoft Fabric
  3. [151]Microsoft Learn: Secure your Fabric Data Warehouse
  4. [152]Microsoft Learn: Column-level security in Fabric Data Warehouse
  5. [209]Microsoft Learn: Row-level security (RLS) with Power BI
  6. [247]Microsoft Learn: Integrate Direct Lake security
  7. [268]Microsoft Learn: Data security in OneLake
  8. [273]Microsoft Learn: Semantic model permissions
  9. [274]Microsoft Learn: Share items in Microsoft Fabric
  10. [276]Microsoft Learn: Read data secured with OneLake security
  11. [277]Microsoft Learn: OneLake security for SQL analytics endpoints
  12. [278]Microsoft Learn: Table, column, and row-level security in OneLake
  13. [279]Microsoft Learn: Object-level security (OLS)
  14. [280]Microsoft Learn: Row-level security (RLS) guidance in Power BI Desktop
  15. [281]Microsoft Learn: Dynamic data masking in Fabric Data Warehouse
  16. [282]Microsoft Learn: How to implement dynamic data masking in Fabric Data Warehouse
  17. [283]Microsoft Learn: Row-level security in Fabric Data Warehouse