cancel
Showing results for 
Search instead for 
Did you mean: 
Community Platform Discussions
Connect with fellow community members to discuss general topics related to the Databricks platform, industry trends, and best practices. Share experiences, ask questions, and foster collaboration within the community.
cancel
Showing results for 
Search instead for 
Did you mean: 

Permissions on Unity Catalog Table Constraints

RobsonNLPT
Contributor

Hi all.

I've used new options to add constraints to UC tables

Even granting permissions to an user (ALL PRIVILEGES) on particular schema we have errors when trying to add PKs. The message doesn't make sense (PERMISSION_DENIED: User is not an owner of Table).The only option to solve this issue is granting ownership on tables he is working.I was expecting ALL PRIVILEGES to cover all DDL operations. Is this an issue on this public preview?

Best

Robson

 

 

 

4 REPLIES 4

saikumar246
Databricks Employee
Databricks Employee

Hi, @RobsonNLPT Thank you for bringing your concern here.

I understand that you are granting (ALL PRIVILEGES) on a particular schema to the user and while that user is trying to add the Primary Key, getting (PERMISSION_DENIED: User is not an owner of Table). It is expected, because in Databricks altering the table, deleting the table or adding the PKs(any alter operations on the table) should need owner privilege on the table. Please see the below link for your reference regarding adding PKs.

https://docs.databricks.com/en/machine-learning/feature-store/uc/feature-tables-uc.html#:~:text=Only...

In Databricks, granting ALL PRIVILEGES on the object means you are granting the below permissions that do not include all the DDL operations.

saikumar246_0-1707745880381.png

You can follow this link to understand more about the General Unity Catalog privilege types in the Databricks:- https://docs.databricks.com/en/data-governance/unity-catalog/manage-privileges/privileges.html#gener...

Please leave a like if this answers your question, follow-ups are appreciated.

Regards,

Sai Kumar

This is an accurate answer, but it misses the point that the design implemented is an anti-pattern. These permissions need to be assignable privileges through Unity Catalog, not managed by "owners".

Here are 3 example bad outcomes of the design:

One: we have Groups A and B working in a given schema. Tables in this schema are "theirs" and we want them to create, update, delete, etc. We also have an admins group C, and a group for service principals D that need to have permissions. Instead of assigning the appropriate permissions to each group and being done, we have to create an additional Group E containing these groups to be the "owner" of the schema. It's not a big deal for one schema, but having hundreds of schemas and dozens of combinations of "owners" adds up to a lot of hassle.

Two: Perhaps most impactful, we want users to be able to create, edit, modify, and delete tables within a schema, but not necessarily to make any changes to the schema. To do this, we have to make them (or their group) owners of the tables in the schema. Our solution has been to make these users "owners" of the schema so that they can update the "owner" of the tables in it as needed to delete or update DDL. The "owner" permissions on the schema provide expanded permissions to it that we don't really want to give them, but the only alternative is to have our admin team manually update ownership on tables and/or do the DDL changes and table drops for them, but that's just too onerous. 

Three: For many schemas we want to allow editing in our development environment but we want to prevent users from manually changing these in our testing or production environments. As "owners" have privileges on the object, we can't have the same owner set in all environments, which becomes a maintenance headache for anyone using automated deployment tools like Terraform. This requires putting each owner group in a variable to manage the difference between environments, which is doable but messy and SHOULD be unnecessary. 

These headaches would go away by having "EDIT DDL" (or cover this with MODIFY) and "DROP TABLE" permissions on tables, and inheritable from the parent schema and catalog. Similarly DROP SCHEMA, MODIFY SCHEMA, DROP CATALOG, and MODIFY CATALOG permissions are needed and just make sense for the UC permissions model. 

dmart
New Contributor III

So how does one grant these permissions to non-owners?

ATN
New Contributor II

I think the easiest way would be for a workspace admin to create a identity and access group and add the users and service principals who needs to run DDL operations to that group. Ownership of tables could then be transfered to this group.

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