Demystifying roles and access in Microsoft Fabric lake house

In Microsoft Fabric, SQL analytics endpoint is automatically generated for a Lakehouse. With SQL analytics endpoint a user can view the Lake house data using TSQL.

When it comes to lakehouse roles and access the lakehouse security model is not completely intertwined but to a certain extent is rather explicit.

Lets check out all the different scenarios through an example.

Note : If you would want to skip the writeup you can instead watch the video demo here.

Role Types

In Microsoft Fabric there are four types of default workspace roles

• Admin -It is the highest form of privilege. An admin can view, modify, share, and manage all artifacts in a fabric workspace including permissions.

• Member - Can view, modify, and share all content in the workspace but has no authority to manage workspace permissions.

• Contributor - Can only view and modify content in the workspace.

• Viewer - Can view all content in the workspace but cannot perform any modifications

Setup

I have a group in my Entra account named FabricUsers. This group has two users, fabricuser_1 and fabricuser_2.

LakeHouse access without Workspace access

I have a lakehouse named LakeHouse_1 that resides under workspace My_Workspace.

I will add the group FabricUsers and grant access to a lakehouse LakeHouse_1without any additional permissions selected.

When I login as a fabricuser_1 , I can access the lakehouse but I cant see or browse any items in the lakehouse which is an expected behavior.

I have explained the reason for this behavior further in the article.

Grant WorkSpace Access

Now, I will grant a Viewer access(which is the least privileged access) to the Workspace My_Workspace where the lakehouse Lakehouse_1 resides.

Granting Viewer access to the Workspace, now allows the users of group FabricUsers to connect through SQL endpoints and access all the data in the lakehouse through it.

Remember: The group has NOT been granted “Read all SQL Endpoints data” permission.

Note : Granting any form of access to a user or group at the workspace level does NOT create a corresponding SQL user in the SQL Analytics endpoints.

Default lake house role

OneLake generates a default RBAC Role named DefaultReader in every newly created Lakehouse , this group is automatically created and includes any users with any workspace access by default.

For example, if I grant the group FabricUsers Contributor access for the workspace

by default the group FabricUsers gets added to the DefaultReader role in the lakehouse.

But we DONT want any form of workspace level access for this group.

Revert the workspace access granted for the Group FabricUsers as we want the scope of access limited only to the lakehouse Lakehouse_1 not to the entire workspace.

Once done the access level for group FabricUsers would look like this.

So now the question arises as how to ensure that the lakehouse is accessible to the members of Group FabricUsers , given that the group FabricUsers have no access to the workspace.

Manage OneLake data access (preview)

Open the Lakehouse and select Manage OneLake data access (preview)

and select the DefaultReader role

and add the group FabricUsers as a member to this role.

FabricUser group is added as a user to the DefaultReader role

Give couple of minutes for changes to get reflected and then login into the tenant with any user of the group FabricUsers and the tables in the lakehouse should now be accessible.

And that’s how you can grant granular access to lakehouses in the workspace without explicitly granting any access at the workspace level.

Note : The SQL endpoint access will still be denied as the group FabricUsers has no workspace access(as we reverted it) and also we haven’t granted any explicit SQL endpoint access.

Limit workspace access to a folder/s

Next ,what if we want the access to be limited to a single folder or specific folders in the lakehouse ?

Go back to the DefaultReader role and select folder/s to which you want the user to have access. I selected lk_table_1 folder.

Now login with either user fabricuser_1 or fabricuser_2 and both users should be able to view only the table lk_table_1 and not lk_table_2.

Note : If you go back and reassign the Group FabricUsers workspace level access, the users in the group FabricUsers will be able to view data under all items under the lakehouse.

This means that access granted at workspace level will ALWAYS override access granted to the individual objects irrespective of access granted to them.

Grant Read all SQL Endpoints data access

Granting “Read all SQL Endpoints data” will allow users of the group FabricUsers to query all the tables in the lakehouse.

To grant “Read all SQL Endpoints data” to group, select Manage Permissions in the Lakehouse option

and select Add user

Note : Even if the FabricUsers group is already a user of the lakehouse, you can re-add it and grant additional access without needing to remove and re-add the user.

and grant “Read all SQL Endpoints data” to the Group after entering the group or user name.

This makes the data accessible through SQL endpoints for users in group FabricUsers.

Note : Granting SQL endpoints access does not create a corresponding SQL user to whom the SQL endpoint access has been granted.

LakeHouse Custom Roles

Now lets create a custom role in the lakehouse and name it as MyDefinedRoles

and limit the access of this role MyDefinedRoles to folder lk_table_2

Remember the access for role DefaultReader is limited to lk_table_1

I will explicitly make user fabricuser_2 a member of the role MyDefinedRoles.

Note : That fabricuser_2 is also a member of role MyDefinedRoles whose access is limited only to lk_table_1.

So what should happen in this case ? Will fabricuser_2 user have access to both the folders as he/she is member of two roles with one role having access to lk_table_1 and the other role having access to lk_table_2 ?

If your answer is yes, then you are correct.

Data for both the tables lk_table_1 and lk_table_2 is accessible to user fabricuser_2 .

and for user fabricuser_1 , who is member of the group FabricUsers and FabricUsers group being a member of DefaultReader role, only lk_table_1 data is accessible.

You can watch demonstration of the above use case in the video that I posted here.

But then what about SQL Endpoints ?

Earlier we had explicitly granted SQL endpoints access to the group FabricUsers. The tables will stay accessible for both users.

fabricuser_2 SQL endpoint access :

fabricuser_1 SQL endpoint access :

If SQL endpoints access is not granted, the lakehouse data stays inaccessible for the users of the group FabricUsers.

Access SQL Endpoints without granting access to the SQL Endpoints

I deleted the FabricUsers group from the lakehouse LakeHouse_1

and re added the FabricUsers group without granting any access to the SQL endpoints.

This is because I want to limit the scope of access for the objects in SQL endpoints at a granular level without granting explicit SQL endpoint access. By default, lakehouse tables are created under the dbo schema.

Grant the group FabricUsers, SELECT access on schema dbo.

GRANT SELECT ON SCHEMA::dbo TO [FabricUsers]

This creates a SQL user with name FabricUsers. Maybe this should help to trace user activity through tracing tools.

Note : Granting SQL endpoints access does not create a corresponding SQL user but GRANT or DENY does.

As both tables are under schema dbo, the tables in the schema dbo is accessible for users of group FabricUsers.

Privilege precedence in SQL Endpoints

Now I have denied access to the schema dbo for group FabricUsers

DENY SELECT ON SCHEMA::dbo TO [FabricUsers]

But I granted user fabricuser_2 explicit access to table lk_table_1 that is part of the dbo schema.

GRANT SELECT ON [dbo].[lk_table_1] TO [fabricuser_2@azureguru.net];

fabricuser_2 now has no access to any tables under schema dbo in spite of being granted exclusive access to the table lk_table_1.

This is expected behavior as least privileges takes precedence irrespective of the hierarchy level where access is defined.

Lets switch the scenario wherein access to the dbo schema for FabricUsers is allowed.

GRANT SELECT ON SCHEMA::dbo TO [FabricUsers]

But select on table lk_table_1 is denied for user fabricuser_2

DENY SELECT ON [dbo].[lk_table_1] TO [fabricuser_2@azureguru.net];

DENY will take precedence disallowing access to lk_table_1 for user fabricuser_2 but allowing access to table lk_table_2 , both of which are part of the dbo schema.

while the user fabricuser_1 is able to access both the tables.

Note : Any form of explicit access, whether its a GRANT or DENY for individual users or groups creates corresponding users in the SQL endpoints.

Video Walkthrough

Part 1 :

Part 2 :

Conclusion

Microsoft Fabric offers a robust and integrated security framework to safeguard access to different entities in the workspaces. By leveraging built-in features like Roles, Endpoint access and Workspace level access to ensure high level security you can securely manage the Fabric tenant without much overheard.

I hope this write up was able to provide some level of insights into the intricacies related to different security entities involved in Fabric ecosystem.

Thanks for reading !!!