POST_START

Investigating an Authorization Failure After a Schema Move

I was working on a data pipeline that recently moved several schemas from the default catalog to the production catalog. After the move, some users started reporting authorization errors when trying to access the schemas. I needed to investigate why this was happening and figure out how to resolve it.

Checking the Schemas in the Production Catalog

I first wanted to verify what schemas were currently present in the production catalog. I ran the SHOW SCHEMAS IN production; command to get a list of schemas.

SHOW SCHEMAS IN production;

I saw a representative result like this:

databaseName
default
sales
finance

This confirmed that the schemas had indeed been moved, and the production catalog now contained the expected databases.

Reviewing Catalog-Level Privileges

Next, I wanted to check what privileges were granted at the catalog level for the production catalog. I ran the SHOW GRANTS ON CATALOG production; command to see who had access.

SHOW GRANTS ON CATALOG production;

I saw a representative result like this:

principal	actionType	objectType
data_analysts	USE CATALOG	CATALOG
data_engineers	CREATE SCHEMA	CATALOG

This showed that the data_analysts group had the USE CATALOG privilege, which allows them to access the catalog, and the data_engineers group had the CREATE SCHEMA privilege. But this didn’t explain the access issues users were facing at the schema level.

Examining Schema-Level Privileges

To dig deeper, I turned to the system catalog and ran the SELECT * FROM system.information_schema.schema_privileges WHERE catalog_name = 'production'; query to see what privileges were assigned to specific schemas.

SELECT * FROM system.information_schema.schema_privileges WHERE catalog_name = 'production';

I saw a representative result like this:

grantee	privilege_type	catalog_name	schema_name
data_analysts	USE SCHEMA	production	finance
data_engineers	USE SCHEMA	production	finance

This revealed that only the data_analysts and data_engineers groups had the USE SCHEMA privilege on the finance schema in the production catalog. However, I noticed that the sales schema was not listed here at all. That was the key clue.

It turned out that the sales schema had not been properly granted access to the relevant users after the move. While the USE CATALOG privilege was in place, the schema-level access was missing. This meant users who needed access to sales couldn’t see or interact with it.

I then took the necessary steps to grant the appropriate privileges to the sales schema for the affected groups. This resolved the authorization failure and restored access for the users.