By: Koen Verbeeck | Updated: 2024-08-26 | Comments | Related: > Microsoft Fabric
Problem
We created a lakehouse in Microsoft Fabric that contains several tables. We want to share the lakehouse with other teams, but some persons should only have access to specific tables. Is there a way to restrict access in a lakehouse?
Solution
In Microsoft Fabric, there are several ways to assign permissions to a person. You can assign permissions through the built-in workspace roles, which grant permissions (such as read or write) to all the objects in the workspace. If you want to give access to only a specific object, you can share it. For the warehouse and the lakehouse, both concepts are described in the following tips:
- How to Share Access to a Fabric Warehouse?
- How to Manage Access Control for a Microsoft Fabric Lakehouse?
In a Fabric warehouse, you can also assign granular permissions, as we have always done in any SQL Server-related product, using GRANT, DENY, and REVOKE. For a lakehouse, the SQL Analytics Endpoint is basically a warehouse layer over the lakehouse, so you can use the exact same security principles as in the warehouse (with the exception that the SQL Endpoint is read-only). However, these constructs do not exist in the lakehouse, so how can we secure individual tables? Luckily, a new feature that solves this issue has been introduced: role-based access control (RBAC) in OneLake. With RBAC, you can secure folders inside OneLake and assign permissions to users or groups.
In this tip, we'll explain how to use RBAC to restrict access to certain folders (and thus tables in the lakehouse). At the time of writing, the RBAC feature is in public preview.
Managing OneLake Folder Permissions with RBAC
Before we start, it's important to realize that the RBAC roles are used to assign read roles. When the permissions of a user need to be validated, the following sequence takes place:
This means that workspace permissions take precedence over an RBAC role. If a user has admin, member, or contributor access to the workspace, they can view all the OneLake folders for that workspace. RBAC roles only take effect for users with the Viewer role of the workspace. If the user doesn't have a workspace role but has lakehouse permissions (for example, by having the lakehouse shared with them, as explained in the tip How to Manage Access Control for a Microsoft Fabric Lakehouse?), the RBAC roles can be used for the following:
- Grant access to certain folders if the user has Read permission.
- Restrict access to certain folders if the user has ReadAll permission.
It's also very important to note that RBAC doesn't impact the permissions for the SQL Analytics Endpoint. You can restrict table access in OneLake (and thus in the Lakehouse), but the user will still be able to read data from the table when querying the SQL Endpoint. As mentioned before, permissions on the SQL Endpoint are managed like in the warehouse, which is described in the tip How to Implement Granular Permissions in the Microsoft Fabric Warehouse? This means that if you want to completely restrict access to a table, you need to implement permissions in two different places.
Implementing an RBAC role
Let's try this feature out with the user Alice. We've been working with this user in the previous tips of Fabric security. On the Lakehouse level, Alice has the Read permission (the lakehouse was shared with her in the tip How to Manage Access Control for a Microsoft Fabric Lakehouse?).
On the SQL Endpoint, Alice has the ReadData permission.
To get started with RBAC, go to the lakehouse with either an Admin, Member, or Contributor role of the workspace. In the lakehouse itself, click on the Manage OneLake data access button. At the time of writing, this feature is still in preview.
You will get a warning with the consequences of enabling this feature:
It is possible that enabling this feature breaks any external data shares. By default, a role named DefaultReader will be created:
This role will contain any user that has the ReadAll permission, and this role grants access to all the folders of the lakehouse. It's possible to remove or edit this role if you don't want to give users access to everything.
When you click on New role, you can give the role a name and choose which folders you want the role to have access to:
In our role, we only assign access to one table (located in the \Tables folder) and one folder named "images" (in the \Files folder). When you give access to a folder, all files in the folder and all subfolders are included. You will also be able to traverse the parent folders, just like in Windows. In our example, a user belonging to the "TestRBAC" role will see the \Tables and \Files folders at the root level. Assigning permissions in OneLake is similar to the experience in OneDrive or Windows.
Next, we need to assign users (or groups) to our new role. You can filter existing users on the permissions they currently have on the lakehouse. In the following screenshot, all users with the permission Read are listed:
.
The other option is to assign users directly to the role:
Once the user is added, click Save for the changes to take effect.
Testing the RBAC Role
We can test the role by logging in as the user Alice. Currently, there's no option to impersonate another user (which is possible in Power BI to test row-level security, for example). When Alice goes to the lakehouse, she will only see the table and the folder assigned to her in the RBAC role:
She can see all the files in the images folder:
When browsing OneLake directly (using Azure Storage Explorer, for example), the same results are returned:
However, when Alice uses the SQL Endpoint, she can view and query all tables!
Next Steps
- Check out the following tips about managing permissions in a Fabric workspace:
- For more details about the new OneLake RBAC feature, check out the official documentation.
- You can find all Microsoft Fabric tips in this overview.
About the author
Koen Verbeeck is a seasoned business intelligence consultant at AE. He has over a decade of experience with the Microsoft Data Platform in numerous industries. He holds several certifications and is a prolific writer contributing content about SSIS, ADF, SSAS, SSRS, MDS, Power BI, Snowflake and Azure services. He has spoken at PASS, SQLBits, dataMinds Connect and delivers webinars on MSSQLTips.com. Koen has been awarded the Microsoft MVP data platform award for many years.This author pledges the content of this article is based on professional experience and not AI generated.
View all my tips
Article Last Updated: 2024-08-26
