POST_START

Investigating a Production View That Lost Access to Its Underlying Data

I was troubleshooting an issue where a production view in our Databricks Unity Catalog was no longer accessible. The view, production.reporting.daily_sales, was supposed to provide daily sales data to our analytics team, but users were now getting errors when trying to query it. I needed to figure out why the view had lost access to its underlying data.

Understanding the View Definition

I started by checking the definition of the view to see what it was referencing. I ran the SHOW CREATE TABLE command to retrieve the SQL that defines the view.

SHOW CREATE TABLE production.reporting.daily_sales;

createtab_stmt
CREATE VIEW production.reporting.daily_sales AS SELECT …

The output showed that the view was built on the production.sales.orders table. This confirmed that the view was dependent on the underlying table. But it didn’t explain why users couldn’t access it anymore.

Checking Access Permissions

Next, I checked the access permissions for the view itself to see who had the ability to query it. I ran the SHOW GRANTS ON VIEW command.

SHOW GRANTS ON VIEW production.reporting.daily_sales;

principal	actionType	objectType
data_analysts	SELECT	VIEW
reporting_engineers	SELECT	VIEW

The view had SELECT permissions granted to both data_analysts and reporting_engineers. This indicated that the issue wasn’t with the view’s permissions—it was likely a problem with the underlying data source.

Verifying Underlying Table Access

To confirm this, I checked the access permissions on the production.sales.orders table. I ran the SHOW GRANTS ON TABLE command to see who had access to the table itself.

SHOW GRANTS ON TABLE production.sales.orders;

principal	actionType	objectType
data_analysts	SELECT	TABLE
data_engineers	MODIFY	TABLE

The output showed that data_analysts had SELECT access to the table, which matched the permissions on the view. However, the reporting_engineers group did not have access to the table. This meant that while they could query the view, they couldn’t access the underlying data.

Resolving the Access Issue

I realized that the reporting_engineers group needed access to the production.sales.orders table in order to query the view. I coordinated with the security team to grant them SELECT permissions on the table. Once this was done, the view became accessible again for all intended users.

This experience highlighted the importance of understanding the access chain in Databricks Unity Catalog. A view is only as accessible as its underlying data, and permissions must be carefully managed across both the view and its source table.