Unity Catalog permissions approach for strict controls when creating tables

[ajbush]

Hi all,

I'm deploying Unity Catalog into a large enterprise with quite strict controls. I want to give my users some autonomy over creating tables from upstream datasets they have select access on, but also restrict these controls. I've been through all the Unity Catalog permissions docs, however are a few key requirements that I can't seem to implement in Unity and I'm wondering if I missed something.

I have a simple Catalog structure like the following:

catalog_1/
├─ schema1/
│  ├─ table1 (user1)
│  ├─ table2 (user2)
├─ schema2/
│  ├─ table3 (user1)

The idea is that my users only have access permissions assigned within the schema, therefore I don't want to grant owner on the schema object. One of the main requirements I have is to protect against data loss, therefore a drop schema cascade operation from a user is a huge risk.

The key requirements I have are:

1. Within a schema, to allow a user to drop a table they did not create. In the example, I would like user2 to be able to drop table1. Because of the ownership model (i.e., user2 is neither a schema owner or table owner since they did not create it) then user2 is not able to drop the table, even though they have the ALL PERMISSIONS grant on the schema. Unless I'm missing something, either I will have to assign owner to all my users (using a group) at the schema level, ask each user to change the owner on the table they just created to the group or have a batch job fix up the permissions and transfer ownership of all the tables.
2. For a user to be able to create a table but not be able to assign grants directly on the table to other users. It seems that since the table creator is an owner, they are able to grant whatever permission they like to other users even though the permissions on the schema are not given. Again, I will have to either ask each user to change the owner on the table they just created to someone else or have a batch job fix up the permissions and transfer ownership of all the tables. This also seems that I can have either this requirement or requirement 1 but not both, since only an owner can drop objects but an owner can always grant. Is this the case?
3. The final requirement is that I want my users to create tables in some schemas (e.g., table3 in schema2) but not be able to drop them. I need to protect against accidental drops / deletes but also allow creation. Unity Catalog doesn't appear to have a rollback / history approach and I need a way of permanently storing all tables in some schemas. I could use external tables and storage in ADLS but I need a user friendly way for my users to create tables. Again, an approach I though of here is another batch job that changes the ownership of tables within the schemas but there will be a time delay and is additional overhead.

Any advice at all would be helpful to see if I can meet any of the above requirements.

Thanks

[Priyag1]

A schema contains tables, views, and functions. You create schemas inside catalogs .

Requirements
You must have the USE CATALOG and CREATE SCHEMA data permissions on the schema’s parent catalog. Either a metastore admin or the owner of the catalog can grant you these privileges. If you are a metastore admin, you can grant these privileges to yourself.

All users have the USE CATALOG permission on the main catalog by default.

Your Databricks account must be on the Premium plan or above.

You must have a Unity Catalog metastore linked to the workspace where you perform the schema creation.

The compute resource that you use to run the notebook or Databricks SQL to create the catalog must be using a Unity Catalog compliant access mode.

Create a schema
To create a schema (database), you can use Catalog Explorer or SQL commands.

Data Explorer
SQL
Python
R
Scala
Log in to a workspace that is linked to the metastore.

Click Catalog icon Catalog.

In the Catalog pane on the left, click the catalog you want to create the schema in.

In the detail pane, click Create database.

Give the schema a name and add any comment that would help users understand the purpose of the schema.

(Optional) Specify the location where data for managed tables in the schema will be stored.

Specify a location here only if you do not want managed tables in this schema to be stored in the default root storage location that was configured for the metastore or the managed storage location specified for the catalog (if any). See Create a Unity Catalog metastore.

The path that you specify must be defined in an external location configuration, and you must have the CREATE MANAGED STORAGE privilege on that external location. You can also use a subpath of that path. See Manage external locations and storage credentials.

Click Create.

Assign permissions for your catalog. See Unity Catalog privileges and securable objects.

Click Save.

You can also create a schema by using the Databricks Terraform provider and databricks_schema. You can retrieve a list of schema IDs by using databricks_schemas.