How to Grant Access to an Individual Table/View in SQL Analytics Endpoint for a Lakehouse?

[mramesh]

Hi Community,
I'm working with the Lakehouse in Microsoft Fabric, and I need to provide access to a specific table or view through the SQL Analytics endpoint. Can anyone share the steps or best practices for granting access to an individual table/view via SQL Analytics, without giving broader access to the entire Lakehouse or other tables/views?

[VenuBollineni · Accepted solution]

Hi @mramesh  Good Day!

For example, to give a specific user access to a view, you can use T-SQL

GRANT SELECT ON [lakehouse_demo].[dbo].[Customer10] TO [username];

For doc pls check below
What is the SQL analytics endpoint for a lakehouse? - Microsoft Fabric | Microsoft Learn
Lakehouse sharing and permission management - Microsoft Fabric | Microsoft Learn

Regards,
Venu Bollineni

[fdnavarropecci]

Hi, I have been testing this as well and found some unexpected behaviour.

If someone has experience with item-level access control and grants on specific views and sees a mistake in my process here, please chip in. Otherwise this looks like a bug/flaw to me.

Use Case and Setup Description

We have a workspace with three Lakehouse items: Bronze, Silver and Gold.

We want to expose certain views on the gold layer as a data product.

The access should be granted only  to users that belong to a given AD Group. 

The group is granted Read access to the Lakehouse Item. No ReadData or ReadAll permissions are given.

The group does not have any roles assigned at workspace level.

The group is in addition granted select on certain views in the Gold Lakehouse.

The expected behavior would be that the users in that group can list the tables and views in the Gold Lakehouse via the SQL Endpoint and read the data from the views they have been granted access to. We would expect that the users can't see the tables and views, nor read the data in the Bronze and Silver Lakehouses.

Findings

1. There is a single SQL endpoint for the whole workspace, not one per Lakehouse. We initially were under the impression that each Lakehouse had a separate SQL endpoint. If a Warehouse item is added to the same workspace, it will also share the same SQL endpoint.
2. Once granted read to one of the Lakehouses, users can see and read data in every other Lakehouse/Warehouse via the SQL endpoint, even if there is no access specifically granted to them to those items.

[PatChan]

Hi @fdnavarropecci, thanks for your detailed thought process. I am like you where after I ran "GRANT SELECT ON [lakehouse_demo].[dbo].[Customer10] TO [username];", this [username] could see every single tables and views even though we only granted this [username] one view. Have you found a solution to this yet?

[fdnavarropecci]

Hi @PatChan , we tested this again and is now working as expected. We suspect it was related to some role assignments that were removed shortly before granting access to the item.

[AndyDDC · Accepted solution]

Hi @mramesh further to what @VenuBollineni said, you also need to "share" the lakehouse with the user (or group).  Make sure you don't check any of the options, doing this will only give the user "connect" permissions to the Lakehouse SQL Endpoint.  Then the object level permissions as described by @VenuBollineni will kick in

[Kash10]

Hi, 

I performed the steps mentioned below but I am getting the below error
User is not authorized to perform current operation for workspace '39d311d6-640e-4655-8757-ab01acca075a', artifact '76085361-7964-470c-a992-3e6d82a64ac8'.

What needs to be done here, if I don't the user to be able to view all the items unders the workspace.

[fdnavarropecci]

This looks like you're missing  permissions to share the item, you need either member or admin role at the workspace level to be able to do so.

[Kash10]

Thanks for your response fdnavarropecci!
But I am the platform and the workspace admin, just by following the above steps, I am still not able to provide the permission to view the table data. Is it possible to list all the steps so that i can try them in the same order.

[VenuBollineni · Accepted solution]

Hi @mramesh  Good Day!

For example, to give a specific user access to a view, you can use T-SQL

GRANT SELECT ON [lakehouse_demo].[dbo].[Customer10] TO [username];

For doc pls check below
What is the SQL analytics endpoint for a lakehouse? - Microsoft Fabric | Microsoft Learn
Lakehouse sharing and permission management - Microsoft Fabric | Microsoft Learn

Regards,
Venu Bollineni