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.

Requirements

  • Admin privileges in your Sigma organization; see User account types.
  • ACCOUNTADMIN role on your Snowflake warehouse (ensure that your default ROLE is not set to ACCOUNTADMIN, ORGADMIN, or SECURITYADMIN in Snowflake).
  • Okta, Azure AD, or Ping as your Identity Provider (IdP).
  • A Sigma connection to your Snowflake warehouse.

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.

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

Snowflake OAuth Limitations

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; see Snowflake documentation on Create Security Integration.

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
type = external_oauth
enabled = true
external_oauth_type = okta
external_oauth_issuer = 'https://.okta.com/oauth2/'
external_oauth_jws_keys_url = 'https://.okta.com/oauth2//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. Go to Administration > Authentication:

    1. In the Sigma header, click your user avatar to open the user menu.

    2. Select Administration to open the Administration portal.

    3. In the side panel, select Authentication.

  2. In the Authentication Method and Options section, locate the Authentication Method setting and click Edit.

  3. In the Authentication Method & Options page, configure OAuth authentication:

    1. In the Authentication Method dropdown, select the OAuth or OAuth or password option.

    2. Toggle the Allow Guest Access switch to the "on" position to enable guest users to access permitted content, or toggle it to the "off" position to restrict guest user access.

    3. Toggle the 2-Factor Authentication Required switch to the "on" position to require two-factor email authentication, or toggle it to the "off" position to disable two-factor authentication.

    4. In the Metadata URI field, enter the OAuth metadata URI from your authorization server.

    5. In the Client ID field, enter the client ID from your OAuth application.

    6. In the Client Secret field, enter the client secret from your OAuth application.

    7. Click Save to apply the changes.

  4. 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. See Connect to Snowflake.

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.

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.

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, you will not be prompted to provide a username or password for the connection. Sigma will always run queries with the organization member's Snowflake OAuth credentials. This includes when users are viewing Datasets and Workbooks owned by others.

This overrides the typical non-OAuth behavior where the Dataset or Workbook owner's permission to the source data is evaluated within Sigma and then the queries are run using the Snowflake user account credentials set in the Connection's settings.

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

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, their permission to the source data is evaluated within Sigma and then the queries are run using the Snowflake user account credentials set in the Connection's settings.

To select this option, create a connection with β€œOAuth Access” switched off.