OAuth with Snowflake

You can use OAuth with your Snowflake connections for centralized permission management between your Snowflake warehouse and your Sigma organization. Managing data access from a single location facilitates stronger data access security and decreases Admin time investment for faster permission rollouts.

Summary of Content

Requirements
OAuth for Permissions Management
      What is OAuth?
      How is OAuth used for Permissions Management?
Configure OAuth for Snowflake and Sigma
      Step 1: Create an App for Sigma in your OAuth Provider
      Step 2: Add OAuth Users to your App
      Step 3: Create an Authorization Server
      Step 4: Add an Access Policy for the Authorization Server
      Step 5: Create a Security Integration in Snowflake
      Step 6: Configure OAuth in Sigma
      Step 7: Configuring your Connection
            OAuth-enabled Connections with a Service Account
                  Creating a Service Account
                  Run Individual Workbooks “as service account”
            OAuth-enabled Connections without a Service Account
            Username/Password Connections (No OAuth - default)
Snowflake OAuth Limitations
Related Resources

Requirements

OAuth for Permissions Management

What is OAuth?

OAuth is a single sign-on (SSO) authorization framework that allows your users to securely log in to applications without the need for a username and password. This authorization happens between a client (you and your users) and one or more resources (i.e. Sigma and Snowflake) via your IdP. 

Your IdP uses an authentication server and short-lived tokens to authenticate your application’s users.

How is OAuth used for Permissions Management?

Configuring OAuth with Snowflake and Sigma will allow you to pass Snowflake roles to Sigma organization members. This is accomplished by establishing a chain of trust between your IdP, Snowflake warehouse, and Sigma. Learn how.

After you configure these three entities, you can enable OAuth on a per-connection basis in Sigma for any of your Snowflake connections. Learn about connection configuration types.

Configure OAuth for Snowflake and Sigma

Snowflake with OAuth requires configuration between an IdP, Snowflake and Sigma. This feature uses Snowflake’s External OAuth capabilities.

High Level Overview

  1. Create an App for Sigma in your IdPO
  2. Add OAuth Users to your App
  3. Create an OAuth Authorization Server
  4. Add an Access Policy for the Authorization Server
  5. Create a Security Integration in Snowflake
  6. Configure Sigma to trust the IdP
  7. Configure your Connection

The exact implementation of steps 1-3 varies depending on your IdP. Please visit their documentation for detailed instructions. If you are using Azure, follow our Azure specific instructions.

Step 1: Create an App for Sigma in your IdP

You will first need to create a Web OpenID Connect app within your IdP for Sigma. Within the app, please:

  1. Enable the authorization code grant type.
  2. Enable the refresh token grant type.
  3. Set your login redirect URL. Typically, this will be https://api.sigmacomputing.com/api/v2/oauth/1/authcode
    However, if you are running Sigma on AWS, please use https://aws-api.sigmacomputing.com/api/v2/oauth/1/authcode

Creating your Sigma OAuth app will generate a Client ID and Client Secret. Both fields will be used for configuration in Sigma (Step 6). 

Step 2: Add OAuth Users to your App

After creating your OAuth app, you will need to add a list of your OAuth users. These users will be mapped to both Sigma and Snowflake. Access to Snowflake roles is defined on the authorization server (Step 3).

All users must also have permission to access the warehouse in Snowflake.

Step 3: Create an Authorization Server

An authorization server is used to connect your users to Snowflake roles. Please create an authorization server in your IdP. 

Authorization Server configuration requires the following values:

Field

Value

Audience

https://<your-snowflake-account>.snowflakecomputing.com

Scopes

session:role-any - requests that the Snowflake access tokens received by Sigma have permission to assume any Snowflake role the user has been granted

offline_access - requests a refresh token that can be used to get new access tokens "offline" (without asking a human user to re-authenticate with the IdP)

openid - requests an OpenID token that can be used to authenticate the user to Sigma

email - requests that the OpenID token include the user's email

profile - requests that the OpenID token include other information from the user's profile (including the user's full name)

Claims

snowflake_username = <username>

Claims allow you to connect your OAuth users to user roles in your Snowflake warehouse. Claim definitions are IdP dependent.

 

The authorization server provides a metadata URI.  This id is needed for OAuth configuration within Sigma (step 6). The server also provides an issuer url and jws keys url, both of which are needed for the Snowflake security integration (step 5).

OKTA requires OKTA API Access Management to be enabled in your OKTA instance to create an authorization server.

Step 4: Add an Access Policy for the Authorization Server

  1. Create and/or assign an access policy to your new app (created in step 1). Access policies define rules for access and token lifetimes on an individual app.  
  2. Within the access policy, define access and refresh token lifetimes as desired for all grant types, users, and scopes.

Step 5: Create a Security Integration in Snowflake

Creating a security integration allows Snowflake to trust your IdP. Visit Snowflake’s documentation to learn how.

The following is an (Okta) example of the command you will need to run in Snowflake. Command values vary slightly depending on your IdP.

create security integration <name>
    type = external_oauth
    enabled = true
    external_oauth_type = okta
    external_oauth_issuer = 'https://<COMPANY>.okta.com/oauth2/<ID>'
    external_oauth_jws_keys_url = 'https://<COMPANY>.okta.com/oauth2/<ID>/v1/keys'
    external_oauth_token_user_mapping_claim = 'snowflake_username'
    external_oauth_snowflake_user_mapping_attribute = 'login_name'
    external_oauth_any_role_mode = 'ENABLE';


Step 6: Configure OAuth in Sigma

  1. Open your Admin Portal by selecting Administration in the user menu at the top right of your screen.
  2. Select the Authentication page from the left hand panel.
    Screen_Shot_2020-10-28_at_5.24.53_PM.png
  3. Click the blue Edit button under Authentication Method and Options.
  4. Select ‘OAuth’ or ‘OAuth or Password’ from the Authentication Method dropdown menu.
    Screen_Shot_2020-08-31_at_3.48.43_PM.png
  5. Under Metadata URI, enter the OAuth metadata URI from your authorization server.
  6. Under Client ID, enter the client ID from your OAuth application.
  7. Under Client Secret, enter the client secret from your OAuth application.
  8. Click Save.
  9. Test your OAuth configuration by logging out and logging back into Sigma. Your organization’s login page should now display a ‘Log in with SSO’ button.

Step 7: Configure your Connection

After you have configured permission inheritance with your IdP and Snowflake, you will need to enable OAuth your Snowflake connection(s). This is done on a per-connection basis.  Learn how to create a new Snowflake connection.

OAuth-enabled connections may be run with or without a service account. 

OAuth-enabled Connections with a Service Account

If you select the service account option on your connection, you will be prompted to provide a Snowflake user and password.

These service account credentials are used to run queries on dashboards exposed through Sigma’s public or application embedding features.

Optionally, you can choose the Run as service account setting on individual dashboards/workbooks. This setting queries the published version of the dashboard using the service account’s credentials  whenever it is viewed from within Sigma or run as part of a scheduled report. This ensures that any user with Sigma permissions on a dashboard/workbook will be able to view it regardless of warehouse permissions. Learn how

All other queries are run against your warehouse using the requesting organization member's Snowflake OAuth credentials.

To select this option, create a connection with both “OAuth Access” and “Service Account” switched on.

Screen_Shot_2020-08-31_at_3.52.33_PM.png

Create a Service Account

A service account is a Snowflake user account implemented for admin purposes in Sigma. It is the same as any other Snowflake user account. However, it should be granted the default role permission you would like on your connection, as well as permission to access the warehouse.

Your service account must be added to the OAuth user list, like all other OAuth accounts on the connection.

Run Individual Workbooks “as service account”

This action requires Admin privileges.

  1. Open the Workbook.
  2. If the Workbook is in edit mode, click the green ‘Publish’ button in the top right corner of the page.
  3. Click on the Share option inside the Workbook's menu
  4. This will open the Share modal. Navigate to the modal's Who has Access tab.
  5. Switch on Run as service account.
  6. Click Save.

OAuth-enabled Connections without a Service Account

When this option is selected, Sigma will always run the query with the organization member's Snowflake OAuth credentials. You will not be prompted to provide a username or password for the connection.

To select this option, create a connection with “OAuth Access” switched on and “Service Account” switched off.

Screen_Shot_2020-08-31_at_3.50.58_PM.png

Username/Password Connections (No OAuth - default)

By default, Snowflake connections do not use OAuth. This option remains available even if your organization has OAuth configured.

When a Sigma organization member accesses data from a non-OAuth connection, Sigma uses the username and password credentials defined on the connection to authenticate with Snowflake.

To select this option, create a connection with “OAuth Access” switched off.

Snowflake OAuth Limitations

  • Materialization, CSV Upload,  and Dataset Warehouse Views are not supported for connections using OAuth. 

  • OAuth tokens may expire if the author goes a significant amount of time without logging into Sigma. Such an occurrence will affect schedules. This limitation can be avoided by running the dashboard as a service account.

Related Resources

Connect to Snowflake
Manage Authentication
OAuth with Snowflake and Azure AD

 

Was this page helpful?
Yes No