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

-- External OAuth for Azure AD
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';

-- External OAuth for Okta
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';

-- For a custom OAuth 2.0 server
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

-- For Tableau Desktop (using the preset)
CREATE SECURITY INTEGRATION sf_oauth_tableau
  TYPE = OAUTH
  ENABLED = TRUE
  OAUTH_CLIENT = TABLEAU_DESKTOP
  OAUTH_ISSUE_REFRESH_TOKENS = TRUE
  OAUTH_REFRESH_TOKEN_VALIDITY = 86400;

-- For a custom application (confidential client)
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;

-- For SPA / mobile (public client)
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

-- Scope-based role control (the default)
-- Include the following in the token scope on the IdP side:
--   session:role:ANALYST_ROLE
-- -> the Snowflake session then uses ANALYST_ROLE

-- Changing ANY_ROLE_MODE
ALTER SECURITY INTEGRATION ext_oauth_azure
  SET EXTERNAL_OAUTH_ANY_ROLE_MODE = 'ENABLE';
-- -> any role granted to the user may be used

-- ENABLE_FOR_PRIVILEGE (excluding 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.

-- Connecting with the 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'
)

-- Connecting with SnowSQL (External OAuth)
-- snowsql -a <account> --authenticator oauth --token <access_token>

Management and Auditing

-- List the integrations
SHOW SECURITY INTEGRATIONS;

-- Integration details (to check client_id and so on)
DESCRIBE SECURITY INTEGRATION sf_oauth_webapp;

-- Auditing OAuth connections (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