SHOW GRANTS ON a table shows only the roles granted directly on it — one hop — not the people who reach it through a chain of roles. Here is the real answer, every role and user who can read an object, transitively, done two ways: a live procedure that needs only SHOW, and the ACCOUNT_USAGE CTE as a history-capable fallback.
Everything here is open source — both queries live in the GitHub repository ↗, and the whole story is in the field note ↗.
Most guides say “query ACCOUNT_USAGE.” But that schema is restricted to ACCOUNTADMIN for security and lags up to a few hours — so most engineers can’t use it. The working answer is the procedure; ACCOUNT_USAGE is the fallback, and the only source that can also answer historically.
Not INFORMATION_SCHEMA? OBJECT_PRIVILEGES answers a different question — what privileges exist directly on an object (one hop) — not who can read it transitively. It has no role-hierarchy view, so it can’t walk the graph.
It loops SHOW GRANTS: on the object, then SHOW GRANTS OF ROLE up every branch, hopping database roles, guarding cycles and SYSTEM$ pseudo-roles, until it lands on users.
-- a Snowpark procedure that walks SHOW GRANTS live
CREATE OR REPLACE PROCEDURE object_access(obj STRING, debug BOOLEAN DEFAULT FALSE)
RETURNS TABLE (seed_kind STRING, seed STRING, lvl NUMBER, principal STRING,
principal_type STRING, privilege STRING, granted_on STRING,
object_name STRING, direction STRING, access_path STRING,
status STRING, detail STRING)
LANGUAGE PYTHON RUNTIME_VERSION = '3.11'
PACKAGES = ('snowflake-snowpark-python')
HANDLER = 'run' EXECUTE AS CALLER -- runs with the CALLER's visibility
AS $$
# tolerate SHOW GRANTS column-name drift across versions
def grantee(d): return d.get("grantee_name") or d.get("grantee")
def gtype(d): return (d.get("granted_to") or d.get("granted_to_type")
or d.get("grantee_type"))
def is_system(n): return bool(n) and n.upper().startswith("SYSTEM$")
def climb(session, role, is_db, priv, gon, obj, chain, out, depth=0, maxd=15):
if depth > maxd: return # depth backstop
cmd = (f'SHOW GRANTS OF DATABASE ROLE "{role}"' if is_db
else f'SHOW GRANTS OF ROLE "{role}"')
for r in [x.as_dict() for x in session.sql(cmd).collect()]:
who, wtyp = grantee(r), gtype(r)
if not who or who in chain or is_system(who): continue # cycle + SYSTEM$ guard
new = [who] + chain
out.append((who, wtyp, priv, gon, obj, "inherited",
" -> ".join(new), "OK"))
if wtyp == "ROLE":
climb(session, who, False, priv, gon, obj, new, out, depth+1)
# DATABASE ROLE recurses the same way; USER is terminal
def run(session, obj, debug=False):
out = []
for r in [x.as_dict() for x in session.sql(f"SHOW GRANTS ON {obj}").collect()]:
who, wtyp = grantee(r), gtype(r)
if not who or is_system(who): continue
out.append((who, wtyp, r.get("privilege"), r.get("granted_on"),
obj, "direct", who, "OK"))
if wtyp == "ROLE":
climb(session, who, False, r.get("privilege"), r.get("granted_on"),
obj, [who], out)
return session.create_dataframe(out) # full 12-col version in the repo
$$;A procedure that RETURNS TABLE is read out of the result cache — mind the query-id:
-- read it back: LAST_QUERY_ID() names the PREVIOUS statement, so PIN it
CALL object_access('TABLE crosshire.silver.orders');
SET qid = LAST_QUERY_ID(); -- grab the CALL's id immediately
SELECT lvl, principal, principal_type, access_path
FROM TABLE(RESULT_SCAN($qid))
WHERE status = 'OK'
ORDER BY lvl, principal;
-- or materialize once and forget query-ids
CALL object_access('TABLE crosshire.silver.orders');
CREATE OR REPLACE TEMP TABLE access_map AS
SELECT * FROM TABLE(RESULT_SCAN(LAST_QUERY_ID()));A recursive CTE that audits reads, not grants: the seed keeps only read-conferring privileges (SELECT, OWNERSHIP), and the climb follows only the USAGE hierarchy edge — holding a role is a USAGE grant, owning it is not. The repo also ships an object-independent alternate. Drop the deleted_on IS NULL filters to answer historically.
-- full graph + HISTORY, but admin-gated + lagged
WITH RECURSIVE reach AS (
-- seed: roles that can READ the object directly
SELECT grantee_name AS role, 1 AS hop
FROM snowflake.account_usage.grants_to_roles
WHERE granted_on = 'TABLE'
AND name = 'ORDERS' AND table_catalog = 'CROSSHIRE' AND table_schema = 'SILVER'
AND privilege IN ('SELECT','OWNERSHIP') -- read-conferring only
AND deleted_on IS NULL
UNION ALL
-- climb: who HOLDS a role we reached — USAGE is the hierarchy edge
SELECT g.grantee_name, r.hop + 1
FROM snowflake.account_usage.grants_to_roles g
JOIN reach r ON g.name = r.role
WHERE g.granted_on IN ('ROLE','DATABASE_ROLE')
AND g.privilege = 'USAGE' -- owning a role != holding it
AND g.deleted_on IS NULL
)
-- resolve reachable roles down to the actual USERS (essential — without this
-- join the answer stops one hop short of the people)
SELECT DISTINCT u.grantee_name AS user_name, r.role, r.hop
FROM reach r
JOIN snowflake.account_usage.grants_to_users u
ON u.role = r.role AND u.deleted_on IS NULL
ORDER BY r.hop, user_name;The procedure runs with the caller's own visibility. A branch the caller cannot see does not crash the walk — it returns a status='ERROR' [UNREADABLE] row, and the climb continues everywhere it can see. Complete up to your role, blind spots labelled not hidden. For an all-seeing sweep, flip to EXECUTE AS OWNER + MANAGE GRANTS.
SHOW GRANTS column labels vary across versions — grantee_name vs grantee, granted_to vs granted_to_type vs grantee_type. Hard-code one and you get clean, empty, wrong results. The getter tolerates every variant; run with debug => TRUE once to print the real labels.
A procedure that RETURNS TABLE is read with RESULT_SCAN(LAST_QUERY_ID()). But LAST_QUERY_ID() always names the PREVIOUS statement — the first SELECT moves it off the CALL. Pin it with SET qid = LAST_QUERY_ID() the instant the call returns, or materialize into a TEMP TABLE.
The live walk answers who can reach this now. It has no DELETED_ON trail, so it cannot replay who could last Tuesday — that forensic timeline is the one thing the ACCOUNT_USAGE rung does that this cannot.
This is the first audit. The library grows the way the Databricks audit did — each domain a folder, each question answered from the most accessible source that can answer it.
Confidence note: the ACCOUNT_USAGE column labels and the SHOW GRANTS output shape drift across Snowflake versions. The queries mark themselves unverified-live where they have not been run against a live account — confirm the columns on yours before you trust a zero. Identifiers are scrubbed to placeholders.