Snowflake's role hierarchy is one of the more elegant permission models in modern data infrastructure — roles inherit from other roles, grants cascade, and a well-designed hierarchy can express complex access patterns cleanly. That same elegance is exactly what makes it hard to review. A standard SOC 2 access review asks "who has access to what, and is that still appropriate?" — a question that's straightforward against a flat permissions table and genuinely difficult against a multi-level role inheritance graph.
Why Standard Access Review Tools Struggle Here
Most SaaS access review and IGA tools ingest a flat list of user-to-permission mappings from an application's API. Snowflake's SHOW GRANTS output gives you that raw data, but it doesn't resolve inherited access — a user assigned to ANALYST_ROLE might effectively have SELECT access to a dozen schemas through three layers of role inheritance that a flat grant list won't surface without additional traversal logic.
This means a reviewer looking at a standard access review dashboard populated from Snowflake's account_usage views often can't actually answer "does this person have access to the PII schema" without manually walking the role graph — which is exactly the kind of manual step that turns into a rubber-stamped review nobody trusts.
What We Build: Resolved-Effective-Access Reporting
Rather than reviewing raw grants, we build a pipeline that resolves the full inheritance chain and produces effective-access reports per user, per object:
-- Recursive CTE resolving Snowflake role inheritance for effective access reporting
WITH RECURSIVE role_hierarchy AS (
SELECT grantee_name AS role_name, name AS granted_role, 0 AS depth
FROM snowflake.account_usage.grants_to_roles
WHERE granted_on = 'ROLE'
UNION ALL
SELECT rh.role_name, g.name, rh.depth + 1
FROM role_hierarchy rh
JOIN snowflake.account_usage.grants_to_roles g
ON g.grantee_name = rh.granted_role
WHERE g.granted_on = 'ROLE' AND rh.depth < 10
)
SELECT ur.grantee_name AS user_name, rh.granted_role AS effective_role
FROM snowflake.account_usage.grants_to_users ur
JOIN role_hierarchy rh ON rh.role_name = ur.role
The output feeds a review UI (or an export into whatever GRC platform is already in place) showing each user's fully-resolved effective access to sensitive schemas, tables, and views — not just their directly assigned role name.
Handling Service Accounts and Their Warehouses Separately
Snowflake environments accumulate service-account roles for BI tools, ETL pipelines, and reverse-ETL jobs, often with broader access than a human reviewer would grant if asked fresh. These need a separate review track from human users, since the "appropriate" test is different — a service account's access should map to what its specific pipeline touches, not to a job function. We tag service-account roles distinctly in the review export so reviewers aren't asked to make a human-appropriateness judgment about a machine identity, and so non-human access gets its own certification cadence.
Building the Evidence Auditors Actually Sample
A SOC 2 auditor reviewing data warehouse access controls typically wants to see: who reviewed access, when, against what criteria, and what remediation happened for flagged exceptions. Our pipeline produces a point-in-time snapshot of resolved effective access for each review cycle, paired with the reviewer's decision log (approve, revoke, escalate) per user-object pair — giving you a defensible evidence trail instead of a spreadsheet someone filled out from memory.
Deprovisioning: Closing the Loop
Reviewing access is only half the control. The other half is verifying that revoked access actually gets removed. Our pipeline includes a follow-up check that re-runs the effective-access resolution after each review cycle and flags any user whose access wasn't actually reduced despite a "revoke" decision — catching the class of failure where a role was removed from a user but a broader parent role inheritance still grants the same access.
This resolved-effective-access approach fits within our security automation capability, alongside patterns covered in SOC 2 access review audit failures.
Ask us what your GRC platform isn't covering: 907-841-8407 or contact@rutagon.com.
Ask us what your GRC platform isn't covering →
Frequently Asked Questions
Why can't Vanta or Drata handle Snowflake access reviews natively?
They can ingest Snowflake's flat grant data through the API, but resolving the multi-level role inheritance into true effective access typically requires custom logic most GRC platforms don't build for a single data warehouse product — that's the gap our pipeline closes.
How deep does Snowflake's role inheritance typically go?
It varies by environment, but three to five levels of role-to-role grants is common in mature Snowflake accounts, especially where functional roles are layered on top of access roles by design.
Does this replace Snowflake's native RBAC, or sit alongside it?
It sits alongside — we don't change how Snowflake's RBAC works, we build the reporting and evidence layer that makes reviewing it tractable for a human auditor sample.
How do you handle newly created roles that haven't been reviewed yet?
New roles are flagged in the next review cycle by comparing the role inheritance graph snapshot against the prior cycle's, so nothing silently joins the effective-access picture without being surfaced.
Can this same pattern apply to other role-based systems besides Snowflake?
Yes — the recursive-resolution approach applies to any system with role-to-role inheritance (AWS IAM group/role chains, some ERP systems); Snowflake is one of the more common places we've seen it cause audit friction.