Accidentally removing the service principal that owns the view seems to put the Unity Catalog in an illegal state. Can you fix this?

[fuselessmatt]

I renamed our service principal in Terraform, which forces a replacement where the old service principal is removed and a new principal with the same permission is recreated. The Terraform succeeds to apply, but when I try to run dbt that creates tables a table based on some views, I get the error message Table 'my_view' does not have sufficient privilege to execute. This is slightly misleading as the table is not the view and a view can't have privileges. Principals have privileges on securable objects.

Also, trying to recreate the with the new service principal results in insufficient privilege error.

In contrast, it works neither error occurs for the table that are also owned by the removed SP. The new SP has many privileges on the catalog and thereby the schema. Couldn't the view work in the same way?

UPDATE: This part has been answered in this thread

What makes things worse is that there is no one liner to reassign all views in a catalog or schema. You have to click every view or create a script with the API. This would be nice be a nice feature to have in many circumstances. It could work something like this.

ALTER ALL VIEWS IN SCHEMA my_schema ALTER OWNER TO my_user

[Anonymous]

@Mattias P​ :

It sounds like the issue you're facing is related to permissions and ownership after renaming the service principal in Terraform. Since the new service principal has the same permissions as the old one, it's possible that the ownership of the views and tables that were created by the old service principal was not properly transferred to the new service principal.

To fix this issue, you can try to modify the ownership of the views and tables using SQL commands in your database. Here's an example SQL command that you can use to modify the ownership of all views in a schema:

ALTER VIEW SCHEMA.my_schema.* OWNER TO new_owner;

You can modify this command to target specific views by replacing the * wildcard with a comma-separated list of view names. Similarly, you can modify the ownership of all tables in a schema using the following SQL command:

ALTER TABLE SCHEMA.my_schema.* OWNER TO new_owner;

Again, you can replace the * wildcard with a comma-separated list of table names to target specific tables. Make sure to replace my_schema and new_owner with the appropriate values for your database and new service principal. Once you've modified the ownership of the views and tables, you should be able to run dbt without encountering any permission errors.

[fuselessmatt]

Thanks! This is actually a really helpful (secret?) command to know if I end up in this situation again! I guess I was too stuck in how it worked in Redshift to try that.

Since you are from Databricks, what do you think of the idea of letting the view permissions work the same way as for tables? I guess the behaviour of when the owner is removed is not really defined in the documentation. CREATE TABLE grants creating new tables and views, but it doesn't say it allows for removing old tables or view.

[fuselessmatt]

This is also true for removing groups before unassigning them (removing and unassigning in Terraform)

│ Error: cannot update grants: Could not find principal with name <My Group Name>

[Xanderdr]

@fuselessmatt How did you solve that last issue where you still have grants for a removed group?