Permissions on Unity Catalog Table Constraints
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
02-07-2024 02:19 AM
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
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
02-12-2024 05:59 AM
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.
In Databricks, granting ALL PRIVILEGES on the object means you are granting the below permissions that do not include all the DDL operations.
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
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
10-23-2024 11:33 AM
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.
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
05-15-2024 11:04 AM
So how does one grant these permissions to non-owners?
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
09-10-2024 03:57 AM
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.