Identity passthrough
One SAML login. Snowflake as the user, with no secondary-role leak.
A tested Entra ID pattern for per-user RBAC and audit, without a service account.
Kevin Keller ยท kevinkeller.org
Why it matters
The app must query Snowflake as the signed-in user
- So row access and masking policies apply to that person
- So
QUERY_HISTORYshows who actually asked - So nobody has to justify a shared service account to the auditors
Problem 1
A SAML assertion can't be swapped for an OAuth token
Entra ID has no flow that turns a SAML assertion into an OAuth access token. After login the app holds proof of who the user is, but nothing Snowflake will accept.
Problem 2
38
secondary roles stayed active with a role-any token
The app asked for APP_ANALYST. Still active: SECURITYADMIN, ORGADMIN, ACCOUNTADMIN, and the PII schema was readable.
EXTERNAL_OAUTH_ALLOWED_ROLES_LIST caps only the primary role.The pattern
Silent OIDC hop, role-scoped tokens, a second integration
Snowflake, not your app code, decides which roles the app may use.
How it works
Three parts, one login for the user
- Silent OIDC hop: after SAML, redirect to
/authorizewithprompt=none,login_hintand PKCE. No extra UI. - Dedicated resource app: one scope per role, such as
session:role:APP_ANALYST. Never consentrole-any. - Second External OAuth integration: on the v2 issuer, next to the central v1 one.
The result
Snowflake enforces the boundary itself
| Request with a role-scoped token | Result |
|---|---|
| SQL or REST API with a scoped role | Runs, no secondary roles, PII denied |
| Cortex Agent and its SQL tool | Runs as APP_ANALYST only |
| Any role outside the token | Rejected (390317) |
USE SECONDARY ROLES ... | Refused (3107) |
Who owns what
Broad in Entra, narrow in Snowflake
- Identity team: consents a superset of role scopes once, including roles that don't exist yet
- Snowflake team: enables and revokes roles any time with one
ALTERon the allowlist - Adding a role to the allowlist worked immediately
- Removing one revoked it at once, with the same token
Agent identity
The same user, treated as an agent
With IS_AGENTIC = TRUE, sessions stay the end user and scoped role, but IS_AGENT_ACTIVATED is true.
- Agent-aware masking returns
agent-maskedto the app - The same user in Snowsight sees the real value
- The flag flips live, on the next query
Gotchas
The traps are in the details
- The SQL API ignores
X-Snowflake-Role: sendrolein the body - Entra's default SAML NameID isn't the UPN
- Hidden iframes fail in some browsers: use a top-level redirect
- MCP can return auth errors as HTTP 200: inspect the body
The takeaway
role-any looks like least privilege. Role-scoped tokens are.
Personal project, not an official Snowflake product