Object ownership is getting changed on dropping and recreating tables

Use TRUNCATE or REPLACE for tables and ALTER VIEW for views instead of dropping and recreating them.

Written by akash.bhat

Last published at: December 15th, 2022

Problem

Ownership of SQL objects changes after dropping and recreating them. This can result in job failures due to permission issues.

Cause

In Databricks Runtime 7.3 LTS, when jobs are run with table ACLs turned off, any action that drops and recreates tables or views preserves the table ACLs that was set the last time the job was run with table ACLs turned on.

In Databricks Runtime 9.1 LTS and above this behavior changed. Any action that drops a table or view clears the table ACL state.

Solution

You should use TRUNCATE or REPLACE for tables and ALTER VIEW for views instead of dropping and recreating them.

To replace a view, you should be the owner of the view or an administrator.

Delete

Info

If you want to restore the behavior from Databricks Runtime 7.3 LTS, you can add spark.databricks.acl.enforceTableOwnerAssignment false to the cluster's Spark config.

spark.databricks.acl.enforceTableOwnerAssignment was introduced in Databricks Runtime 9.1 LTS. 

Previously, when objects were created outside of a table ACL enabled cluster the ACL system had no knowledge of them. A Databricks administrator would have to set ownership permissions for new objects and clean up dangling permissions for deleted objects.

Now, objects created outside of Databricks SQL or table ACL enabled clusters create representations in the ACL system, assigning ownership automatically or dropping permissions as needed.