ACCOUNT_USAGE is off-limits. Can you still see who can read this table?

You need to know who can read crosshire.silver.orders — every role and every user, all the way down. The view built for exactly that, ACCOUNT_USAGE.GRANTS_TO_ROLES, sits in the SNOWFLAKE database, and on most accounts it is sealed behind ACCOUNTADMIN: it lays bare every grant in the account, so handing it out is itself a security decision. You are not an admin. So you reach for the one command you can run:

SHOW GRANTS ON TABLE crosshire.silver.orders;

Two rows come back. SELECT to CROSSHIRE_ETL_SILVER, OWNERSHIP to CROSSHIRE_PLATFORM. Two roles. You could write that down as the answer and move on.

You would be wrong. A grant on an object is not the answer; it is the first hop of the answer. CROSSHIRE_ETL_SILVER rolls up into another role. That role rolls up into another. The chain ends on database roles, and on people. If anyone — a departed employee, a contractor, a service account — still holds any role along it, they read the table just the same. None of that is in your two rows.

That gap has a name worth keeping: the one-hop lie. SHOW GRANTS ON <object> is scrupulously honest about exactly one edge of the graph, the roles sitting directly on the object, and silent about the transitive closure that is the real access list. Role hierarchy, database-role nesting, and the final role-to-user grants sit one, two, five hops further up. SHOW will not take a single step toward them.

If you did hold the grant, the answer is a tidy recursive query, and the next section writes it out in full. That section is also where the second problem surfaces: even with the access, ACCOUNT_USAGE lags up to two hours, so it cannot confirm a change you made this morning. Restricted, or stale. Either way, not the tool in your hand right now.

* SHOW output is not a table — you reach it with RESULT_SCAN(LAST_QUERY_ID()), which is where §6 spends its time.

At the moment the answer matters most — an incident, the morning after an offboarding — every easy source is either lying or late. SHOW GRANTS ON shows one hop. ACCOUNT_USAGE runs hours behind. Walking the graph live is the only answer that is current the instant you ask, and true as far as your own role can see.

There is a clean answer to the graph question, and Snowflake ships it. It sits behind a door most engineers do not hold the key to. If your role can read the SNOWFLAKE shared database, this whole note is a footnote: ACCOUNT_USAGE publishes the role graph as two flat tables — GRANTS_TO_ROLES and GRANTS_TO_USERS — and a recursive CTE walks it in one statement.

WITH RECURSIVE reach AS (
  -- seed: roles granted directly on the object
  SELECT grantee_name AS role, 1 AS hop
  FROM snowflake.account_usage.grants_to_roles
  WHERE name = 'ORDERS'
    AND table_catalog = 'CROSSHIRE'   -- pin the db + type so a same-named
    AND table_schema  = 'SILVER'      -- object elsewhere can't over-match
    AND granted_on = 'TABLE'
    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 the reachable roles down to the actual humans + service users
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;

Two predicates make this a read audit, not a grant dump. The seed keeps only read-conferring grants on the object (privilege IN ('SELECT', 'OWNERSHIP')), and the climb follows only the USAGE edge — in Snowflake, holding a role is a USAGE grant, whereas owning it (OWNERSHIP) is not the same thing, so an owner who can't activate the role is not counted. The final join to GRANTS_TO_USERS is what lands it on the people — the same depth the live procedure reaches. One honesty note, since the rest of this piece preaches it: the column labels here (name, table_catalog, table_schema, granted_on, deleted_on) and the exact database-role value drift across Snowflake versions the same way SHOW output does — and database-role names come back DB.ROLE-qualified, so the climb's role-to-role join may need to match on that form. This is the one path in the note we have not run against a live account — confirm the columns and the join on yours before you trust a zero.

Two things keep this from being the answer for everyone. Access. ACCOUNT_USAGE lives in the SNOWFLAKE database, granted to ACCOUNTADMIN by default. A mid-privilege data engineer cannot see it, and getting the grant is a ticket, an approval, and a security review. Latency, which bites even if you hold the access. The grants views carry a documented lag, up to about two hours, so the role you revoked at 09:00 can still sit in this result at 10:30. For a quarterly access review that is fine. For “did the offboarding an hour ago actually take?” it is the wrong instrument: it can swear someone is gone while they still hold a role three hops up, or show them present long after they left.

So the rest of this note builds the answer that leans on neither the access nor the clock: the same graph walk, driven live by the one command every role can run against objects it can see — SHOW GRANTS.

The graph has three kinds of seed, and each wants a different direction of travel. This note builds one of them — the object — end to end; the other two are their own notes and reuse this one's output row exactly. An object is a terminal source: nothing is granted to a table, so you only ever climb upward from it, toward the roles and users who can reach it. (A user is the mirror image — a terminal sink you only walk downward; a role sits mid-graph and needs both directions. Same schema, different entry point.)

object_access writes a 12-column row — deliberately flat, so the companion procedures UNION into the same report: SEED_KIND, SEED, LVL, PRINCIPAL, PRINCIPAL_TYPE, PRIVILEGE, GRANTED_ON, OBJECT_NAME, DIRECTION, ACCESS_PATH, STATUS, DETAIL. Two columns carry the whole story. ACCESS_PATH is the chain of hops rendered as role -> role -> user, so a reader can see how a principal reaches the object, not just that they do. STATUS is OK for a resolved hop and ERROR for a branch the caller could not read — which, under EXECUTE AS CALLER, is a feature, and §5 explains why.

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
AS $$
# ... handler below ...
$$;

The procedure lives in one central schema — a utility database you can create procedures in — and takes the target as a fully-qualified string, so one install audits every database regardless of where you call it from. Grant USAGE on the procedure to the roles that should run it, then call fully-qualified from any context.

Installed. Now watch it walk. The whole thing is one idea applied over and over: ask SHOW GRANTS a question, act on each answer, and follow every answer that is itself a role. run does the seed: SHOW GRANTS ON the object, emit one direct row per grantee, and for any grantee that is a role, hand off to climb. Each climb runs one more SHOW GRANTS OF ROLE — who holds this role — emits the inherited rows, and recurses on any grantee that is itself a role. It stops at a user, because a user grants nothing onward.

def climb(session, seed, 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 {dbrole_ref(role, obj_db(obj))}' if is_db
           else f'SHOW GRANTS OF ROLE "{role}"')
    rows, err = _show(session, cmd, out)
    for r in rows:
        who, wtyp = grantee(r), gtype(r)
        if not who or who in chain or is_system(who):
            continue                          # cycle + pseudo-role guard
        new_chain = [who] + chain
        out.append(row(principal=who, principal_type=wtyp, privilege=priv,
                       granted_on=gon, object_name=obj, direction="inherited",
                       access_path=" -> ".join(new_chain), status="OK"))
        if wtyp == "ROLE":
            climb(session, seed, who, False, priv, gon, obj, new_chain, out, depth+1)
        elif is_db_type(wtyp):
            climb(session, seed, who, True,  priv, gon, obj, new_chain, out, depth+1)
        # USER is terminal — no recursion

Three edge cases are not optional. Cycles. Snowflake role hierarchies are meant to be acyclic, but a misconfigured account can grant in a loop; the who in chain guard means a cycle terminates instead of recursing until the depth cap. Database roles. A database role is not an account role — you inspect it with SHOW GRANTS OF DATABASE ROLE "DB"."ROLE", a different command, and a database role can be granted to another database role, so the climb has to hop db-role -> db-role as readily as role -> role. System pseudo-roles. Snowflake's internal roles — the SYSTEM$ family — cannot be inspected with SHOW GRANTS OF ROLE and raise if you try, so is_system() skips them rather than letting one error abort the walk.

Footgun one: the column names drift. SHOW GRANTS is a metadata command, and the exact column labels in its result have varied across Snowflake versions and grant shapes — the grantee has appeared as grantee_name and as grantee; the grantee's type as granted_to, granted_to_type, and grantee_type. Hard-code one label and the procedure returns clean, empty, wrong results on an account where the column is named the other thing. The fix is a getter that tolerates every variant seen in the wild:

def g(d, *keys):
    for k in keys:
        if d.get(k) is not None: return d[k]
    return None

def grantee(d): return g(d, "grantee_name", "grantee")
def gtype(d):   return g(d, "granted_to", "granted_to_type", "grantee_type")

Footgun two: whose eyes is this running through. The procedure is EXECUTE AS CALLER, so every SHOW runs with the caller's visibility. A branch the caller's role cannot see does not silently vanish and does not abort the walk — it comes back as a status='ERROR' row marked [UNREADABLE], and the climb continues down every branch it can see. That is the right default for a self-service audit: an analyst running object_access gets a truthful map of the graph as their role experiences it, with the blind spots labelled rather than hidden.

When you need the all-seeing version — a governance sweep that must not have blind spots — you flip the procedure to EXECUTE AS OWNER and grant the owning role MANAGE GRANTS. Now every SHOW runs with full visibility and the [UNREADABLE] rows disappear. The trade is real and worth stating out loud: caller-rights gives every user a safe, scoped view; owner-rights concentrates all-seeing power in one procedure and one role. Which one you install is a security-posture decision, not a default — make it deliberately, and have someone who owns RBAC on the account sign it off.

A grant audit that is quietly, plausibly wrong is worse than no audit at all — because someone will trust it. Make the blind spots loud, and make the caller-versus-owner call on purpose.

A procedure that RETURNS TABLE does not let you SELECT … FROM object_access(…) directly. You CALL it, then read the result out of the cache with RESULT_SCAN(LAST_QUERY_ID()). And here is the trap that costs an afternoon: LAST_QUERY_ID() always points at the immediately preceding statement. The moment you run the first SELECT … RESULT_SCAN(LAST_QUERY_ID()), the next LAST_QUERY_ID() refers to that SELECT — not the CALL — so a second slice scans the wrong result. Capture the id once, right after the call, and reuse the variable:

CALL object_access('TABLE crosshire.silver.orders');
SET qid = LAST_QUERY_ID();       -- grab the CALL's id immediately

-- reuse $qid freely; it is stable no matter what runs next
SELECT lvl, principal, principal_type, privilege, access_path
FROM TABLE(RESULT_SCAN($qid))
WHERE status = 'OK'
ORDER BY lvl, principal;

-- just the humans, and the path each one takes in
SELECT lvl, principal, access_path
FROM TABLE(RESULT_SCAN($qid))
WHERE principal_type = 'USER'
ORDER BY lvl;

-- the blind spots: branches this caller could not read
SELECT status, detail, access_path
FROM TABLE(RESULT_SCAN($qid))
WHERE status <> 'OK';

Two constraints ride along with the variable. SET qid = LAST_QUERY_ID(); must be the very next statement after the CALL — anything in between captures the wrong id. And both the variable and the cached result are session-scoped: $qid lives only in your current session, and RESULT_SCAN can re-read a query's result for up to 24 hours within that session. Open a new worksheet and both are gone — you re-run the CALL.

If you would rather not depend on session state at all, materialize the result once into a temp table and slice it with ordinary SQL — join it, aggregate it, GROUP BY lvl — with no query-id bookkeeping:

CALL object_access('TABLE crosshire.silver.orders');
CREATE OR REPLACE TEMP TABLE access_map AS
  SELECT * FROM TABLE(RESULT_SCAN(LAST_QUERY_ID()));

SELECT principal_type, COUNT(DISTINCT principal) AS principals
FROM access_map WHERE status = 'OK'
GROUP BY principal_type;

The temp table persists for the whole session regardless of what you run in between. That is usually the cleaner choice the moment you intend to slice the same result more than twice.

It reads the graph, not future intent. The walk reports current grants. A role that could be granted the object tomorrow is not in the result, and should not be. Caller-rights bounds the truth. Under EXECUTE AS CALLER the report is complete only up to the caller's own visibility; read the [UNREADABLE] rows as “there is more here that your role cannot see,” not as “this branch is empty.” Column labels drift. The getter in §5 covers the variants seen so far; a future Snowflake release could add another, and the symptom — clean, empty, wrong — is quiet. When you install this, confirm the actual column names on your account with the procedure's debug flag before you trust a zero. It is a snapshot, not a history. The walk answers “who can reach this now.” It cannot answer “who could last Tuesday” — there is no deleted_on trail to replay. That backward-looking question is the one thing ACCOUNT_USAGE does that this does not; when you need the forensic timeline, the lagged history is the right tool, and this is the wrong one.

Everything above in one procedure — the seed hop, the recursive climb, the column-drift getter, the cycle and SYSTEM$ guards, and the [UNREADABLE] rows that keep a caller's blind spot from aborting the walk. This is the artifact. Scrub the identifiers for your own account, and confirm the actual SHOW GRANTS column labels with the debug flag before you trust a zero.

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
AS $$
COLS = ["SEED_KIND", "SEED", "LVL", "PRINCIPAL", "PRINCIPAL_TYPE", "PRIVILEGE",
        "GRANTED_ON", "OBJECT_NAME", "DIRECTION", "ACCESS_PATH", "STATUS", "DETAIL"]
_CTX = {"seed_kind": "OBJECT", "seed": ""}

# --- tolerate SHOW GRANTS column-name drift (see the footguns section) ---
def g(d, *keys):
    for k in keys:
        if d.get(k) is not None: return d[k]
    return None
def grantee(d): return g(d, "grantee_name", "grantee")
def gtype(d):   return g(d, "granted_to", "granted_to_type", "grantee_type")

def is_system(name):
    return bool(name) and name.upper().startswith("SYSTEM$")
def is_db_type(t):
    return (t or "").upper().replace("_", " ") == "DATABASE ROLE"

def obj_db(obj):
    ident = obj.split()[-1]              # 'TABLE crosshire.silver.orders' -> ident
    return ident.split(".")[0].strip('"')
def dbrole_ref(role, db):
    if "." in role:
        d, r = role.split(".", 1)
        return f'"{d}"."{r}"'
    return f'"{db}"."{role}"'

def row(principal, principal_type, privilege, granted_on, object_name,
        direction, access_path, status, detail=""):
    lvl = 0 if direction in ("error", "meta") else len(access_path.split(" -> "))
    return (_CTX["seed_kind"], _CTX["seed"], lvl,
            principal, principal_type, privilege, granted_on, object_name,
            direction, access_path, status, detail)

def _show(session, cmd, out):
    try:
        return [r.as_dict() for r in session.sql(cmd).collect()], None
    except Exception as e:                 # caller could not read this branch
        out.append(row("[UNREADABLE]", "", "", "", "",
                       "error", cmd, "ERROR", str(e)[:200]))
        return [], str(e)

def climb(session, seed, 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 {dbrole_ref(role, obj_db(obj))}' if is_db
           else f'SHOW GRANTS OF ROLE "{role}"')
    rows, err = _show(session, cmd, out)
    for r in rows:
        who, wtyp = grantee(r), gtype(r)
        if not who or who in chain or is_system(who):
            continue                          # cycle + pseudo-role guard
        new_chain = [who] + chain
        out.append(row(principal=who, principal_type=wtyp, privilege=priv,
                       granted_on=gon, object_name=obj, direction="inherited",
                       access_path=" -> ".join(new_chain), status="OK"))
        if wtyp == "ROLE":
            climb(session, seed, who, False, priv, gon, obj, new_chain, out, depth+1)
        elif is_db_type(wtyp):
            climb(session, seed, who, True,  priv, gon, obj, new_chain, out, depth+1)
        # USER is terminal -- no recursion

def run(session, obj, debug=False):
    seed = obj.strip()
    _CTX["seed_kind"], _CTX["seed"] = "OBJECT", seed
    out = []
    rows, err = _show(session, f"SHOW GRANTS ON {seed}", out)   # the one seed hop
    for r in rows:
        who, wtyp = grantee(r), gtype(r)
        priv, gon = g(r, "privilege"), g(r, "granted_on")
        if not who or is_system(who):
            continue
        out.append(row(principal=who, principal_type=wtyp, privilege=priv,
                       granted_on=gon, object_name=seed, direction="direct",
                       access_path=who, status="OK"))
        if wtyp == "ROLE":
            climb(session, seed, who, False, priv, gon, seed, [who], out)
        elif is_db_type(wtyp):
            climb(session, seed, who, True,  priv, gon, seed, [who], out)
    if debug and rows:                        # emit the real column labels once
        out.append(row("(columns)", "INFO", "", "", seed, "meta",
                       " | ".join(sorted(rows[0].keys())), "OK",
                       "SHOW GRANTS column labels on this account"))
    return session.create_dataframe(out, schema=COLS)
$$;

Read it back the way §6 lays out. To query the same result set more than once, pin the id the instant the call returns — CALL object_access('TABLE crosshire.silver.orders'), then SET qid = LAST_QUERY_ID() — and RESULT_SCAN($qid) as many slices as you need. When the analysis runs long, or you want ordinary joins and aggregates, store the whole result once — CREATE TEMP TABLE access_map AS SELECT * FROM TABLE(RESULT_SCAN(LAST_QUERY_ID())) — and run your analysis against that table instead. Same rows, no query-id bookkeeping.