Snowflake OAuth Security Integration: External OAuth vs Snowflake OAuth

Snowflake's OAuth Security Integration lets you authenticate to Snowflake over the OAuth 2.0 protocol without passwords. It comes in two flavors — External OAuth (tokens issued by an external authorization server) and Snowflake OAuth (tokens issued by Snowflake itself) — which you choose between based on your use case.

External OAuth vs Snowflake OAuth Comparison

Creating an External OAuth Security Integration

-- Azure AD向けExternal OAuth
CREATE SECURITY INTEGRATION ext_oauth_azure
  TYPE = EXTERNAL_OAUTH
  ENABLED = TRUE
  EXTERNAL_OAUTH_TYPE = AZURE
  EXTERNAL_OAUTH_ISSUER = 'https://sts.windows.net/<tenant-id>/'
  EXTERNAL_OAUTH_JWS_KEYS_URL = 'https://login.microsoftonline.com/<tenant-id>/discovery/v2.0/keys'
  EXTERNAL_OAUTH_AUDIENCE_LIST = ('https://<account>.snowflakecomputing.com')
  EXTERNAL_OAUTH_TOKEN_USER_MAPPING_CLAIM = 'upn'
  EXTERNAL_OAUTH_SNOWFLAKE_USER_MAPPING_ATTRIBUTE = 'LOGIN_NAME'
  EXTERNAL_OAUTH_ANY_ROLE_MODE = 'DISABLE';

-- Okta向けExternal OAuth
CREATE SECURITY INTEGRATION ext_oauth_okta
  TYPE = EXTERNAL_OAUTH
  ENABLED = TRUE
  EXTERNAL_OAUTH_TYPE = OKTA
  EXTERNAL_OAUTH_ISSUER = 'https://myorg.okta.com/oauth2/default'
  EXTERNAL_OAUTH_JWS_KEYS_URL = 'https://myorg.okta.com/oauth2/default/v1/keys'
  EXTERNAL_OAUTH_AUDIENCE_LIST = ('https://<account>.snowflakecomputing.com')
  EXTERNAL_OAUTH_TOKEN_USER_MAPPING_CLAIM = 'sub'
  EXTERNAL_OAUTH_SNOWFLAKE_USER_MAPPING_ATTRIBUTE = 'LOGIN_NAME'
  EXTERNAL_OAUTH_ANY_ROLE_MODE = 'ENABLE';

-- カスタムOAuth 2.0サーバー向け
CREATE SECURITY INTEGRATION ext_oauth_custom
  TYPE = EXTERNAL_OAUTH
  ENABLED = TRUE
  EXTERNAL_OAUTH_TYPE = CUSTOM
  EXTERNAL_OAUTH_ISSUER = 'https://auth.example.com'
  EXTERNAL_OAUTH_JWS_KEYS_URL = 'https://auth.example.com/.well-known/jwks.json'
  EXTERNAL_OAUTH_AUDIENCE_LIST = ('snowflake-api')
  EXTERNAL_OAUTH_TOKEN_USER_MAPPING_CLAIM = 'email'
  EXTERNAL_OAUTH_SNOWFLAKE_USER_MAPPING_ATTRIBUTE = 'EMAIL'
  EXTERNAL_OAUTH_ANY_ROLE_MODE = 'ENABLE';

Creating a Snowflake OAuth Security Integration

-- Tableau Desktop向け（プリセット使用）
CREATE SECURITY INTEGRATION sf_oauth_tableau
  TYPE = OAUTH
  ENABLED = TRUE
  OAUTH_CLIENT = TABLEAU_DESKTOP
  OAUTH_ISSUE_REFRESH_TOKENS = TRUE
  OAUTH_REFRESH_TOKEN_VALIDITY = 86400;

-- カスタムアプリケーション向け（Confidentialクライアント）
CREATE SECURITY INTEGRATION sf_oauth_webapp
  TYPE = OAUTH
  ENABLED = TRUE
  OAUTH_CLIENT = CUSTOM
  OAUTH_CLIENT_TYPE = 'CONFIDENTIAL'
  OAUTH_REDIRECT_URI = 'https://myapp.example.com/oauth/callback'
  OAUTH_ISSUE_REFRESH_TOKENS = TRUE
  OAUTH_REFRESH_TOKEN_VALIDITY = 7776000;

-- SPA/モバイル向け（Publicクライアント）
CREATE SECURITY INTEGRATION sf_oauth_spa
  TYPE = OAUTH
  ENABLED = TRUE
  OAUTH_CLIENT = CUSTOM
  OAUTH_CLIENT_TYPE = 'PUBLIC'
  OAUTH_REDIRECT_URI = 'https://spa.example.com/callback'
  OAUTH_ISSUE_REFRESH_TOKENS = TRUE
  OAUTH_REFRESH_TOKEN_VALIDITY = 3600;

Role Mapping for External OAuth

-- スコープベースのロール制御（デフォルト）
-- IdP側のトークンスコープに以下を含める:
--   session:role:ANALYST_ROLE
-- → Snowflakeセッションでは ANALYST_ROLE が使用される

-- ANY_ROLE_MODEの設定変更
ALTER SECURITY INTEGRATION ext_oauth_azure
  SET EXTERNAL_OAUTH_ANY_ROLE_MODE = 'ENABLE';
-- → ユーザーに付与されている任意のロールを使用可能

-- ENABLE_FOR_PRIVILEGE（ACCOUNTADMINを除外）
ALTER SECURITY INTEGRATION ext_oauth_azure
  SET EXTERNAL_OAUTH_ANY_ROLE_MODE = 'ENABLE_FOR_PRIVILEGE';

OAuth Authentication Flow

With External OAuth, the client application first obtains an access token from the external authorization server, then passes that token to the Snowflake driver's connection parameters. Snowflake verifies the token signature, identifies the user from the claims, and establishes a session.

-- Python Connectorでの接続例（External OAuth）
import snowflake.connector

conn = snowflake.connector.connect(
    account='<account>',
    authenticator='oauth',
    token='<external_oauth_access_token>',
    warehouse='COMPUTE_WH',
    database='ANALYTICS',
    schema='PUBLIC'
)

-- SnowSQL接続例（External OAuth）
-- snowsql -a <account> --authenticator oauth --token <access_token>

Management and Auditing

-- Integration一覧の確認
SHOW SECURITY INTEGRATIONS;

-- Integration詳細（client_idの確認等）
DESCRIBE SECURITY INTEGRATION sf_oauth_webapp;

-- OAuth接続の監査（LOGIN_HISTORY）
SELECT
  user_name,
  client_ip,
  first_authentication_factor,
  second_authentication_factor,
  is_success,
  error_message,
  event_timestamp
FROM SNOWFLAKE.ACCOUNT_USAGE.LOGIN_HISTORY
WHERE first_authentication_factor = 'OAUTH_ACCESS_TOKEN'
  AND event_timestamp >= DATEADD(DAY, -7, CURRENT_TIMESTAMP())
ORDER BY event_timestamp DESC;

Check Your Understanding

Frequently Asked Questions