By: Koen Verbeeck | Updated: 2024-08-05 | Comments | Related: > Microsoft Fabric
Problem
We're doing a proof-of-concept with Microsoft Fabric, building our data model in a lakehouse. We'd like to give people access to the data inside it so they can build their own reports with whatever tool they want. Is there an easy way to share access to a lakehouse (preferably not by giving access to the entire workspace)?
Solution
Microsoft Fabric is the new unified data analytics platform. One of its workloads is the lakehouse, where you can ingest, transform, and store data using the Spark compute engine. For more information about the lakehouse, check out the following tips:
- What are Lakehouses in Microsoft Fabric?
- Create a Notebook and Access Your Data in a Microsoft Fabric Lakehouse
In this tip, we'll investigate how you can give users in your organization access to a lakehouse so they can query the tables or files inside it. A similar tip is available for the Fabric warehouse: Microsoft Fabric Warehouse - Configure Access and Permissions. We'll use the same user (Alice) created to demonstrate the access control in the warehouse.
How to Give a User Access to a Microsoft Fabric Lakehouse
In this tip, we'll create a new Lakehouse to experiment with. In your Fabric-enabled workspace, click on the New dropdown to get a list of items you can create:
Select Lakehouse from the list and give it a name. Click Create.
Once the lakehouse has loaded, choose the Start with sample data option.
This will load a delta table with public holidays into the tables section, as well as some images and a couple of sample datasets into the files section.
Workspace Roles
Like with the warehouse, the first option to give someone access is to use the built-in workspace roles. A detailed explanation can be found in the tip named Microsoft Fabric Warehouse - Configure Access and Permissions. As a reminder, here are the workspace roles:
- Viewer – This is the default role. The user can view the content (through the SQL Analytical Endpoint) but cannot modify it.
- Contributor – The user can view and modify the content.
- Member – The user has the same permissions as a contributor but can share content and add other members.
- Admin – All the above and the ability to assign permissions.
When Alice is added as a viewer, she can open the lakehouse, but she cannot view the tables or the files:
However, she can query all tables with the SQL Analytical Endpoint.
Alice can also use SQL Server Management Studio (SSMS) to connect to the workspace and see all of the lakehouses, warehouses, and mirrored databases.
The connection string information can be found in the details section of the lakehouse by clicking on the ellipsis of the lakehouse in the workspace for the context menu and then clicking View details.
If you want to further secure the data inside the lakehouse when accessing it through the SQL Analytical Endpoint, you can apply row-level, object-level, and column-level security, as explained in the following tip: How to Implement Granular Permissions in the Microsoft Fabric Warehouse. After all, the SQL Endpoint behaves as a read-only version of the warehouse.
With the other workspace roles, a user can execute all CRUD (create-read-update-delete) operations on the lakehouse with, for example, code in a notebook. For an overview of the workspace roles with respect to the lakehouse, check out the documentation.
Sharing a Lakehouse
Workspace roles are an all-or-nothing approach. Either someone can view everything in the workspace, or they can view nothing at all. If you want to give Alice access to only a certain lakehouse but not to other objects like warehouses, sharing is a better method. Let's remove Alice from the Viewer role in the workspace access menu.
Next, in the workspace, select the ellipsis next to the lakehouse to get the additional options:
From the menu, select Share.
Enter the email of the user to whom you want to give access.
The permissions we can assign are the following:
- Read all SQL endpoint data: This is the ReadData permission and is similar to db_datareader in SQL Server.
- Read all Apache Spark: This is the ReadAll permission. All data (even the underlying files) can be read using Apache Spark.
- Build reports on the default semantic model: This is the Build permission, allowing the user to build Power BI reports on the default model of the lakehouse. Unlike the warehouse, this permission is not selected by default.
When nothing is selected, the Read permission is assigned, which only allows the user to connect to the SQL endpoint. In essence, sharing a lakehouse is the same as sharing a warehouse, as described in the following tip: Microsoft Fabric Warehouse - Configure Access and Permissions.
When Alice browses the Fabric service, the workspace will not be visible. Remember: We removed Alice from the Viewer role of the workspace. The Lakehouse can be discovered through the OneLake data hub.
When Alice clicks on the lakehouse, she will get an overview with the connection string and a link to the lakehouse.
However, it's not possible to open the lakehouse:
If Alice goes back to the Onelake data hub and selects the SQL endpoint instead, she gets the following overview:
The Create a blank report button is greyed out because Alice doesn't have a Power BI Pro subscription. When she clicks the Query button, the SQL endpoint will open, and she can preview and query the data.
What if you want to restrict access further using row or column-level security, for example? You can use the SQL endpoint by using the familiar controls of SQL Server. This is explained in the following tip: How to Implement Granular Permissions in the Microsoft Fabric Warehouse.
With the connection string from the Onelake hub, Alice can use an external tool, such as SSMS, to connect to the lakehouse. Behind the scenes, the SQL endpoint will be used to connect Alice to the lakehouse:
Remember to specify the database name as part of the options; otherwise, an error will be returned:
As admins, we can grant Alice additional permissions. In the lakehouse options menu, select Manage permissions.
On the permissions screen, we can add permissions to a user, remove permissions, or add a new user.
When the ReadAll permission is assigned, Alice can access the lakehouse data using a tool like Azure Storage Explorer. There, she can view all the tables, as well as all the files that are stored in the lakehouse:
If any security is applied to the SQL endpoint, it doesn't take effect here. Everything can be viewed with the ReadAll permission when going straight to the source files. In another tip, we'll explore how to apply security on the OneLake level.
Next Steps
- If you haven't already, check out the tips on security in the warehouse:
- You can find an overview of all Microsoft Fabric tips on this page.
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-05
