Lakehouse Object Sharing onelake security role

[SeattlePowerBI]

Hello-

I have a lakehouse and I want to share two delta tables with users. I don't want them to see anything in the workspace, just query these two tables through the connector or analytics endpoint.

According to microsoft documentation, I should be able to add these users to a onelake security role which includes the two tables, then share the lakehouse with them with all boxes in the sharing dialog unchecked (not sharing the sql endpoint for example). However, users are now able to access anything in the lakehouse that I defined in the security role.

I've had a ticket open with Microsoft to confirm if this is possible to share tables this way, or if there is something else I need to do, but haven't been able to get confirmation from any support rep, so I thought it may be faster to just ask here on the forum. Am I on the right track? Is this setup correct (which it is according to docs.. https://learn.microsoft.com/en-us/fabric/data-engineering/lakehouse-sharing) or do I need to script permissions to the objects (which sort of defeats the purpose of the onelake security role.)

Thank you!

[v-saisrao-msft]

Hi @SeattlePowerBI,

We haven’t heard back from you in a while regarding your issue. let us know if your issue has been resolved or if you still require support.

Thank you.

[v-saisrao-msft]

Hi @SeattlePowerBI,

Checking in to see if your issue has been resolved regarding oblect sharing. Please let us know if you still need any assistance.

Thank you.

[v-saisrao-msft]

Hi @SeattlePowerBI,

I hope you had a chance to review the solution shared by @BhaveshPatel. If it addressed your question, Consider accepting the helpful reply as solution it helps others find answers more quickly.
If you're still facing the issue, feel free to reply, and we’ll be happy to assist further.

Thank you.

[BhaveshPatel · Accepted solution]

Yes. There is no way to hide all data users through Lakehouse . Read all SQL End Point or Lakehouse is the last option in Microsoft Fabric Lakehouse. it is open source linux systems. For that, You need to work in Direct Lake Mode ( Power BI Desktop ) where there is a option of "Make changes to this model" and you can select/ deselect whatever you want. or there is an option of row level security in Power BI Service. Microsoft Fabric is a Unified Data where there is only one security at all.

[BalajiL]

• Option 1: Try provide SQL Endpoint access at lakehouse level 
• 

  Option 2

• Create a view in warehouse , the underlying base table pointing to lakehouse tables. 
• Provide granular level access only to the table itself. 
• GRANT SELECT ON OBJECT::dbo.<view_name> TO [someuser@domain.com];

[SeattlePowerBI]

For option 2, can I grant select to the role?

[BalajiL]

Yes, you can provide group/ role

[SeattlePowerBI]

I did this, and the user is able to connect in Power BI desktop, but not in excel through the sql endpoint

[BalajiL]

• Glad to see that it worked, it should work in excel via sql endpoint. 

[SeattlePowerBI]

Can someone help me understand what the point of creating a onelake security role and granting access to a subset of tables, but not sharing the sql endpoint? What would be the usecase of that if noone can connect to the lakehouse from a client tool. Just trying to understand the reason why it is there, not trying to be rude.

Shouldn't this function the same as scripting the role, adding users to the role and granting the role access to specific objects (which does work for what I'm trying to do).?

[SeattlePowerBI]

The problem with this is granting access to the SQL analytics endpoint that way enables read on all objects in the lakehouse. I only want them to see and be able to query two objects.. I don't see the use of a onelake security role and sharing to two specific objects if I have to grant access to all objects via the sql endpoint for them to use the lakehouse. Scripting access defeats the purpose of the onelake security role, doesn't it? Hoping this isn't the only way.

[BalajiL]

Option 2 mentioned above in the thread will work for your case.

[v-saisrao-msft]

Hi  @SeattlePowerBI 

Thank you for reaching out to the Microsoft Fabric Forum Community. 
I have tested your scenario. 

If you've already shared Lakehouse with the SQL endpoint enabled, then the user will be able to access all the data exposed through that endpoint. 

However, if you share the Lakehouse without enabling the SQL endpoint, then the user won’t be able to access the data or query anything, unless they are explicitly granted access through roles or permissions. 

Try creating a new Lakehouse for testing and sharing it without enabling the SQL endpoint. This will help you confirm whether the role-based access works as intended when endpoint access is restricted. 

Below is a screenshot of the lakehouse without giving any permission to the user. 

So, the user cannot access the data in the Lakehouse. 

Then I've changed the permission to the same Lakehouse by enabling the permission to the user and  now the user can access the data. 

If this post helps, then please consider Accept it as a solution to help the other members find it more quickly.

Thank you.