Configure Snowflake OAuth via an App

This article describes configuration of an App handling Snowflake OAuth authentication. The described approach applies to Metric Insights v7.1.0+ and comprises the following steps:

  1. Define a Custom Script that makes API calls to Snowflake and returns obtained access tokens to the App:
    1. The Custom Script sends a request to the Snowflake token endpoint using the Client ID and Client Secret. These credentials are securely stored as Custom Script Parameters, with the Client Secret encrypted.
    2. The Custom Script handles token exchange using authorization code (for first-time authentication), or refresh token (for renewing access).
    3. The obtained access token is stored as an App Entity and can be accessed via the Entity API.
  2. Create an App Template and App to interface with the Custom Script:
    1. The App Template serves as a blueprint for creating Apps with different parameter values, avoiding the need to duplicate code.
    2. The App initiates authentication using the Snowflake OAuth authorization endpoint with the configured Client ID and redirect URI.
    3. The App provides the user interface to trigger OAuth authentication and retrieve tokens from Snowflake.

Table of contents:

  1. Configure Custom Script
    1. Create New Custom Script
    2. Add Custom Script Parameters
      1. Add clientId Parameter
      2. Add clientSecret Parameter
      3. Add tokenEndpoint Parameter
    3. Add Parameter Set
    4. Enter Custom Script Code
  2. Configure App Template
    1. Create New App Template
    2. Upload Assets
    3. Add Template Variables
      1. Add client_id Variable
      2. Add endpoint Variable
  3. Configure App
    1. Create New App
    2. Enter Content Values
    3. Configure App Entities
      1. Add access_tokens Entity
      2. Add access_token_request Entity
  4. Access App
    1. Obtain Access Token Stored in App Entity

NOTE: The names of Custom Script, App Template, App, and associated Variables, Parameters, and Entities must exactly match the names specified in the instructions below for the configuration to function.

1. Configure Custom Script

1.1. Create New Custom Script

Access Admin > System > Custom Scripts

  1. [+ New Custom Script]
  2. Name: Snowflake Oauth
  3. External Application Name: Choose an External Application to automatically generate an API token for the Custom Script.
  4. Authentication User: Choose a User on whose behalf the Custom Script will operate.
  5. [Save]

NOTE: See Configure Custom Scripts for more details.

1.2. Add Custom Script Parameters

1.2.1. Add clientId Parameter

  1. [+Add Parameter]
  2. Name: clientId
  3. Type: String
  4. Activate the Required checkbox
  5. [Save]

1.2.2. Add clientSecret Parameter

  1. [+Add Parameter]
  2. Name: clientSecret
  3. Type: Password
  4. Activate the Required checkbox
  5. [Save]

1.2.3. Add tokenEndpoint Parameter

  1. [+Add Parameter]
  2. Name: tokenEndpoint
  3. Type: String
  4. Activate the Required checkbox
  5. [Save]

1.3. Add Parameter Set

  1. [+Add Parameter Set]
  2. Parameter Set Name: Enter a name for the Parameter Set
  3. tokenEndpoint: Enter the Snowflake token endpoint in the following format: https://<snowflake_account_url>/oauth/token-request
  4. clientId: Enter Client ID of the integration
  5. clientSecret: Enter Client Secret for the integration
  6. Is Default: Activate the checkbox
  7. [Save]
  8. [Save and Enable] the Custom Script

1.4. Enter Custom Script Code

Access the Editor tab

Enter the Custom Script code, which does the following:

  1. Checking Custom Script parameters, authorization code, and Redirect URI from the previous OAuth request.
  2. Checking whether to fetch an access token with authorization code (for a first-time login), or renew the previously fetched access token using a refresh token.
  3. Sending request to Snowflake API Token endpoint to obtain a token.
    • Client ID and Client Secret stored as Custom Script's parameters are used.

NOTE: A Custom Script parameter value can be accessed via customScript.parameters.<parameter_name>.

  1. Once the code is entered, [Save and Enable].

2. Configure App Template

2.1. Create New App Template

Access Content > Apps

  1. Name: mi-oauth
  2. [Save]

NOTE: See App (Portal Page) Templates Basics for details.

2.2. Upload Assets

Access the Assets tab

  1. [Upload Assets], select the dist.zip file with the App assets

2.3. Add Template Variables

Access the Variables tab

2.3.1. Add client_id Variable

  1. [+ Variable]
  2. Name: client_id
  3. Type: Text
  4. [Save Variable]

2.3.2. Add endpoint Variable

  1. [+ Variable]
  2. Name: endpoint
  3. Type: Text
  4. [Save Variable]

3. Configure App

3.1. Create New App

Access Content > Apps

  1. [+ New App]
  2. Name: mi-oauth
  3. Template: Select the previously created mi-oauth App Template
  4. [Save]

NOTE: See Overview of Apps (Portal Pages) for details on creating Apps in Metric Insights.

3.2. Enter Content Values

Access the Content tab

  1. client_id: Enter Client ID of the integration
  2. endpoint: Enter Snowflake OAuth endpoint in the following format: https://<snowflake_account_url>/oauth/authorize
  3. [Save]

3.3. Configure App Entities

3.3.1. Add access_tokens Entity

  1. [+ Add Entity]
  2. Name: access_tokens
  3. Entity Type: Internal
  4. Access Type: Private
  5. [Save]

3.3.2. Add access_token_request Entity

  1. [+ Add Entity]
  2. Name: access_token_request
  3. Entity Type: Custom Script
  4. Custom Script: Select the previously created Snowflake Oauth Custom Script
  5. [Save]

4. Access App

  1. [View]
  2. [Get Access Token]
  3. [Sign in] using Snowflake credentials
  4. [Allow]
  5. The App displays a message indicating that an access token has been retrieved

4.1. Obtain Access Token Stored in App Entity

To obtain the value of the fetched access token stored in App Entity, execute the following request to Entity API:

 fetch("<MI_hostname>/data/page/mi-oauth/access_tokens", 
   {   
    method: "GET", 
   }
);

The obtained token data is stored in the following format:

{
    "data": [
        {
            "id": "token",
            "value": {
                "access_token": "ver:1-hint:8364XXX",
                "token_type": "Bearer",
                "expires_in": 600,
                "refresh_token": "ver:2-hint:1276XXX",
                "refresh_token_expires_in": 7775999,
                "scope": "refresh_token session:role:ACCOUNTADMIN",
                "username": "METRICINSIGHTS",
                "user_first_name": "John",
                "user_last_name": "Doe",
                "idpInitiated": false,
                "expires_timestamp": 1745334043
            }
        }
    ],
    "count": 1
}

See the fields description in the table below:

Field Name Description
access_token Value of the Snowflake access token
token_type Access token type.
  • This value is always Bearer
expires_in Number of seconds left until access token expiration
refresh_token Value of Snowflake refresh token
refresh_token_expires_in Number of seconds left until refresh token expiration
scope Defines what permissions and roles the token grants
username Username of the user to whom the token belongs
user_first_name First name of the user to whom the token belongs
user_last_name Last name of the user to whom the token belongs
idpInitiated Whether the session was initiated by an Identity Provider; e.g., SSO login ("true"), or by the system/user ("false")
expires_timestamp Unix epoch time of access token expiration