Granular Data Access Controls

Know where the granular check happens

Fabric evaluates access in three stages, in order: Microsoft Entra ID authentication, then Fabric access, then data security. The first two establish who you are and that you may use Fabric at all; only the third looks at the data itself.

At the data-security stage, Fabric checks whether a user can perform the requested action on a table. The same stage checks whether a user can perform the requested action on a file.

That third stage has four access controls that can be configured individually or together: workspace roles, item permissions, compute or granular permissions, and OneLake security. Workspace membership is therefore an input to this decision, not something settled before it, and on its own it never answers whether one particular table or file may be touched.

Within a lakehouse, the correct granular mechanism depends on the path used to reach the data.

Pick the route by how far it must reach

Two granular routes exist, and they differ in which engines respect them.

T-SQL permissions are compute permissions for the SQL analytics endpoint. Use Data Control Language commands such as GRANT, DENY, and REVOKE when users query lakehouse data with T-SQL. OneLake security roles apply consistently across Spark, SQL, and OneLake APIs.

This distinction matters when a restriction must work through one SQL endpoint or across the Fabric compute engines.

Filter rows and columns with T-SQL

Inside the SQL route, two controls answer two different questions.

T-SQL supports row-level security for SQL analytics endpoint access. Choose this control when the decision is about which records a SQL user can access.

T-SQL also supports column-level security for SQL analytics endpoint access. Choose this control when the decision is about which fields a SQL user can access.

The vocabulary is worth fixing before an exam question uses it against you: records means rows, fields means columns. A requirement that says "only their own region's orders" is a row question; one that says "everything except salary" is a column question.

Dynamic data masking is another granular T-SQL capability, but it is distinct from the row-level and column-level controls above.

Express the same filters in a OneLake role

OneLake security can express the same two dimensions in its role model. A OneLake security role can include optional row filters as constraints. A OneLake security role can also include optional column filters as constraints. The role's Constraints component is where both of those optional filters live.

Because OneLake enforces its roles across Spark, SQL, and OneLake APIs, use this route when the row or column restriction must remain consistent across those engines. A filter written only in T-SQL stops at the endpoint; a notebook reading the same table through Spark would never see it.

One condition decides whether those filters bite in SQL. The SQL analytics endpoint applies OneLake row and column filters when it runs in the user's identity access mode, and the lakehouse, Spark notebooks, and semantic models in Direct Lake on OneLake mode filter as well. When a user is not allowed to read every row and column of a table, direct storage access to that table is blocked outright instead of being filtered.

Writing one of these filters is a short trip through the role. Open Manage OneLake security, select the role, then select more options next to the table and choose Row security to type the SQL statement that decides which rows the member sees, or Column security to set the confidential columns to Hide, leaving at least one column allowed. Save, then test with someone who holds only that role and check both what they can now read and what they can no longer reach.

Scope a role to tables or folders

A OneLake security role is assembled from four components, and each one answers a separate question.

A OneLake security role defines which tables its members can access. The role's Data component records the selected tables, while its Members component records the assigned users or groups. A OneLake security role defines which folders its members can access.

The Permission component deserves a second look. The role's Permission component is either Read, for viewing data, or ReadWrite, for viewing and editing data in the selected tables or folders without granting a workspace write role.

That last clause is the useful part: a contributor-style capability on one folder no longer forces a workspace-wide write role. The two levels stop being interchangeable as soon as filters enter the picture, though, because a OneLake security role with ReadWrite access cannot contain row or column constraints. One role cannot both allow editing and hide rows or columns.

Know who a OneLake role cannot restrict

OneLake security roles are intended to grant specific data access to Viewers or users who have only Read item permission, because those users have no OneLake data access by default. The role is a way of adding narrow access, not of subtracting broad access.

Admin, Member, and Contributor workspace roles already have full read and write access to OneLake data, so OneLake security roles do not restrict their access. Suppose a OneLake role hides the Salary column of the Employee table. A workspace Admin assigned to that role still sees Salary, because the Admin role overrides the restriction. Only workspace Admins and Members can create or modify OneLake security roles.

Create a role and check what it inherits

To create a role, open the lakehouse lake view and select Manage OneLake security. Select New role, name it, and choose the permission level: pick Read whenever the role will carry row or column restrictions. Select the tables or folders to grant, apply any row or column security, add members, and then select Create.

You can edit the role later to change its data, its members, or its row and column security.

Then check inherited access before assuming a custom role is restrictive. Every new lakehouse includes a DefaultReader role. Sharing a lakehouse with Read all Apache Spark and subscribe to events adds the recipient to DefaultReader and grants read access to all data.

When a user should be limited to a custom role, remove that user from DefaultReader or the user retains full read access through the default role. That removal is not a click on the member list, though. DefaultReader fills itself, because its members are the users who hold read-all permission on the lakehouse, and members who arrived that way cannot be taken out of the role one by one. One way out is to revoke the permission that put them there, which here is the Apache Spark and events grant on the item. An administrator can also modify the default role, or remove it and create a custom role.

For table and folder Read grants, several roles add their permissions together. That is a union, not an intersection, and it is why DefaultReader keeps the wide path open. Column security in the SQL analytics endpoint is the exception: a column hidden there stays blocked even when another role would grant it, because that engine intersects the roles instead of adding them.

Key takeaways

• Use T-SQL row-level or column-level security for granular SQL analytics endpoint access.
• Use a OneLake security role to select tables or folders and enforce its access across Spark, SQL, and OneLake APIs, with the SQL analytics endpoint filtering when it runs in the user's identity access mode.
• A custom OneLake role cannot restrict Admin, Member, or Contributor access, and DefaultReader membership can preserve full read access.
• A ReadWrite role cannot carry row or column constraints, so a restriction belongs in a Read role.