OAuth token generation using Snowflake custom OAuth

Based on support article from Snowflake

Solution

The objective of the article is to provide a means of generating an access token and refresh token using Snowflake Custom OAuth. Once complete, end-users should be able automate the flow to get the new access tokens via refresh token(until expiry).

For testing purposes, this article will also show a successful connection using access token when making a connection through SnowSQL.

Steps:

  • Login to Snowflake Web Interface with user credentials having privileges to 'ACCOUNTADMIN' role. This user's default role should not be ACCOUNTADMIN.
  • Create Security Integration of type OAuth and OAuth Client as CUSTOM
create or replace security integration oauth_KB
    type=oauth
    enabled=true
    oauth_client=CUSTOM
    oauth_client_type='CONFIDENTIAL'
    oauth_redirect_uri='https://localhost.com';
    oauth_issue_refresh_tokens=true
    oauth_refresh_token_validity=86400;

*where oauth_redirect_uri can refer to anything that doesn’t exist as long as you can see the Browser URL bar.
  • Note down OAUTH_CLIENT_ID, OAUTH_AUTHORIZATION_ENDPOINT, OAUTH_REDIRECT_URI and OAUTH_TOKEN_ENDPOINT values by running the below command in Snowflake:
desc integration oauth_KB;
  • Note down Client Secret by running below command:
select SYSTEM$SHOW_OAUTH_CLIENT_SECRETS('OAUTH_KB');

*You will get 2 values, note down any 1 of the values.

  • Encode OAUTH_CLIENT_ID and OAUTH_REDIRECT_URI values using https://www.urlencoder.org/. Encoded values will be used to generate authorization token in the next step.
  • Generate Authorization token by accessing the authorization endpoint in the browser. Form the URL as shown below:
<OAUTH_AUTHORIZATION_ENDPOINT>?client_id=<encoded value of Client ID>&response_type=code&redirect_uri=<OAUTH_REDIRECT_URI>

E.g. 
https://snowflaketestaccount.snowflakecomputing.com/oauth/authorize?client_id=lhJdQCSRFZdz786%2FjmC%2Fr%2FV9gs%3D&response_type=code&redirect_uri=https%3A%2F%2Flocalhost.com
  • When Snowflake Login Window appears, login with the user who does not have default role set to ACCOUNTADMIN or SECURITYADMIN
  • Once done, you will see the below in the URL bar after some time:
https://localhost.com/?code=<Code Value>
  • Note down the code value. This is the AUTHORIZATION CODE which will be used to generate access/refresh token. This is a short lived code.
  • Generate OAuth Access Token and Refresh Token using cURL command. cURL is just used as an example, you can also use other tools to generate the tokens.
curl -X POST -H "Content-Type: application/x-www-form-urlencoded;charset=UTF-8" \
--user "<OAUTH_CLIENT_ID not encoded>:<OAUTH_CLIENT_SECRET>" \
--data-urlencode "grant_type=authorization_code" \
--data-urlencode "code=<AUTHORIZATION CODE>" \
--data-urlencode "redirect_uri=<OAUTH_REDIRECT_URI not encoded>" \
<OAUTH_TOKEN_ENDPOINT>
  • This will generate the access token and refresh token. Screenshot for reference:

refresh token

  • Connect to Snowflake using SnowSQL CLI and access_token as
snowsql -a <accountname> -u <username> \
--authenticator oauth \
--token "access_token"

*You will be able to successfully connect to Snowflake Instance with the help of access token

  • Since access tokens are short lived(10 minutes), you will refresh the access token by using refresh tokens until refresh token expires. In this example, refresh token expiry is set to 86400s(1 hour)
curl -X POST -H "Content-Type: application/x-www-form-urlencoded;charset=UTF-8" \
    --user "<OAUTH_CLIENT_ID not encoded>:<OAUTH_CLIENT_SECRET>" \
    --data-urlencode "grant_type=refresh_token" \
    --data-urlencode "refresh_token=<refresh_token value>" \
    --data-urlencode "redirect_uri=<OAUTH_REDIRECT_URI not encoded>" \
    <OAUTH_TOKEN_ENDPOINT>
  • This will generate new access token. Screenshot for reference.

access token

More Information