Fabric Security and Governance: Workspace Roles, RLS, and OneLake Access

Fabric evaluates access in three layers, and a user needs a path through all of them to reach data: the workspace layer (roles granted on the whole workspace), the item layer (permissions granted on a single Lakehouse, Warehouse, notebook, or report through sharing), and the data layer (RLS, CLS, OLS, dynamic data masking, and OneLake folder security that filter what a permitted user sees inside an item). A workspace role is the broadest grant — it applies to every item in the workspace, including items created later. Item permissions are the surgical alternative: you share one item with a user who has no workspace role at all, and they see only that item.

The layers combine additively for access but each answers a different question. Workspace roles answer "what can you do in this workspace?" Item permissions answer "which items can you touch?" Data-level security answers "which rows, columns, and files can you see once you are inside?" On the exam, requirements phrased as "a user must query only one warehouse" point at item sharing, "analysts must see only their own region's rows" points at RLS, and "the finance team manages all content in the workspace" points at a workspace role.

One principle governs the whole model: grant the least privilege at the narrowest layer that satisfies the requirement. Giving someone the Viewer workspace role when they need one report exposes every item in the workspace; sharing the single report does not.

Fabric has exactly four workspace roles, and their capabilities nest: each role can do everything the roles below it can. Admin owns the workspace itself — only Admins can update or delete the workspace and add or remove other Admins. Member can manage membership below Admin level and share items broadly. Contributor is the working engineer's role: create, edit, and delete items, run notebooks and pipelines, and write data — but no rights to manage who else has access. Viewer is read-only: view items and query data through the SQL analytics endpoint (subject to any SQL-level security), but no OneLake or Spark access to the underlying files.

Two rows in that table decide most exam questions. First, Contributor cannot share — a scenario where a developer must build content but must not grant access to others is Contributor, not Member. Second, Viewer reads through SQL, not through OneLake — a Viewer querying a Lakehouse uses its SQL analytics endpoint and is subject to any T-SQL security defined there, but cannot open the Delta files with Spark. That distinction is the hinge for the RLS-bypass discussion later in this lesson.

Roles are assigned to individual Microsoft Entra ID users, security groups, Microsoft 365 groups, or distribution lists. Prefer groups: assigning roles to groups keeps access reviews manageable and makes onboarding a membership change instead of a Fabric change.

Item-level access lets a user reach a single item without holding any workspace role, and it is how you serve consumers outside the engineering team. When you share a Warehouse or Lakehouse, the share dialog maps to underlying item permissions: Read lets the recipient connect to the item; ReadData lets them read every table through the SQL analytics endpoint or Warehouse SQL surface; ReadAll lets them read the underlying files directly through OneLake and Apache Spark; Build lets them create reports on the associated semantic model; Reshare lets them pass their access on to others; and Write grants edit rights on the item.

The Read/ReadData/ReadAll split is the load-bearing distinction. A recipient with only Read on a warehouse can connect but sees no data until you grant table access with T-SQL (GRANT SELECT) — this is the pattern for fine-grained SQL security. Granting ReadData opens all tables through SQL, where RLS, CLS, OLS, and masking still apply. Granting ReadAll opens the raw Delta files — and SQL-defined security does not travel to file reads, so ReadAll effectively bypasses RLS and CLS unless OneLake security restricts the paths.

Item permissions also come into play within a workspace: sharing is additive on top of roles, and some permissions (like Build on a semantic model) may need to be granted even to users who can already see the item. When a requirement says "external analysts must query the warehouse tables but must never see the files," the answer is share with ReadData and withhold ReadAll.

RLS, CLS, and OLS are T-SQL controls defined in a Warehouse or in a Lakehouse's SQL analytics endpoint, and they filter what a user sees when querying through SQL. Row-level security restricts which rows a query returns. You implement it with an inline table-valued function that encodes the predicate — typically comparing a column to USER_NAME() — and bind it with CREATE SECURITY POLICY as a filter predicate on the table. From then on, every SELECT from that table is silently filtered per caller; salespeople querying the same table each see only their own territory's rows.

Column-level security restricts which columns a user can select. It is not a separate feature so much as column-scoped grants: GRANT SELECT ON dbo.Customers (CustomerID, Name, Region) TO [analyst@contoso.com] allows those three columns and nothing else — a query touching the omitted Salary column fails outright. Object-level security works one level up with GRANT, DENY, and REVOKE on whole tables, views, schemas, stored procedures, and functions. A common composite pattern: deny direct table access, expose curated views, and grant SELECT only on the views.

Know the enforcement boundary cold: these controls live in the SQL engine, so they protect the Warehouse and the SQL analytics endpoint — and nothing else. A user who reaches the same Delta tables through Spark or a OneLake file path is not filtered by a security policy. Also distinguish engine RLS from semantic model RLS: Power BI semantic models define their own roles with DAX filter expressions, and models in Direct Lake mode can either enforce their own security or fall back to the SQL endpoint's identity — the two RLS systems are configured separately and do not synchronize automatically. Finally, note that a Lakehouse's SQL analytics endpoint is read-only for data but does accept security DDL, so you can define RLS/CLS/OLS over Lakehouse tables for SQL consumers.

Dynamic data masking (DDM) obfuscates sensitive column values in query results for non-privileged users without changing the stored data. You attach a masking function to a column with ALTER TABLE ... ALTER COLUMN ... ADD MASKED WITH (FUNCTION = '...'). Four functions exist: default() fully masks per data type (strings become XXXX, numbers become 0), email() exposes the first letter and the domain suffix pattern, partial(prefix, padding, suffix) reveals custom leading and trailing characters, and random() substitutes a random number within a range for numeric columns.

Masking applies to users who lack the UNMASK permission. Users holding elevated item or workspace access — Admins, Members, and Contributors — see real values, so DDM is meaningful mainly for shared, ReadData-style consumers. Grant UNMASK (database-wide, or scoped to a schema, table, or column) to selectively reveal true values to specific auditors or applications without rewriting the mask.

The exam expects you to know DDM's limits. It is presentation-layer obfuscation, not encryption: the plaintext is stored and processed normally, and a user with ad-hoc query rights can often infer masked values with predicate probing (WHERE Salary > 100000 still filters on real values). Choose DDM to keep casual eyes off PII in shared query surfaces; choose CLS to block a column entirely; never present DDM as a substitute for access control on the exam or in production.

OneLake security controls access to the data itself — the folders and files under a Lakehouse — for users who come in through OneLake paths, Spark notebooks, or other file-oriented endpoints. This is the layer that closes the gap T-SQL security leaves open. With OneLake data access roles on a Lakehouse, you define named roles that grant Read on specific folders (for example, only Tables/gold or a particular subfolder of Files) and assign users or groups to them. Once data access roles are enabled on a lakehouse, users with ReadAll-style access no longer see everything by default — they see only the folders their roles allow.

This changes the earlier bypass story: without OneLake security, ReadAll means every file; with it, file-path access is itself scoped. The evolution of OneLake security is toward defining security once at the OneLake level — including row and column rules — and having compute engines enforce it consistently, but for exam purposes keep the working model simple: SQL security guards the SQL door, OneLake security guards the file door, and a complete design covers both doors.

Practical guidance: keep raw and sensitive zones in folders that only pipeline identities and engineers can read, expose curated gold folders through data access roles, and route business consumers through the SQL analytics endpoint or semantic model where RLS and CLS apply. Shortcuts deserve a note — a OneLake shortcut's target data is governed by the security of the target location, so shortcutting into a lakehouse does not smuggle anyone past the target's controls.

Governance in Fabric answers three questions: how sensitive is this item, can I trust it, and who touched it? Sensitivity labels from Microsoft Purview Information Protection classify items (Public, General, Confidential, Highly Confidential — whatever your organization defines). A label applied to a Fabric item follows the data downstream: it propagates to derived items through inheritance and persists when data is exported to files, where it can drive encryption and usage protection from the Purview side. Labels can be applied manually by item owners, mandated by tenant policy, or inherited from upstream sources. Labeling requires the feature to be enabled in the tenant admin portal and appropriate licensing.

Endorsement is Fabric's trust signal, with three levels. Promoted is self-service: any user with write permission on an item can promote it to signal it is ready for wider use. Certified is controlled: only reviewers explicitly authorized by the tenant admin can certify, marking the item as meeting organizational quality standards. Master data designates an item as the authoritative source of core organizational data — also restricted to admin-authorized users. Endorsed items surface prominently in the OneLake catalog and in search, steering consumers toward trusted content.

Fabric audit logs record user and system operations — item creation, sharing, permission changes, data access — in the unified Microsoft Purview audit log. Compliance teams search them in the Microsoft Purview portal's Audit solution or with the Search-UnifiedAuditLog PowerShell cmdlet; retention depends on your Purview audit plan. For the exam: audit logs are the answer to "determine who shared the warehouse" or "investigate who changed permissions," and they live in Purview, not inside the Fabric workspace UI.

Requirements: engineers build and load a Lakehouse; a BI team queries curated tables but each analyst may see only their own region's rows; the analysts must never read raw files; salary data must be hidden from everyone but HR; auditors need to know who accessed what. Here is the layered design.

Workspace layer: engineers get Contributor (they build but do not manage access); the data platform lead gets Admin; nobody else gets a workspace role. Item layer: share the Lakehouse with the BI team's Entra ID security group granting SQL analytics endpoint access (ReadData-style) and explicitly not ReadAll — no file-path access. Data layer: on the SQL analytics endpoint, create a security policy with a predicate function mapping USER_NAME() to a region assignment table for RLS; use column-scoped GRANT SELECT so the salary column is selectable only by the HR group; optionally add partial() masking on customer email for the general analyst population. Enable OneLake data access roles so that even engineers' Spark access is scoped to the folders their pipelines own.

Governance: apply a Confidential sensitivity label to the Lakehouse so it propagates to downstream reports and exports; have the authorized reviewer mark the curated gold tables' semantic model as Certified; answer the auditors with Purview audit log searches. Every requirement maps to exactly one layer — which is precisely the mapping skill DP-700 scenario questions test.