POST_START

Troubleshooting a Service Principal That Lost Production Table Access

Today, I encountered an issue where a service principal that was previously able to access a table in the production catalog suddenly lost access. The team was trying to run a critical ETL job, and the error message indicated that the service principal didn’t have the necessary permissions. I knew that the first step in troubleshooting access issues in Databricks Unity Catalog is to check the current grants on the affected objects.

Checking Catalog-Level Permissions

I started by checking the grants at the catalog level to see if the service principal had access to the production catalog at all. I ran the following SQL command:

SHOW GRANTS ON CATALOG production;

I saw a representative result like this:

principal | actionType | objectType
data_analysts | SELECT | TABLE
data_engineers | MODIFY | TABLE

From this output, I noticed that the service principal in question wasn’t listed here. That meant the service principal either didn’t have access to the catalog at all or the access had been revoked. Since the service principal was supposed to have access, I decided to dig deeper and check the schema-level permissions.

Checking Schema-Level Permissions

Next, I ran the same command but at the schema level to see if the service principal had access to the schema within the production catalog. I executed the following SQL:

SHOW GRANTS ON SCHEMA production.sales;

I saw a representative result like this:

principal | actionType | objectType
data_analysts | SELECT | TABLE
data_engineers | MODIFY | TABLE

Again, the service principal wasn’t listed here. This confirmed that the service principal didn’t have access to the schema, which in turn meant it couldn’t access any tables within that schema. I realized that the issue was likely at the catalog or schema level, and the table-level grants weren’t the problem.

Checking Table-Level Permissions

As a final check, I ran the command to see the grants directly on the table to ensure that the service principal wasn’t somehow granted access through a more specific object. I executed the following SQL:

SHOW GRANTS ON TABLE production.sales.orders;

I saw a representative result like this:

principal | actionType | objectType
data_analysts | SELECT | TABLE
data_engineers | MODIFY | TABLE

Once again, the service principal wasn’t listed. This solidified my understanding that the issue was not at the table level but rather at the catalog or schema level. Since the service principal wasn’t appearing in any of these grants, I concluded that the access had been revoked, possibly due to a misconfiguration or an accidental removal during a permissions update.

Next Steps

With the source of the issue identified, I moved on to restoring access by re-granting the necessary permissions at the appropriate level. I made sure to document the changes and communicate with the team to ensure that this issue wouldn’t recur in the future.