All 33 lessons
Start here: cloud and data basics
- 1. Start here: the exam, the plan and what you already know
- 2. Cloud in plain words: SaaS, PaaS, IaaS and where Fabric fits
- 3. Data basics: files, tables, formats and the lakehouse idea
- 4. A tour of Microsoft Fabric: OneLake, workspaces and items
- 5. The three languages: SQL, PySpark and KQL side by side
- 6. Getting data in: the map of Fabric tools
Store, ingest and transform batch data (exam area 2, 30-35%)
- 7. Choosing a data store and a transformation tool
- 8. Shortcuts and mirroring: use data without copying it
- 9. Ingest data with pipelines and Copy job
- 10. Transform with PySpark and SQL: join, denormalize, aggregate
- 11. Messy data: duplicates, missing values and late arrivals
- 12. Full and incremental loads, and loading a star schema
Orchestrate and stream (exam areas 1 and 2)
Set up and secure a Fabric solution (exam area 1, 30-35%)
- 18. Workspace settings: Spark, domains, OneLake and data workflows
- 19. Version control with Git and database projects
- 20. Deployment pipelines: development, test and production
- 21. Who can see what: workspace roles and item permissions
- 22. Fine-grained security: rows, columns, objects, files and masking
- 23. Governance: sensitivity labels and endorsement
Monitor, fix and optimize (exam area 3, 30-35%)
- 24. Monitoring hub, alerts and Activator
- 25. Fixing errors in pipelines, dataflows and notebooks
- 26. Fixing errors in Eventhouse, Eventstream and T-SQL
- 27. Optimize lakehouse tables: V-Order, OPTIMIZE, VACUUM and partitions
- 28. Optimize Spark, pipelines and warehouses
- 29. Optimize eventstreams, eventhouses and queries
Exam practice and final review
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.
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.
| Control | Warehouse 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.
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.
- Share the Warehouse with no extra permissions. That gives Read, so the person can connect but query nothing.
- 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.
-- Ops analysts read every table and view in the reporting schemaGRANT SELECT ON SCHEMA::reporting TO [ops.analyst@northwindrides.com];-- Support agents read one view, not the tables behind itGRANT SELECT ON OBJECT::dbo.vw_TripSummary TO [support.agent@northwindrides.com];-- Explicitly block the raw payments table for supportDENY SELECT ON OBJECT::dbo.Payments TO [support.agent@northwindrides.com];-- REVOKE removes an earlier GRANT or DENYREVOKE SELECT ON OBJECT::dbo.Payments FROM [support.agent@northwindrides.com];
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.
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.
-- Support may read these columns of Customers, and no othersGRANT 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: worksSELECT CustomerID, FirstName, City FROM dbo.Customers;
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.
- A predicate function: a true-or-false test, written as a function that returns a one-row table when a row is allowed.
WITH SCHEMABINDINGlocks it to the objects it uses. - A security policy:
CREATE SECURITY POLICYbinds 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.
CREATE SCHEMA Security;GO-- 1. Predicate function: returns a row (allow) when the test is trueCREATE FUNCTION Security.fn_station_filter(@ManagerEmail AS varchar(100))RETURNS TABLEWITH SCHEMABINDINGASRETURN SELECT 1 AS fn_resultWHERE @ManagerEmail = USER_NAME()GO-- 2. Security policy: binds the function to the tableCREATE SECURITY POLICY StationFilterADD FILTER PREDICATE Security.fn_station_filter(ManagerEmail)ON dbo.StationRevenueWITH (STATE = ON);GO
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?
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.
| Function | What 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.
ALTER TABLE dbo.CustomersALTER TABLE dbo.CustomersALTER COLUMN Phone ADD MASKED WITH (FUNCTION = 'partial(0,"XXX-XXX-",4)'); -- XXX-XXX-4567ALTER TABLE dbo.CustomersALTER COLUMN BirthDate ADD MASKED WITH (FUNCTION = 'default()'); -- 1900-01-01ALTER TABLE dbo.PaymentsALTER COLUMN TipAmount ADD MASKED WITH (FUNCTION = 'random(1, 10)'); -- a random 1-10-- The auditor may see real values in CustomersGRANT UNMASK ON dbo.Customers TO [auditor@northwindrides.com];
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.
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.
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.
Try it in Fabric
A free Fabric trial is enough. Use a Lakehouse with at least one table and one folder.
- Open the Lakehouse and select Manage OneLake security.
- On the All Lakehouse roles tab, select New.
- Enter a role name with letters and numbers only, such as SupportSeattle. Keep Read. Select Next.
- Choose Selected data, then Edit. Tick one table and one folder. Select Add data, then Next.
- Add a test member and select Create.
- Open the role. Next to the table, select ... then Row security. Enter a
SELECT * FROM ... WHERErule and select Save.
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.
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.
Worth keeping
- Warehouse: GRANT/DENY for objects and columns, a function plus policy for rows.
- Masking disguises results but isn't encryption; UNMASK or CONTROL shows real values.
- Lakehouse folders, tables, rows and columns: OneLake security roles, minus DefaultReader.
- 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.
- [4]Microsoft Learn: Study Guide for Exam DP-700
- [167]Microsoft Learn: Share items in Microsoft Fabric
- [195]Microsoft Learn: Share your data and manage permissions
- [196]Microsoft Learn: Data security in OneLake
- [197]Microsoft Learn: How OneLake security controls data access
- [198]Microsoft Learn: Row-level security in Fabric Data Warehouse
- [199]Microsoft Learn: Column-level security in Fabric Data Warehouse
- [200]Microsoft Learn: Dynamic data masking in Fabric Data Warehouse
- [201]Microsoft Learn: SQL granular permissions in Microsoft Fabric
- [202]Microsoft Learn: Secure your Fabric Data Warehouse
- [203]Microsoft Learn: Permissions: GRANT, DENY, REVOKE
- [204]Microsoft Learn: DENY (Transact-SQL)
- [205]Microsoft Learn: Table, column, and row-level security in OneLake
- [206]Microsoft Learn: Create and manage OneLake security roles
- [207]Microsoft Learn: Row-level security syntax reference
- [208]Microsoft Learn: OneLake security for SQL analytics endpoints
- [299]Microsoft Learn: Read data secured with OneLake security
- [300]Microsoft Learn: How to implement dynamic data masking in Fabric Data Warehouse