Black Friday50% off your first month on any monthly plan with EARLY50 See plans
All 33 lessons
  1. Start here: cloud and data 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. The three languages: SQL, PySpark and KQL side by side
    6. 6. Getting data in: the map of Fabric tools
  2. Store, ingest and transform batch data (exam area 2, 30-35%)

    1. 7. Choosing a data store and a transformation tool
    2. 8. Shortcuts and mirroring: use data without copying it
    3. 9. Ingest data with pipelines and Copy job
    4. 10. Transform with PySpark and SQL: join, denormalize, aggregate
    5. 11. Messy data: duplicates, missing values and late arrivals
    6. 12. Full and incremental loads, and loading a star schema
  3. Orchestrate and stream (exam areas 1 and 2)

    1. 13. Pipeline or notebook? Choosing how to orchestrate
    2. 14. Schedules and event-based triggers
    3. 15. Parameters and dynamic expressions
    4. 16. Streaming in Fabric: Eventstreams, Spark streaming and KQL
    5. 17. KQL and windowing functions for real-time data
  4. Set up and secure a Fabric solution (exam area 1, 30-35%)

    1. 18. Workspace settings: Spark, domains, OneLake and data workflows
    2. 19. Version control with Git and database projects
    3. 20. Deployment pipelines: development, test and production
    4. 21. Who can see what: workspace roles and item permissions
    5. 22. Fine-grained security: rows, columns, objects, files and masking
    6. 23. Governance: sensitivity labels and endorsement
  5. Monitor, fix and optimize (exam area 3, 30-35%)

    1. 24. Monitoring hub, alerts and Activator
    2. 25. Fixing errors in pipelines, dataflows and notebooks
    3. 26. Fixing errors in Eventhouse, Eventstream and T-SQL
    4. 27. Optimize lakehouse tables: V-Order, OPTIMIZE, VACUUM and partitions
    5. 28. Optimize Spark, pipelines and warehouses
    6. 29. Optimize eventstreams, eventhouses and queries
  6. Exam practice and final review

    1. 30. Exam day: question types, timing and how to think
    2. 31. Mock exam 1
    3. 32. Mock exam 2
    4. 33. Final review: every decision on one page
See the leaderboard

22 min

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

Restrict which rows and columns people see and mask sensitive values in Fabric.

By the end of this lesson you can

  • Write T-SQL for object-, column- and row-level security in a Warehouse
  • Mask sensitive columns and grant UNMASK to the right people
  • Restrict folders, tables, rows and columns in a Lakehouse with OneLake security
  • Pick the control and the engine each scenario needs

From earlier lessons · 1 of 2

A Northwind partner must read one Lakehouse's tables from a Spark notebook, nothing more. Which item permission should you add when you share it?

Pick one answer.

From earlier lessons · 2 of 2

Which users do OneLake security roles actually restrict?

Pick one answer.

Northwind Rides gives its support agents T-SQL access to the customers Warehouse. Then a manager reads the rules. Agents may see customers in their own city only. They must never see card numbers. Email addresses should show only a hint. One table, three different limits. Workspace roles can't do this.

[4]

Where each control lives

Exam depth: know it cold.

Fine-grained security comes in two families. T-SQL security lives in a Warehouse or SQL analytics endpoint. OneLake security lives on a Lakehouse and applies across engines.

ControlWarehouse or SQL endpoint (T-SQL)Lakehouse (OneLake security role)
Object-level.GRANT, DENY, REVOKE on schemas, tables and views.Role scoped to schemas, tables or folders.
Column-level.GRANT SELECT on a column list.Role that hides columns.
Row-level.Predicate function plus security policy.Role with a SQL WHERE rule.
Masking.Dynamic data masking.Not supported.
Files and folders.Not available.Role scoped to folders.

Object-level security (OLS) controls access to whole objects, such as tables or folders. You'll meet each row of this table below.

[202][205][208]

Rendering diagram…
T-SQL rules guard only the SQL door. OneLake security roles guard the data itself, for every engine.

[208]

[208]

Object-level security: GRANT, DENY, REVOKE

Exam depth: know it cold.

Use T-SQL to control which schemas, tables and views a person can query.

  1. Share the Warehouse with no extra permissions. That gives Read, so the person can connect but query nothing.
  2. GRANT the objects they need. DENY explicitly blocks a permission. REVOKE removes an earlier GRANT or DENY.

You can't run CREATE USER in Fabric. The first GRANT or DENY creates the database user for you. Granting to roles or groups, not individuals, keeps this manageable.

[195][199][201][203][204]

sql
-- Ops analysts read every table and view in the reporting schema
GRANT SELECT ON SCHEMA::reporting TO [ops.analyst@northwindrides.com];
-- Support agents read one view, not the tables behind it
GRANT SELECT ON OBJECT::dbo.vw_TripSummary TO [support.agent@northwindrides.com];
-- Explicitly block the raw payments table for support
DENY SELECT ON OBJECT::dbo.Payments TO [support.agent@northwindrides.com];
-- REVOKE removes an earlier GRANT or DENY
REVOKE SELECT ON OBJECT::dbo.Payments FROM [support.agent@northwindrides.com];
Object-level security in the Northwind Warehouse.

[201][203]

You are a data engineer at Northwind Rides. Support agents must query only the view dbo.vw_TripSummary in the Warehouse, not its base tables. You need least privilege. What should you do?

Pick one answer.

[195][201]

Column-level security

Exam depth: know it cold.

Column-level security (CLS) limits which columns of a table a person can read. In a Warehouse you write it as a GRANT with a column list.

A query that names a blocked column fails with an error. That includes SELECT *. The person must list only allowed columns.

The rule lives in the database, so it holds in every tool, Power BI included. Direct Lake reports fall back to DirectQuery, querying the Warehouse live, to respect it.

[199]

sql
-- Support may read these columns of Customers, and no others
GRANT SELECT ON dbo.Customers
(CustomerID, FirstName, City, Email) TO [support.agent@northwindrides.com];
-- As the agent: fails with Msg 230, SELECT denied on column 'CardNumber'
SELECT * FROM dbo.Customers;
-- As the agent: works
SELECT CustomerID, FirstName, City FROM dbo.Customers;
Column-level security: support can't read CardNumber.

[199]

Row-level security: a function and a policy

Exam depth: know it cold.

Row-level security (RLS) limits which rows of a table a person can see. In a Warehouse it takes two pieces.

  1. A predicate function: a true-or-false test, written as a function that returns a one-row table when a row is allowed. WITH SCHEMABINDING locks it to the objects it uses.
  2. A security policy: CREATE SECURITY POLICY binds that function to a table as a filter predicate.

Filter predicates silently hide rows from SELECT, UPDATE and DELETE. INSERT still works. The policy applies to everyone, even the table owner. So write the function to let admins through if they need all rows. Creating a policy needs ALTER ANY SECURITY POLICY.

[198]

sql
CREATE SCHEMA Security;
GO
-- 1. Predicate function: returns a row (allow) when the test is true
CREATE FUNCTION Security.fn_station_filter(@ManagerEmail AS varchar(100))
RETURNS TABLE
WITH SCHEMABINDING
AS
RETURN SELECT 1 AS fn_result
WHERE @ManagerEmail = USER_NAME()
OR USER_NAME() = '[email protected]';
GO
-- 2. Security policy: binds the function to the table
CREATE SECURITY POLICY StationFilter
ADD FILTER PREDICATE Security.fn_station_filter(ManagerEmail)
ON dbo.StationRevenue
WITH (STATE = ON);
GO
Each station manager sees only their own station's rows. The finance lead sees all.

[198]

Predict, then reveal

A colleague runs ALTER FUNCTION on Security.fn_station_filter to add a second manager. It fails with an error. Why, and what's the fix?

[198]

Masking hides values on screen. It doesn't stop a clever WHERE clause. Picture: Three-panel comic strip, cartoon style with soft colors.

Dynamic data masking

Exam depth: know it cold.

Dynamic data masking (DDM) shows a disguised value in query results. The stored data doesn't change. It works in a Warehouse and a SQL analytics endpoint.

FunctionWhat a masked user sees
default().XXXX for text, 0 for numbers, 1900-01-01 for dates.
email().First letter, then [email protected].
random(start, end).A random number in that range, numeric columns only.
partial(prefix, padding, suffix).The first and last characters you choose, with padding between.

To see real values, a user needs UNMASK, or CONTROL (full control) on the database. Fabric's examples grant UNMASK on a whole table. Admins, Members and Contributors have CONTROL, so they see real values by default. Adding a mask needs ALTER ANY MASK.

[200][300]

sql
ALTER TABLE dbo.Customers
ALTER COLUMN Email ADD MASKED WITH (FUNCTION = 'email()'); -- [email protected]
ALTER TABLE dbo.Customers
ALTER COLUMN Phone ADD MASKED WITH (FUNCTION = 'partial(0,"XXX-XXX-",4)'); -- XXX-XXX-4567
ALTER TABLE dbo.Customers
ALTER COLUMN BirthDate ADD MASKED WITH (FUNCTION = 'default()'); -- 1900-01-01
ALTER TABLE dbo.Payments
ALTER COLUMN TipAmount ADD MASKED WITH (FUNCTION = 'random(1, 10)'); -- a random 1-10
-- The auditor may see real values in Customers
GRANT UNMASK ON dbo.Customers TO [auditor@northwindrides.com];
All four masking functions, and UNMASK on one table.

[200][300]

[200]

You are a data engineer at Northwind Rides. Support agents must confirm a caller by the last four digits of their phone number. They must never read card numbers at all. What should you configure?

Pick one answer.

[199][200]

OneLake security: folders, tables, rows and columns

Exam depth: know it cold.

For a Lakehouse, use OneLake security roles. OneLake security is generally available in Fabric engines such as Spark and the SQL analytics endpoint. Each role has a permission, the data it covers, and members.

  • Data in role: whole schemas, tables or folders. This is how you secure files and folders. Access to a folder covers everything under it.
  • Row security: a rule like SELECT * FROM dbo.trips WHERE City = 'Seattle'.
  • Column security: set a column to Hide.
  • Permission: Read, or ReadWrite for Lakehouses. A ReadWrite role can't hold row or column rules.

A person in two roles gets the union of both: anything either role allows. Row rules combine with OR. Keep a table's row and column rules in one role. Two roles that split them cause query errors.

[197][205][206][207][299]

A OneLake security role named CLSRole with Read permission and Grant type. Under Data in role, the menu next to the publicholidays table is open, showing Row security and Column security.
Microsoft Learn, CC BY 4.0

[206]

[205][206][208]

You are a data engineer at Northwind Rides. A partner has Read on the Lakehouse. They must read only the Files/maintenance folder, from Spark and from the SQL endpoint. What should you do?

Pick one answer.

[205][206]

Try it in Fabric

A free Fabric trial is enough. Use a Lakehouse with at least one table and one folder.

  1. Open the Lakehouse and select Manage OneLake security.
  2. On the All Lakehouse roles tab, select New.
  3. Enter a role name with letters and numbers only, such as SupportSeattle. Keep Read. Select Next.
  4. Choose Selected data, then Edit. Tick one table and one folder. Select Add data, then Next.
  5. Add a test member and select Create.
  6. Open the role. Next to the table, select ... then Row security. Enter a SELECT * FROM ... WHERE rule and select Save.

[206]

Exam-style practice

You are a data engineer at Northwind Rides. Columns in dbo.Customers have masks. An auditor with the Viewer role must see real values in that table. Other masked tables must stay masked for them. What should you run?

Pick one answer.

[200][300]

You are a data engineer at Northwind Rides. A Warehouse table has an RLS security policy. A Spark user reads the same table through a OneLake shortcut in their Lakehouse and sees all rows. You need to explain why. What is the cause?

Pick one answer.

[208]

FlashcardsIndex cardsFlashcards ⚡

Card 1 of 15

[197][198][199][200][201][205][208]

Worth keeping

  1. Warehouse: GRANT/DENY for objects and columns, a function plus policy for rows.
  2. Masking disguises results but isn't encryption; UNMASK or CONTROL shows real values.
  3. Lakehouse folders, tables, rows and columns: OneLake security roles, minus DefaultReader.
  4. T-SQL rules guard only the SQL endpoint; OneLake roles guard every engine.

Your rules now decide who sees each row. But how does someone in the OneLake catalog know a table holds card data, or which copy to trust? 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 (18)

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

  1. [4]Microsoft Learn: Study Guide for Exam DP-700
  2. [167]Microsoft Learn: Share items in Microsoft Fabric
  3. [195]Microsoft Learn: Share your data and manage permissions
  4. [196]Microsoft Learn: Data security in OneLake
  5. [197]Microsoft Learn: How OneLake security controls data access
  6. [198]Microsoft Learn: Row-level security in Fabric Data Warehouse
  7. [199]Microsoft Learn: Column-level security in Fabric Data Warehouse
  8. [200]Microsoft Learn: Dynamic data masking in Fabric Data Warehouse
  9. [201]Microsoft Learn: SQL granular permissions in Microsoft Fabric
  10. [202]Microsoft Learn: Secure your Fabric Data Warehouse
  11. [203]Microsoft Learn: Permissions: GRANT, DENY, REVOKE
  12. [204]Microsoft Learn: DENY (Transact-SQL)
  13. [205]Microsoft Learn: Table, column, and row-level security in OneLake
  14. [206]Microsoft Learn: Create and manage OneLake security roles
  15. [207]Microsoft Learn: Row-level security syntax reference
  16. [208]Microsoft Learn: OneLake security for SQL analytics endpoints
  17. [299]Microsoft Learn: Read data secured with OneLake security
  18. [300]Microsoft Learn: How to implement dynamic data masking in Fabric Data Warehouse