How to grant all tables in schema except 1

eballinger — Thu, 30 Jan 2025 16:55:37 GMT

Hi Guys, I am trying to grant all tables in a schema to a user group in databricks. The only catch is that there is one table I do not want granted. I currently am granting schema access to the group so the benefit is that as tables are add in the future the group will inherit these as well.

What I was thinking is that I could just revoke the single table and it would work but that is not working. This is what I have :

GRANT USE SCHEMA, SELECT, EXECUTE ON SCHEMA catalog_name.schema_name TO `MY_GROUP`;

REVOKE SELECT, EXECUTE ON TABLE catalog_name.schema_name.exclude_table TO `MY_GROUP`;

I have seen suggestions to just list the tables and manually grant the tables one by one and leave out the one I dont want but this will eliminate the benefit in the future where new tables are added I will have to regrant those new tables.

Also I have seen a post where someone suggested to use row filtering and just remove all rows for that user group. That would work but seems like overkill to my simple requirement.

Anyone have any other suggestions on how to I can grant all tables in a schema except for 1 table?

Thanks for any suggestions

Re: How to grant all tables in schema except 1

NandiniN — Fri, 31 Jan 2025 16:25:42 GMT

Your use case will not be solved with this solution, but trying to help still - Is it possible to move that 1 table to another schema?

Re: How to grant all tables in schema except 1

NandiniN — Fri, 31 Jan 2025 17:05:16 GMT

What you are facing is because of inheritance.

https://docs.databricks.com/en/data-governance/unity-catalog/manage-privileges/upgrade-privilege-model.html

I would say this is by design, but please feel free to suggest it as an idea here - https://docs.databricks.com/en/resources/ideas.html#ideas

topic How to grant all tables in schema except 1 in Data Engineering

How to grant all tables in schema except 1

Re: How to grant all tables in schema except 1

Re: How to grant all tables in schema except 1