Hide All database objects in SQL endpoint for users with read access

[Bhargava05]

When a lakehouse is shared with an user without any additional permissions. He is able to connect to the lakehouse through SQL end point and see all the schema, table names and column names. But what i wanted to do is to hide all the metadata from the database unless explicity granted excess to a user.

I did try using revoke and deny for view definition permission but it didnt work,

Before applying revoke/deny

After applying revoke/deny

The user was still able to see metadata of all database objects. And also is there a way where i can revoke view definition to all of the users instead of specifying each at a time? And then grant access to each based on neccessity?

Please do refer to my previous query related to this: Hide All database objects in SQL endpoint for user... - Microsoft Fabric Community

Any help would be appreciated!!

[Bhargava05]

Hi @v-nmadadi-msft 

I did try using DENY ALL ON OBJECT::dbo.dimension_employee TO [alice@mytenant.com]; but somehow even if i run this query on  a single table, its able to deny for all tables in all schemas expect for explicity granted tables. 

And also i did try using onelake data access, Like suggested from microsoft documentation, i haven't added user to any of the workspace roles, i have just shared the lakehouse with no additional permissions. Now i deleted the default reader role and created a new role and assigned a user to the role. Ideally the user should be able to access the table from the role hes part of. But, it throwed an error "unable to load" , although if im giving the user workspace viewer access then hes able to see the data form lakehouse view.

[v-nmadadi-msft · Accepted solution]

Hi @Bhargava05 ,
Thanks for reaching out to the Microsoft fabric community forum.

Currently as the lakehouse schemas is still in public preview some of the features are not yet supported. 
The OneLake Data Access feature does not seem to be working correctly for schema-enabled Lakehouses. However, the feature functions as expected when using a Lakehouse without schema enabled. Since both features Lakehouse with schema enabled and OneLake Data Access are still in preview this behaviour may be due to the limitations of the preview phase. Please wait for the general availability release, as this limitation may be addressed in the final version.

If you find this post helpful, please mark it as an "Accept as Solution" and consider giving a KUDOS. Feel free to reach out if you need further assistance.
Thanks and Regards

[v-nmadadi-msft]

Hi @Bhargava05,

May I ask if you have resolved this issue? If so, please mark the helpful reply and accept it as the solution. This will be helpful for other community members who have similar problems to solve it faster.

Thank you.

[v-nmadadi-msft]

Hi @Bhargava05 ,
Thanks for reaching out to the Microsoft fabric community forum.

By default, sharing a lakehouse grants users the ability to view the schema and the tables.
People you share this Lakehouse with can open it and its SQL endpoint and read the default dataset.

If we go to Manage OneLake data access and select default reader and remove the respective user from the list, the user will only be able to view the lakehouse and not see the tables or the metadata as provided by the screenshot below

This is what we can do with manage onelake data access feature, if this meets your requirement please consider utilising this feature.
If we want to provide the user with access to the name of the tables but not the metadata inside it we have to use Object level security for that and use commands like grant, deny and revoke.

DENY ALL ON OBJECT::dbo.dimension_employee TO [alice@mytenant.com];

Once we successfully run deny command, try to run select queries from the user whom we just denied the permission, they wont be able to query the database and will face permission denied error.
As you have mentioned deny statement have not worked, please check if the user has some other privileges that are preventing the Deny statement to take its effect.

If you find this post helpful, please mark it as an "Accept as Solution" and consider giving a KUDOS.
Thanks and Regards