POST_START

Investigating Revoked Production Access Still Reported by a Consumer

I recently encountered an issue where a consumer reported they were still able to access data in the production catalog, even though their access had been revoked. I needed to investigate why this was happening. To start, I decided to look at the grants assigned to the production catalog.

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

I noticed that the data_analysts group had the USE CATALOG privilege, which allows them to access the catalog. However, the consumer in question was no longer part of this group. This raised a question: was the access still being reported due to some residual privilege or cached information?

To dig deeper, I checked the grants on the sales schema within the production catalog.

SHOW GRANTS ON SCHEMA production.sales;

I saw a representative result like this:

principal	actionType	objectType
data_analysts	USE SCHEMA	SCHEMA
data_engineers	CREATE TABLE	SCHEMA

Again, the data_analysts group had the USE SCHEMA privilege, which allows them to access the schema. But the consumer had been removed from this group. This suggested that the access might not be tied directly to the schema level, but perhaps to the table or another layer.

To confirm, I checked the grants on the orders table in the sales schema.

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

Here, the data_analysts group had the SELECT privilege on the table, which would allow them to query the data. This meant the consumer had access to the table, but their group membership had been revoked. I needed to check if there was any residual access being reported in the audit logs.

I ran an audit query to check for any recent access to the orders table.

SELECT * FROM system.access.audit WHERE request_params LIKE '%production.sales.orders%' ORDER BY event_time DESC;

I saw a representative result like this:

event_time	user_identity	action_name	request_params
2026-09-11 14:32:10	analyst@demo.com	commandSubmit	{table: production.sales.customers}
2026-09-11 14:31:55	engineer@demo.com	getTable	{table: production.sales.customers}

I noticed that the query did not show any access to the orders

. This indicated that the consumer might not have been actively accessing the table. However, the fact that the data_analysts group still had access suggested that the privilege might not have been fully revoked.

I verified that the consumer had been removed from the data_analysts group, and that their access had been explicitly revoked. The issue might have been due to caching or a delay in the propagation of the privilege removal. I decided to monitor the audit logs more closely and also check if the consumer had access to any other tables or schemas that might have been overlooked.