POST_START

Investigating Production Authorization End to End from Identity Through Catalog, Schema, Table, and Policy Access

I started my day by reviewing the latest authorization logs for the production catalog. My task was to understand how a user was able to access a specific table in the production environment. I needed to trace the authorization path from identity through catalog, schema, table, and policy access. To do this, I used a combination of SQL queries and system tables to investigate the access controls in place.

Starting with the Current User

I began by running SELECT current_user(); to identify the user I was investigating. This is the first step in understanding who is interacting with the system. The result came back as alice@company.com, which I noted as the principal for the current session.

Checking Catalog-Level Permissions

Next, I checked the catalog-level permissions for the production catalog using SHOW GRANTS ON CATALOG production;. This helps determine if the user has access at the catalog level, which is a prerequisite for accessing any objects within it. The output showed that alice@company.com had the USAGE privilege on the production catalog, indicating she could access objects within it.

Exploring Schema-Level Access

I then looked into the schema-level access for the sales schema within the production catalog by running SHOW GRANTS ON SCHEMA production.sales;. This is important because access to a schema is required before accessing tables within it. The result showed that alice@company.com had the USAGE privilege on the sales schema, confirming she could access tables in that schema.

Verifying Table Access

To confirm access to the specific table, I ran SHOW GRANTS ON TABLE production.sales.orders;. This query reveals the exact table-level permissions granted to the user. The output indicated that alice@company.com had the SELECT privilege on the orders table, which was the access I was investigating.

Examining Table Details with Extended Description

To get more information about the orders table, I used DESCRIBE TABLE EXTENDED production.sales.orders;. This query provides metadata such as the table location, storage format, and any policies associated with it. I noticed that the table was stored in a Delta Lake format and had a policy applied for data masking, which might be relevant to understanding the access controls in place.

Checking Table Privileges via Information Schema

I then used the information schema to verify the table privileges for the orders table. I ran SELECT * FROM system.information_schema.table_privileges WHERE table_catalog = 'production' AND table_schema = 'sales' AND table_name = 'orders';. This query confirmed the SELECT privilege on the table and also showed that the user had the USAGE privilege on the schema and catalog, reinforcing the access path I had already identified.

Reviewing Audit Logs for Confirmation

Finally, to confirm that the user had actually accessed the table, I reviewed the audit logs using SELECT * FROM system.access.audit WHERE request_params LIKE '%production.sales.orders%' ORDER BY event_time DESC;. The audit logs showed recent queries against the orders table, confirming that the user had not only been granted access but had also used it.

By following this path from identity through catalog, schema, table, and policy access, I was able to fully understand the authorization flow for the orders table. This process is essential for auditing, compliance, and ensuring that access controls are properly enforced in a production environment.