cancel
Showing results for 
Search instead for 
Did you mean: 
Data Engineering
Join discussions on data engineering best practices, architectures, and optimization strategies within the Databricks Community. Exchange insights and solutions with fellow data engineers.
cancel
Showing results for 
Search instead for 
Did you mean: 

How to grant all tables in schema except 1

eballinger
Contributor

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
   

 

1 ACCEPTED SOLUTION

Accepted Solutions

NandiniN
Databricks Employee
Databricks Employee

What you are facing is because of inheritance. 

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

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 

View solution in original post

2 REPLIES 2

NandiniN
Databricks Employee
Databricks Employee

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?

NandiniN
Databricks Employee
Databricks Employee

What you are facing is because of inheritance. 

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

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 

Connect with Databricks Users in Your Area

Join a Regional User Group to connect with local Databricks users. Events will be happening in your city, and you won’t want to miss the chance to attend and share knowledge.

If there isn’t a group near you, start one and help create a community that brings people together.

Request a New Group