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