Skip to main content

Snowflake OAuth

With Snowflake OAuth, users are required to auth into Snowflake using their own user-specific Snowflake credentials, which generates an access token for each user. This allows for the enforcement of user-specific access controls to shared data connections. Because of this, you should only enable Snowflake OAuth for a given connection if each Hex user that will have access to any projects that use that connection has their own user account configured in Snowflake.

If each Hex user does not have their own Snowflake credentials, an alternative to using OAuth would be to configure one or more Snowflake data connections to use either username & password or a key pair (both of which associate the data connection with a single Snowflake user). Then, share the data connections to whoever should have access to them.

tip

This feature is currently in beta, and only available to workspaces on Enterprise plans. If you'd like to use Snowflake OAuth in your workspace, please read the known limitations section below and contact us at [email protected]!

Configuration steps

  1. In order to configure Snowflake OAuth, a Snowflake Account Admin will need to set up the security integration in Snowflake. An Account Admin (i.e. a Snowflake user with the ACCOUNTADMIN role) must run the following SQL snippet in Snowflake and retrieve the generated CLIENT_ID and CLIENT_SECRET (see Snowflake’s OAuth Documentation for more details):

    USE ROLE ACCOUNTADMIN;
    CREATE SECURITY INTEGRATION OAUTH_HEX
    TYPE=OAUTH
    ENABLED=TRUE
    OAUTH_CLIENT = CUSTOM
    OAUTH_CLIENT_TYPE='CONFIDENTIAL'
    OAUTH_REDIRECT_URI='https://app.hex.tech/snowflake-oauth-success'
    OAUTH_ISSUE_REFRESH_TOKENS = TRUE
    OAUTH_REFRESH_TOKEN_VALIDITY = 7776000
    OAUTH_ENFORCE_PKCE = TRUE;
    select system$show_oauth_client_secrets('OAUTH_HEX');

    Note that OAUTH_REFRESH_TOKEN_VALIDITY is set to 90 days here; this value can be modified if you want to require users to reauthenticate at a different interval.

  2. Once the security integration is setup in Snowflake, a Hex Admin can navigate to Workspace Settings Workspace Assets Snowflake OAuth Connections, and then click + Connection.

  1. Follow the prompts to enter the connection details, including the Client ID and Client Secret generated in step 1. Toggle on Passthrough Connection to require users to auth into Snowflake and use their own user-specific credentials. Click Add connection to submit; you will be routed to the Snowflake login screen.

  2. Enter your Snowflake credentials; you will be routed back to Hex. The integration set-up will now be complete.

Set a Snowflake data connection to use OAuth

Once the integration has been established, you can require Snowflake connections to use OAuth by editing a data connection and setting the Authentication Type as OAuth Token.

User authentication experience

Any user who runs a query against a Snowflake data connection that uses OAuth will be prompted to authenticate with their Snowflake credentials. Authenticating with Snowflake will create an access token that will be valid for the amount of time specified by the OAUTH_REFRESH_TOKEN_VALIDITY value that was set when the security integration was generated (by default, 90 days). If a project Editor attempts to run a query against an OAuth Connection when their token has expired, or they have not authenticated for the first time, Hex will display an "Expired" header on the SQL cell and prompt the user to authenticate:

If an App Viewer attempts to run a project containing queries against an OAuth Connection, they will similarly be prompted to authenticate:

Users are able to view their authentication status in User Settings Connected Apps, which includes data on when the token was last generated and when it will expire. It is also possible to refresh and revoke tokens from this page.

Caching and Snowflake OAuth

When using Snowflake OAuth, it's important to be aware of caching states. The use of caching in conjunction with Snowflake OAuth is generally unadvisable because in most cases, Admins want to ensure users run queries using their own permissions, and are not viewing the cached results of queries run by other users with different permissions. For this reason, it's strongly encouraged that projects that use Snowflake OAuth connections 1. do not cache SQL queries and 2. disable the cache default state app setting.

Known limitations

This feature is still in beta, and thus has some known limitations. Please read the following before opting into the beta:

Because of the way Hex's multiplayer logic is configured, there are situations where a Hex user with more restrictive Snowflake permissions may be able to view schema browser states, dataframes, or cached apps on a Hex project that were run previously by Hex user with higher Snowflake permissions.

For example, users will see data or tables that they don't have permission to view in Snowflake if:

  • A user views the schema browser after another user with higher Snowflake permissions has refreshed the schema cache.
  • A user views a dataframe in a project's logic that was generated by another user with higher Snowflake permissions.
  • A user views a published app whose cache has been set by another user with higher Snowflake permissions (see section on caching above for more details).

Troubleshooting

If you'd like troubleshooting assistance from Hex's support team, we strongly recommend that you provision a user account for [email protected] with read-only credentials to your Snowflake warehouse. This will allow our support team to continue providing the best possible support to your Hex users, while keeping all access controls centralized and fully visible in your Snowflake account. Hex's support team will use these credentials strictly for providing support to your Hex users upon request.

If you opt out, our support team will be limited in their ability to troubleshoot projects that utilize Snowflake OAuth data connections.