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.

By JaviPublished 22 min readIntermediate

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:

  1. A workspace role grants access to every item in the workspace, through every engine.
  2. An item share can grant much less than a role, and the extra permissions decide which engines can read data.
  3. Warehouse row-, column- and object-level security filter T-SQL queries, and do not filter the same Delta files read through OneLake.
  4. OneLake security roles restrict folders for readers, and the default reader role can silently undo that.
  5. Dynamic data masking changes what a query returns, not what is stored, and privileged users see through it.
  1. Workspace roleAdmin, Member, Contributor, Viewer; applies to every item
    • Exercise 1
  2. Item permissionRead, ReadData, ReadAll, Write; one item at a time
    • Exercise 2
  3. Engine data securityFilters what a permitted user reads, per engine
    • Warehouse SQLRLS, CLS, object grants, masking
    • OneLake securityFolder and table roles
The three layers you will test. Each layer narrows access only for the people the layer applies to. Workspace Admins, Members and Contributors already pass the bottom two layers on most paths, which is why the lab tests with a second, less privileged identity.

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.analyst and lab.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.csv file from the Lab 01 sample dataset. It is synthetic: 50 customers in six US states, with example.com email 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:

PySpark
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 50
PySpark7 linesRead-only example cell. Nothing on this page runs code.

3. 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:

SQL query: Lab setupT-SQL (Fabric Warehouse)
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;
T-SQL (Fabric Warehouse)8 linesRead-only query display. It is not connected to a database.

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.

  1. In ws_security_lab, select Manage access and add lab.analyst as Viewer and lab.engineer as Contributor.
  2. 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.

  1. As Admin, remove lab.analyst from the workspace roles.

  2. Share wh_security_lab with lab.analyst and leave every additional permission unticked. The share always includes Read.

  3. As lab.analyst, open the Warehouse from the OneLake catalog and run:

    SQL query: Exercise 2: Item-level accessT-SQL (Fabric Warehouse)
    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 SELECT permission error. Read lets you connect; reading data needs ReadData or SQL grants (item permission model).

  4. As Admin, grant access to one table in T-SQL instead of ticking “read all data”:

    SQL query: Exercise 2: Item-level access (2)T-SQL (Fabric Warehouse)
    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.
  5. Rerun the query as lab.analyst. Expected result: 5 rows.

  6. Share lh_security_lab with lab.analyst, again with no additional permissions. As lab.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.

  1. 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.

    SQL query: Row-level security setupT-SQL (Fabric Warehouse)
    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);
    GO
    T-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.
  2. As lab.analyst, run:

    SQL query: Exercise 3: Row-level securityT-SQL (Fabric Warehouse)
    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 need SELECT on rls.state_access: with a schema-bound policy (the default), those permission checks are bypassed (row-level security).

  3. Run the same query as yourself. Expected result: all six states, 50 customers.

  4. 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.

  5. Now, still as lab.engineer, read the same table from nb_security_checks through OneLake:

    PySpark
    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.

  1. As Admin, replace the analyst’s table-wide grant with a column list that leaves out last_name and email:

    SQL query: Exercise 4: Column- and object-level accessT-SQL (Fabric Warehouse)
    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.
  2. As lab.analyst:

    SQL query: Exercise 4: Column- and object-level access (2)T-SQL (Fabric Warehouse)
    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 RLS
    T-SQL (Fabric Warehouse)2 linesRead-only query display. It is not connected to a database.

    Expected result: SELECT * fails with a SELECT permission 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.

  3. Object-level: give the analyst a summary view instead of a table.

    SQL query: Exercise 4: Column- and object-level access (3)T-SQL (Fabric Warehouse)
    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.
  4. As lab.analyst, query the view, then try SELECT * 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.

  1. As Admin, open lh_security_lab and select Manage OneLake security.
  2. Create a role named LandingReaders with Read permission, Selected data, and select only the Files/landing folder. Add lab.analyst as a member.
  3. Wait about five minutes: role changes take that long to apply.
  4. As lab.analyst, open the Lakehouse explorer. Expected result: landing and its customers.csv are visible; restricted isn’t. Microsoft documents that folder permissions inherit downward and let members traverse parent folders without seeing siblings (folder inheritance and traversal).
  5. As Admin, open Manage OneLake security → All members in roles, select lab.analyst and 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.

  1. As Admin, give the analyst the email column back, then mask it:

    SQL query: Exercise 6: Dynamic data maskingT-SQL (Fabric Warehouse)
    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.
  2. As lab.analyst: SELECT customer_id, email FROM dbo.customers; Expected result: values in the documented email() mask format, such as nXXX@XXXX.com, still FL only.

  3. Run the same query as yourself. Expected result: real email addresses. Microsoft documents that Admins, Members and Contributors have CONTROL on the Warehouse, which includes UNMASK (permissions for dynamic data masking). To check it for the Contributor too, add ('lab.engineer@yourtenant.onmicrosoft.com', 'TX') to rls.state_access and query as lab.engineer; the expected result is real addresses for the TX rows.

  4. As lab.analyst, run an inference query:

    SQL query: Exercise 6: Dynamic data masking (2)T-SQL (Fabric Warehouse)
    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.

  5. Grant and remove the right to see real values:

    SQL query: Exercise 6: Dynamic data masking (3)T-SQL (Fabric Warehouse)
    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

  1. As Admin, edit the analyst’s Lakehouse share and add Read all with Apache Spark (ReadAll).
  2. 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

  1. 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:

    SQL query: Break B: row-level security that returns nothingT-SQL (Fabric Warehouse)
    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.
  2. As lab.analyst, query dbo.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 DefaultReader back (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.
  1. 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
What protected the customer table, path by path. The same Delta table, four paths and the outcome Microsoft's documentation predicts for each. Only the T-SQL paths are filtered by the Warehouse security policy.

Cleanup

  1. Remove both test users from any shares and roles you created, or simply delete ws_security_lab, which removes its items, roles and policies.
  2. If you added the test identities to groups for this lab, remove them.
  3. 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.

Tags

  • Microsoft Fabric
  • Security
  • Fabric Warehouse
  • Fabric Lakehouse
  • T-SQL