Key steps Involved.
· Setup an OAuth Security Integration in Snowflake
· From the created OAuth integration, using the below SQLs, fetch the application details needed to initiate OAuth request.
o DESCRIBE SECURITY INTEGRATION
o Calling the system function SHOW_OAUTH_CLIENT_SECRETS
· Once we have the application details like CLIENT_ID, CLIENT_SECRET, REDIRECT_URI and OAUTH_BASE_URI, initiate a request to authenticate your (OAuth Provider) Snowflake account with your new OAuth application
· Once the user is authenticated on the OAuth provider”s (Snowflake) login page, the user is prompt to grant your application the requested scopes, and then redirects the user back to application with an AUTHORIZATION_CODE
· Using this AUTHORIZATION_CODE, initiate a POST request to get the ACCESS_TOKEN to make API calls to Snowflake”s REST API.
Setup an OAuth 2.0 Security Integration in Snowflake
· Only the role ACCOUNTADMIN role can create and manage security integrations
· Using OAuth integration, the administrator can configure below,
o Mandatory parameters
§ Redirect URI
§ Client Type — Confidential or Public
o Optional Parameters like
§ Redirect URI
§ Issue refresh tokens
§ TTL for refresh tokens
§ IP whitelisting
§ PKCE (Proof Key for Code Exchange) support
§ Roles blocked
Figure 1 Creating Security Integration in Snowflake
Get the parameters needed by client application
· Using the below query, get the OAUTH_CLIENT_ID and OAUTH_REDIRECT_URI
Figure 2 Get OAUTH_CLIENT_ID and OAUTH_REDIRECT_URI
· Using the below query, get the CLIENT_SECRET
Figure 3 Query to get CLIENT_SECRET
Generate the OAUTH_AUTHORIZATION_CODE
1. Once we have “CLIENT_ID”, “CLIENT_SECRET”, AND “REDIRECT_URI”, use a web browser to authenticate your Snowflake account with your new OAuth application.
o URL format:
https://<your_account_name>.snowflakecomputing.com/oauth/authorize?client_id=<your_client_id>&response_type=code&redirect_uri=<your_redirect_uri>&scope=<your_scopes>
o Here “scope” is an optional parameter, to limit the role permitted by the ACCESS_TOKEN. If this “scope” is not specified, then the default role for the user is used instead.
o NOTE: the “CLIENT_ID” and “REDIRECT_URI” must be in web encoded format
2. When you access the above URL in web browser, you will be prompted to log-in to your Snowflake account (if you are not already logged in)
Figure 4 Login page to Snowflake Account
3. After logged in, you will see a screen asking you to grant access to your OAuth application. Click “Allow” to grant access.
Figure 5 Screen requesting for grant to OAuth application
4. Once everything is successful, you will be redirected to the REDIRECT_URL you specified.
Figure 6 Redirected URL containing the OAUTH_AUTHORIZATION_CODE
Generate OAuth Access Token
· Once we have the AUTHORIZATION_CODE, initiate a POST request with below payload to obtain the OAuth ACCESS_TOKEN.
Figure 7 Payload to generate OAUTH_ACCESS_TOKEN
Initiate Snowflake session using OAuth Access token
· Once we have the ACCESS_TOKEN, this can be passed to Snowflake client driver by using OAuth Authenticator.
· This session is fixed to the user and the role that was authorized
Figure 8 Connecting to Snowflake driver using OAuth
References
· https://docs.snowflake.com/en/user-guide/oauth-intro
· https://docs.snowflake.com/en/sql-reference/sql/create-security-integration-oauth-snowflake
· https://docs.snowflake.com/en/user-guide/python-connector-example
· https://community.snowflake.com/s/article/Using-OAuth-2-0-with-Snowflake
· https://community.snowflake.com/s/article/HOW-TO-OAUTH-TOKEN-GENERATION-USING-SNOWFLAKE-CUSTOM-OAUTH
· https://flask-docs-ja.readthedocs.io/en/latest/tutorial/dbcon/