Microsoft Fabric
Fabric Security Lab: Workspace Roles, Data Access, RLS and Masking
A hands-on Microsoft Fabric security lab: test workspace roles with two identities, share items with exact permissions, implement row-, column- and object-level security in a Warehouse, restrict Lakehouse folders with OneLake security, mask a column, then break the model on purpose and diagnose it.
On this page
Configuring a security control is the easy half. The hard half is proving what a specific person can actually read, through which engine, and why. This lab builds a small Fabric workspace, secures it with every data-access control a data engineer is expected to use, and then tests each control as a second identity, including the paths the control does not cover.
It is the hands-on companion to Microsoft Fabric Security Architecture for Production Data Platforms. That article explains the model and the design decisions; this lab makes you prove them.
What you will prove
Microsoft documents each of these statements. By the end you will have your own evidence for them, or a documented difference:
- A workspace role grants access to every item in the workspace, through every engine.
- An item share can grant much less than a role, and the extra permissions decide which engines can read data.
- Warehouse row-, column- and object-level security filter T-SQL queries, and do not filter the same Delta files read through OneLake.
- OneLake security roles restrict folders for readers, and the default reader role can silently undo that.
- Dynamic data masking changes what a query returns, not what is stored, and privileged users see through it.
- Workspace roleAdmin, Member, Contributor, Viewer; applies to every item
- Exercise 1
- Item permissionRead, ReadData, ReadAll, Write; one item at a time
- Exercise 2
- Engine data securityFilters what a permitted user reads, per engine
- Warehouse SQLRLS, CLS, object grants, masking
- OneLake securityFolder and table roles
Prerequisites
- A Fabric workspace you can create, on a trial or test capacity. You need the Admin role in it.
- Two test identities in the same Microsoft Entra tenant, called here
lab.analystandlab.engineer. Use real test accounts; every check in this lab depends on signing in as someone else. A second browser profile or a private window for each identity makes switching easy. - The
customers.csvfile from the Lab 01 sample dataset. It is synthetic: 50 customers in six US states, withexample.comemail addresses. - Basic T-SQL. If you have never created a Fabric Warehouse, do Create Your First Fabric Warehouse with T-SQL first.
Replace yourtenant.onmicrosoft.com in every script with your own domain, and use each test user’s exact user principal name.
Lab setup
Create these items. The names are used in every script below.
| Item | Name | Purpose |
|---|---|---|
| Workspace | ws_security_lab |
The boundary for workspace roles |
| Lakehouse (schemas enabled) | lh_security_lab |
Files for OneLake folder security; source table |
| Warehouse | wh_security_lab |
T-SQL security and masking |
| Notebook | nb_security_checks |
Loading data and testing the Spark path |
1. Land the file. In lh_security_lab, create two folders under Files: landing and restricted. Upload customers.csv into landing. Upload any small text file (for example restricted/notes.txt containing one line) into restricted.
2. Load the Lakehouse table. In nb_security_checks, attached to lh_security_lab, run:
df = (spark.read
.option("header", True)
.option("inferSchema", True)
.csv("Files/landing/customers.csv"))
df.write.mode("overwrite").saveAsTable("dbo.customers")
print(df.count()) # expect 503. Copy it into the Warehouse. Open wh_security_lab, start a new SQL query and use a cross-database query to the Lakehouse’s SQL analytics endpoint in the same workspace:
CREATE TABLE dbo.customers AS
SELECT customer_id, first_name, last_name, email, segment, city, [state], country
FROM lh_security_lab.dbo.customers;
SELECT [state], COUNT(*) AS customers
FROM dbo.customers
GROUP BY [state]
ORDER BY customers DESC;Expected result: 50 rows in total, with FL (21) and TX (11) the largest states, as in the sample file. Keep this result: it is the baseline you will compare every filtered query against.
Exercise 1: Workspace roles
Objective: see what a role grants across items and engines.
- In
ws_security_lab, select Manage access and addlab.analystas Viewer andlab.engineeras Contributor. - Sign in as each identity and fill in this test matrix. Record what happened, not what you expected.
| Test | Viewer (lab.analyst) |
Contributor (lab.engineer) |
|---|---|---|
| See every item in the workspace | ||
Query dbo.customers in the Warehouse with T-SQL |
||
Browse Files/landing in the Lakehouse explorer |
||
| Create a new item | ||
| Add another user to the workspace |
Expected result: both see every item. Both can query the Warehouse with T-SQL: Microsoft documents that the Viewer role grants CONNECT and ReadData on every Warehouse and SQL analytics endpoint in the workspace (workspace roles in Fabric Data Warehouse). Only the Contributor can browse Lakehouse files and create items, and neither can add users; a Viewer reads OneLake data only when a OneLake security role grants it, which is Exercise 5 (workspace role table).
Evidence to keep: the filled matrix. Notice that you never chose an item: the role applied to all of them.
Exercise 2: Item-level access
Objective: grant one item, with exactly the permissions needed, and nothing else.
-
As Admin, remove
lab.analystfrom the workspace roles. -
Share
wh_security_labwithlab.analystand leave every additional permission unticked. The share always includes Read. -
As
lab.analyst, open the Warehouse from the OneLake catalog and run:SELECT TOP (5) * FROM dbo.customers;T-SQL (Fabric Warehouse)1 lineRead-only query display. It is not connected to a database.Expected result: the connection works, but the query fails with a
SELECTpermission error. Read lets you connect; reading data needsReadDataor SQL grants (item permission model). -
As Admin, grant access to one table in T-SQL instead of ticking “read all data”:
GRANT SELECT ON dbo.customers TO [lab.analyst@yourtenant.onmicrosoft.com];T-SQL (Fabric Warehouse)1 lineRead-only query display. It is not connected to a database. -
Rerun the query as
lab.analyst. Expected result: 5 rows. -
Share
lh_security_labwithlab.analyst, again with no additional permissions. Aslab.analyst, open it. Expected result: the item opens, but its files and tables aren’t readable.
| Permission on the share | What it adds | Engine |
|---|---|---|
| Read (always) | Open the item, connect to the SQL endpoint | None for data |
| Read all data through SQL (ReadData) | SELECT on all tables |
Warehouse or SQL analytics endpoint |
| Read all with Apache Spark (ReadAll) | Read the data in OneLake | Spark, OneLake APIs, Lakehouse explorer |
| Write / Edit | Change the item | All |
Evidence to keep: the failed and successful queries, with the time of each. You now have a user with access to one table in one item, and no workspace role.
Exercise 3: Row-level security
Objective: filter rows by user in T-SQL, test it as two people, then find the path it doesn’t cover.
-
As Admin, in
wh_security_lab, create a mapping table and the security policy. Put your own account in the mapping with*: Warehouse security policies apply to every user, including Admins and the database owner, so without that row you would filter yourself out.CREATE SCHEMA rls; GO CREATE TABLE rls.state_access ( user_upn varchar(256) NOT NULL, [state] varchar(2) NOT NULL ); INSERT INTO rls.state_access (user_upn, [state]) VALUES ('lab.analyst@yourtenant.onmicrosoft.com', 'FL'), ('you@yourtenant.onmicrosoft.com', '*'); GO CREATE FUNCTION rls.fn_state_filter(@state AS varchar(2)) RETURNS TABLE WITH SCHEMABINDING AS RETURN SELECT 1 AS allowed FROM rls.state_access AS a WHERE a.user_upn = USER_NAME() AND (a.[state] = @state OR a.[state] = '*'); GO CREATE SECURITY POLICY rls.customer_state_policy ADD FILTER PREDICATE rls.fn_state_filter([state]) ON dbo.customers WITH (STATE = ON); GOT-SQL (Fabric Warehouse)28 linesRead-only query display. It is not connected to a database.Notes
- line 21USER_NAME() returns the signed-in user's name as the Warehouse sees it, which is what the mapping table must store.
-
As
lab.analyst, run:SELECT USER_NAME() AS who_am_i; SELECT [state], COUNT(*) AS customers FROM dbo.customers GROUP BY [state];T-SQL (Fabric Warehouse)2 linesRead-only query display. It is not connected to a database.Expected result: one row,
FL, 21 customers. The analyst doesn’t needSELECTonrls.state_access: with a schema-bound policy (the default), those permission checks are bypassed (row-level security). -
Run the same query as yourself. Expected result: all six states, 50 customers.
-
As
lab.engineer(Contributor), run it in T-SQL. Expected result: no rows, because the engineer isn’t in the mapping table and Microsoft documents that security policies apply to all users, including the database owner and workspace Admins, Members and Contributors. -
Now, still as
lab.engineer, read the same table fromnb_security_checksthrough OneLake:path = ("abfss://ws_security_lab@onelake.dfs.fabric.microsoft.com/" "wh_security_lab.Warehouse/Tables/dbo/customers") spark.read.format("delta").load(path).groupBy("state").count().show()PySpark3 linesRead-only example cell. Nothing on this page runs code.Expected result: all 50 customers. The T-SQL security policy filters queries through the Warehouse engine. Microsoft documents that Warehouse SQL security (RLS, CLS, object permissions) is enforced only in the Warehouse’s SQL context and isn’t translated into OneLake security (Warehouse SQL security and shortcuts), and a Contributor has read access to all OneLake data in the workspace.
Evidence to keep: three query results for the same table (analyst, Admin, engineer in T-SQL) and the engineer’s Spark result. The last one matters most: it shows, or disproves, that the SQL policy doesn’t cover the OneLake path.
Exercise 4: Column- and object-level access
Objective: restrict columns and objects with T-SQL permissions in the Warehouse.
-
As Admin, replace the analyst’s table-wide grant with a column list that leaves out
last_nameandemail:REVOKE SELECT ON dbo.customers FROM [lab.analyst@yourtenant.onmicrosoft.com]; GRANT SELECT ON dbo.customers (customer_id, first_name, segment, city, [state], country) TO [lab.analyst@yourtenant.onmicrosoft.com];T-SQL (Fabric Warehouse)4 linesRead-only query display. It is not connected to a database. -
As
lab.analyst:SELECT * FROM dbo.customers; -- expected to fail: column permission denied SELECT customer_id, first_name, [state] FROM dbo.customers; -- expected to work, filtered to FL by RLST-SQL (Fabric Warehouse)2 linesRead-only query display. It is not connected to a database.Expected result:
SELECT *fails with aSELECTpermission error on a column the analyst can’t read; Microsoft’s example shows error 230 (column-level security). Column-level security doesn’t hide columns quietly; it rejects the query. -
Object-level: give the analyst a summary view instead of a table.
CREATE SCHEMA reporting; GO CREATE VIEW reporting.v_customers_by_segment AS SELECT [state], segment, COUNT(*) AS customers FROM dbo.customers GROUP BY [state], segment; GO GRANT SELECT ON reporting.v_customers_by_segment TO [lab.analyst@yourtenant.onmicrosoft.com];T-SQL (Fabric Warehouse)8 linesRead-only query display. It is not connected to a database. -
As
lab.analyst, query the view, then trySELECT * FROM rls.state_access;. Expected result: the view returns FL rows only, because the security policy filters every read of the table, and the mapping table is denied.
Evidence to keep: the column permission error and the view result. Note which control produced which effect: object grants decide which objects, column grants decide which columns, the security policy decides which rows.
Exercise 5: Folder-level access with OneLake security
Objective: let a reader see one Lakehouse folder and not another, and prove it.
OneLake security roles grant data access to users who have only Read on the item or the Viewer workspace role. They don’t restrict Admins, Members or Contributors, who already have full data access.
- As Admin, open
lh_security_laband select Manage OneLake security. - Create a role named
LandingReaderswith Read permission, Selected data, and select only theFiles/landingfolder. Addlab.analystas a member. - Wait about five minutes: role changes take that long to apply.
- As
lab.analyst, open the Lakehouse explorer. Expected result:landingand itscustomers.csvare visible;restrictedisn’t. Microsoft documents that folder permissions inherit downward and let members traverse parent folders without seeing siblings (folder inheritance and traversal). - As Admin, open Manage OneLake security → All members in roles, select
lab.analystand review Roles and Granted data. This is your effective-access evidence: one role, one folder.
Why the Warehouse handles masking and the Lakehouse handles folders here: a Lakehouse SQL analytics endpoint enforces either SQL permissions (delegated identity mode, the default) or OneLake security roles (user identity mode), not both. Dynamic data masking isn’t supported through OneLake security, so the masking exercise uses the Warehouse.
Exercise 6: Dynamic data masking
Objective: mask a column, check who sees what, and understand what masking doesn’t protect.
-
As Admin, give the analyst the
emailcolumn back, then mask it:GRANT SELECT ON dbo.customers (email) TO [lab.analyst@yourtenant.onmicrosoft.com]; ALTER TABLE dbo.customers ALTER COLUMN email ADD MASKED WITH (FUNCTION = 'email()');T-SQL (Fabric Warehouse)4 linesRead-only query display. It is not connected to a database. -
As
lab.analyst:SELECT customer_id, email FROM dbo.customers;Expected result: values in the documentedemail()mask format, such asnXXX@XXXX.com, still FL only. -
Run the same query as yourself. Expected result: real email addresses. Microsoft documents that Admins, Members and Contributors have
CONTROLon the Warehouse, which includesUNMASK(permissions for dynamic data masking). To check it for the Contributor too, add('lab.engineer@yourtenant.onmicrosoft.com', 'TX')torls.state_accessand query aslab.engineer; the expected result is real addresses for the TX rows. -
As
lab.analyst, run an inference query:SELECT customer_id, email FROM dbo.customers WHERE email LIKE 'noah%';T-SQL (Fabric Warehouse)3 linesRead-only query display. It is not connected to a database.Expected result: the emails are still masked, but the rows returned tell the analyst which customers’ real emails start with
noah. Microsoft describes this inference risk explicitly. Masking limits accidental exposure; it doesn’t stop a user with query access from inferring values. -
Grant and remove the right to see real values:
GRANT UNMASK ON dbo.customers (email) TO [lab.analyst@yourtenant.onmicrosoft.com]; -- query again as lab.analyst: real values REVOKE UNMASK ON dbo.customers (email) FROM [lab.analyst@yourtenant.onmicrosoft.com];T-SQL (Fabric Warehouse)3 linesRead-only query display. It is not connected to a database.
Troubleshooting note: “my mask doesn’t work” almost always means the tester is an Admin, Member or Contributor. Test masking as a user with only item access and explicit grants.
Exercise 7: Break and fix
Create each mismatch on purpose, observe the symptom as the affected user, then diagnose it from the evidence before you fix it.
Break A: the folder restriction that stopped working
- As Admin, edit the analyst’s Lakehouse share and add Read all with Apache Spark (ReadAll).
- Wait five minutes. As
lab.analyst, open the Lakehouse explorer.
Expected symptom: restricted and its file become visible, although LandingReaders still grants only landing.
Diagnose: in Manage OneLake security → All members in roles, open lab.analyst and look at Roles. You should find the analyst is now also a member of DefaultReader, the built-in role that gives users with ReadAll access to the Lakehouse’s data. Microsoft warns that users added to a restricted role keep full access unless they are removed from DefaultReader (create and manage roles), and OneLake security combines roles as a union, so the broadest role wins.
Fix: remove ReadAll from the share (or edit DefaultReader so it no longer covers everyone with ReadAll), then check Granted data again.
Break B: row-level security that returns nothing
-
As Admin, change the analyst’s mapping row to a value that isn’t exactly what
USER_NAME()returns, such as a mail alias or a different spelling:UPDATE rls.state_access SET user_upn = 'analyst.alias@yourtenant.onmicrosoft.com' WHERE [state] = 'FL';T-SQL (Fabric Warehouse)3 linesRead-only query display. It is not connected to a database. -
As
lab.analyst, querydbo.customers.
Expected symptom: zero rows and no error. Microsoft documents that filter predicates silently remove rows; if every row is filtered, the query returns an empty set.
Diagnose: run SELECT USER_NAME(); as the analyst and compare it with rls.state_access. New Warehouses use the case-sensitive collation Latin1_General_100_BIN2_UTF8 unless the workspace default was changed (Warehouse collation), so even a difference in capitalization breaks the match.
Fix: correct the row. If you change the predicate function instead, for example to compare lowercase values, drop the security policy first: a function bound to a schema-bound policy can’t be altered.
Evidence to keep: for each break, one line each for symptom, evidence, cause and fix. That is the format of a real access incident.
Exercise 8: Explain
Write the answer to this question in one paragraph, without notes:
Why is a workspace role not always enough to represent the complete data-security model?
A good answer includes these points, in your own words:
- A role applies to every item in the workspace and every engine. Admin, Member and Contributor can read all data, including through Spark and OneLake, whatever T-SQL rules say (Exercise 3, step 5).
- Data-level controls are engine-specific. Warehouse RLS, CLS, object grants and masking protect T-SQL queries; OneLake security protects OneLake paths for readers; neither is translated into the other.
- Narrow access needs item permissions plus data rules, tested as the user who receives them (Exercises 2–6).
- Broad grants combine: ReadAll brings
DefaultReaderback (Break A), and a silent filter mismatch looks like “no data” rather than an error (Break B). - So the security model is the set of paths to the data and what protects each one, which is the subject of the security architecture guide.
- dbo.customersDelta table in OneLake
- Analyst, T-SQL
- Item Read + grants
- RLS, CLS, masking applied
- Admin, T-SQL
- RLS applied, * mapping row
- Masking not applied
- Engineer, T-SQL
- Not in mapping
- Zero rows
- Engineer, Spark
- Contributor role
- All 50 rows
Cleanup
- Remove both test users from any shares and roles you created, or simply delete
ws_security_lab, which removes its items, roles and policies. - If you added the test identities to groups for this lab, remove them.
- Keep your evidence: the Exercise 1 matrix, the four Exercise 3 results, the error 230 message, the masked and unmasked results, and the two break/fix write-ups.
What this lab doesn’t cover
- OneLake row- and column-level security on Lakehouse tables, enforced through the SQL analytics endpoint in user identity mode. Switching an endpoint’s mode briefly makes the workspace’s SQL endpoints unavailable and drops existing SQL roles, so try it in its own workspace.
- Semantic model RLS and object-level security for report readers.
- Sensitivity labels and endorsement, which classify and signal trust rather than grant or restrict access. See Microsoft Fabric Governance with Microsoft Purview.
- Workspace settings that change behaviour rather than access, such as OneLake diagnostics for auditing reads. See the Microsoft Fabric Workspace Settings Cheat Sheet.
Next, take this workspace through Dev and Test with Deploy a Fabric Solution with Git, Deployment Pipelines and Database Projects. Microsoft documents that Warehouse deployments exclude users and permissions on purpose, so the security you build here doesn’t travel with a deployment.
Sources
Last verified against Microsoft Learn: November 2026.
- Data security overview (OneLake): control plane and data plane, workspace roles, item permissions, DefaultReader.
- OneLake security roles, permissions and scopes: folder inheritance, role union, latency.
- Create and manage OneLake security roles: Manage OneLake security, view access by member.
- OneLake security for SQL analytics endpoints: user identity vs delegated mode, Warehouse security through shortcuts.
- Share items in Microsoft Fabric: item permission model.
- Workspace roles in Fabric Data Warehouse: Viewer
CONNECTandReadData,CONTROLfor Admin, Member and Contributor. - Data Warehouse collation: case-sensitive default.
- Row-level security in Fabric Data Warehouse and implement row-level security.
- Column-level security in Fabric Data Warehouse.
- Dynamic data masking in Fabric Data Warehouse.
Was this helpful?
What could be better?
Thanks — that helps me improve Javi Builds.
Continue learning
- Cheat sheetMicrosoft Fabric Workspace Settings Cheat SheetThe Microsoft Fabric workspace settings that change how a data engineering workspace behaves: Spark pools, environments and jobs, domain assignment, OneLake shortcut caching and diagnostics, and data workflow (Apache Airflow job) runtime pools, with what to verify for each.Open cheat sheet
- ResourceMicrosoft Fabric Certification Study PlannerAn adaptable 6–8 week example plan that connects official Fabric certification objectives with hands-on practice, review, and exam readiness.View resource
What should I build next?
I'm building Javi Builds around practical problems data engineers actually face. Tell me what you'd like to see next.
Stay in the loop
Get the Javi Builds updates you actually care about.
Check your inbox
I sent you a confirmation link. Your subscription will be active after you confirm it.