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

  1. Register an app in the IdP with application permissions
  2. Account A integration uses OAUTH_GRANT = 'CLIENT_CREDENTIALS'
  3. An OAUTH2 secret references it
  4. The UDF calls _snowflake.get_oauth_access_token('cred')
  5. 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, scp and name
  • 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

OAuthPATKey-pair
RecommendationPreferredAcceptableLast resort
Role scopingYesYesNo
ExpiryShort-livedConfigurableNone
RotationAutomaticManualManual

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.

Read the full article

Kevin Keller · kevinkeller.org