grant access to the data through views but not to the source data itself?

[Jerome22]

Hi,

I want to grant access to some views in a fabric warehouse, these views are connected to another lakehouse in the same workspace, like:

create view MyView as 

select *

from SilverLakehouse.dbo.mytable

I want to grant access to the warehouse only to my users

but when they access the view, they have an access denied to the underlying table.

how can I grant access without granting access to the lakehouse itself? (I want to keep it hidden, the user should never go to it directly)

the problem occurred only when the user need to access the warehouse database in SQL mode, not through power bi and the default semantic model. 

[v-gchenna-msft]

Hi @Jerome22 ,

We are reaching out to the internal team to get more information related to your query and will get back to you as soon as we have an update.

[v-gchenna-msft]

Hi @Jerome22 ,

Here is the proposed solution. Team tested this solution and this should work. Please read the solution carefully.

Response from internal team -

"
Scenario
User A created a table in Lakehouse and view on top of Lakehouse table in a warehouse within same workspace.
User A needs to give permission on the view without giving access to underlying table to User B.

Solution
It is not possible to give select permission on view in Warehouse without granting select permissions on the underlying object in LH table. This seems to be a bug but it is current behavior in Fabric DW. I will report this to the internal team and log this as an issue.

However, this can be circumvented with following solution.

User A must share the LH and WH via manage permissions without enabling any additional permissions in the below screenshot.

The above permission will avoid following error on LH and WH.

Give select permissions on the Lakehouse table in SQL endpoint to avoid below error.

Deny view definition permission on LH table in SQL endpoint so that User B can't see the object in the object explorer. Note: Select permissions on the LH table still exist, which means, if the user B knows the table name, user can still query LH tables using SQL endpoint. However, user B cannot see objects in the explorer

Remember, with "deny view definition" permission, users cannot get the table name from system views.
Also note that with select permission on view in the warehouse, user B cannot retrieve (user A can though) the view definition to find out the table name.

select definition
from sys.objects o
join sys.sql_modules m on m.object_id = o.object_id
where o.object_id = object_id( 'dbo.MyView')
and o.type = 'V'

"

Thank you

[v-gchenna-msft]

Hello @Jerome22 ,

We haven’t heard from you on the last response and was just checking back to see if you have a resolution yet. In case if you have any resolution please do share that same with the community as it can be helpful to others. Otherwise, will respond back with the more details and we will try to help.

[Jerome22]

Hi,

I did not had the time to test this solution.

it's not critical for now, but next year we'll go in depth with all of this as we'll rampup the usage of Fabric.

[v-gchenna-msft]

Hi @Jerome22 ,

Hope you got some insights over your query. Please continue using Fabric Community incase of your further queries.