Federated queries
Run SQL across Snowflake accounts with short-lived OAuth tokens
Machine-to-machine access through the SQL API, without static credentials.
Kevin Keller · kevinkeller.org
Why it matters
Automation between accounts needs an identity
- One account must execute SQL on another, with no human logging in
- Who does the SQL run as on the target account?
- How are tokens obtained, refreshed and scoped?
- The answer decides how much a leaked credential can do
The pattern
Two sides, two jobs
- Account A obtains tokens: network rule, auth integration, secret, external access
- A Python UDF fetches a token and calls Account B's SQL API
- Account B validates tokens: External OAuth maps them to a user and role
- Any OAuth 2.0 IdP works: Entra ID, GCP, Okta, Auth0, Ping
Option A: Client Credentials
Snowflake handles the whole token lifecycle
- Register an app in the IdP with application permissions
- Account A integration uses
OAUTH_GRANT = 'CLIENT_CREDENTIALS' - An OAUTH2 secret references it
- The UDF calls
_snowflake.get_oauth_access_token('cred') - Snowflake caches the token and refreshes it before expiry
Option B: Self-signed JWT
When the IdP only gives you a private key
- GCP service accounts offer no client credentials endpoint
- The private key is stored as a Snowflake secret
- The UDF signs a short JWT with
kid,iss,aud,scpandname - Account B checks the signature against the IdP's JWKS URL
- No token exchange at runtime, fewer network hops
The key point
The token's claims decide who the SQL runs as
No interactive login in Account A. Account B maps the token to a user and role, and logs it in query and login history.
Ranking the options
OAuth first, key-pair last
| OAuth | PAT | Key-pair | |
|---|---|---|---|
| Recommendation | Preferred | Acceptable | Last resort |
| Role scoping | Yes | Yes | No |
| Expiry | Short-lived | Configurable | None |
| Rotation | Automatic | Manual | Manual |
Security considerations
What to plan for
- Network policies: Account A's egress IPs are not predictable; PrivateLink can help
- Least privilege: limit available roles in the OAuth integration
- Secrets: encrypted, governed by RBAC, hidden from
DESCRIBE SECRET - Audit: requests attributed to the token's user or service principal
Takeaway
Account A obtains, Account B validates, and short-lived tokens beat static keys.
Kevin Keller · kevinkeller.org