Skip to main content

Snowflake OAuth

info
  • Available on the Enterprise plan.
  • If you are an Enterprise customer and you would like to use Snowflake OAuth in your workspace, please read the known limitations section below and contact us at [email protected] to enable the feature in your workspace.

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 who will have access to projects which 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.

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.

    If your Hex workspace is not hosted at app.hex.tech, then you must update that section of the OATH_REDIRECT_URI accordingly.

    Additionally, the name of the SECURITY INTEGRATION in the first and last lines of the above query, must be the same (in this example both are OAUTH_HEX).

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

  1. Follow the prompts to enter the connection details, including the Client ID and Client Secret generated in step 1. Click Add connection to submit; you will be routed to the Snowflake login screen.
caution

When authenticating with Snowflake, make sure to use an account that does not have one of the following as the default Snowflake role: ACCOUNTADMIN, SECURITYADMIN, ORGADMIN. Snowflake blocks these roles from being used with security integrations like OAuth. See Snowflake's documentation for more detail.

  1. 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.

Snowflake roles​

For connections using OAuth for authentication, you can also set a Snowflake role. Setting a role for an OAuth data connection restricts usage of the data connection to only use that role. Therefore, only users who are authorized to assume that role in Snowflake will be able to use that data connection. Note that Snowflake roles are case-sensitive, so make sure to check that the role specified here matches exactly.

The current role assigned to any Snowflake OAuth data connection in a Hex project can be found in the data connection details menu in the Data sources tab of the left sidebar. For OAuth connections that do not have a role set in Workspace settings, users can switch the role being used for the OAuth data connection by using the Switch Snowflake role menu option. Users can choose from any of the OAuth roles with which they’ve already authenticated or authenticate with a new role.

Credential sharing​

Decide on how you want to handle any OAuth credential sharing in Hex projects via the Credential sharing dropdown menu. This setting determines whether or not project creators share their OAuth credentials with other users who are granted access to projects that use this OAuth connection.

Snowlake OAuth data connections have four credential sharing options:

  1. Disabled
  2. App Only
  3. Notebook View Only
  4. Notebook and App View

This setting determines whether users editing or viewing a project use the project creator's OAuth token, or if they have to authenticate to Snowflake with their own credentials.

Notebook view​

When credential sharing is enable for the Notebook view, OAuth connections function the same way as connecting via username or key pair, allowing multiple users to have "Can edit" access to the project if desired. All users with access to the Notebook view of a project are able to run cells using the project creator's OAuth tokens. This means that if the project creator’s OAuth token becomes expired, they will need to re-authenticate with Snowflake and refresh the token for any user to continue working on the project.

When credential sharing is disabled for the Notebook view, there are two main implications:

  1. No one other than the project creator may have "Can edit" access to the project. Any other existing user with "Can edit" permission will be downgraded to "Can view" access, and the project can only be shared with new users with a maximum of "Can view" project access.
  2. Users with "Can view" project access will be able to see the source code cells inputs in Notebook view, however they cannot run cells (as usual) or see cell output.

When sharing a project with our support team via the ? -> Share with support toggle, our support user will inherit the project creator’s OAuth credentials regardless of the credential sharing setting applied. 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.

Published app​

When credential sharing is enabled for the published app, the project creator has the ability to choose whether or not to include their credentials in the published app when they publish their project. When credential sharing is disabled, this option is not available. See the screenshot below for an example. Note that in this example we also show how the usual ability to show results from previous runs is restricted to only allow published projects to run from scratch.

If the project creator's credentials are included in the published app, any app users will be able to view and run the project without having to authenticate with Snowflake. In this scenario, app runs will use the project creator’s credentials. If the project creator’s OAuth tokens have expired, anyone who visits the published app will see an error page notifying them to reach out to the project creator.

If OAuth credentials are not shared with a published app, app Viewers will be required to authenticate with Snowflake if they don’t already have the required Snowflake OAuth token in Hex. By authenticating with Snowflake, an access token is created that is valid for the amount of time specified by the OAUTH_REFRESH_TOKEN_VALIDITY value set when the security integration was generated (by default, 90 days).

Tokens and expiration​

If a project Editor attempts to run a query against an OAuth Connection after the token has expired, or if they have never authenticated, Hex will display an "Expired" header on the SQL cell and prompt the user to authenticate.

For published apps with credential sharing disabled, App Viewers will similarly be prompted to authenticate when they attempt to run a project containing queries against an OAuth connection.

Users can review their current Snowflake authentication tokens in Settings > Connected Apps pages, which includes data on when their tokens were last generated and when they expire. It is also possible to refresh and revoke tokens from this page.

Scheduled runs & caching​

Scheduled runs​

Scheduled runs for projects which use Snowflake OAuth data connections have some limitations depending on whether credential sharing is enabled for the project. When credential sharing is disabled for the published app, scheduled runs can only be scheduled by the project creator. Additionally, scheduled run notifications will not include screenshots. If credential sharing is enabled, the project creator's token will be used to execute the project run and a screenshot of the resulting app can be included in scheduled run notifications.

Published app caching​

If a project has credential sharing disabled for published apps, then the associated published app cannot be set to "Show results from a previous run" (see example screenshot above). Instead, published apps in this configuration will be required to run from scratch when a user visits the app. This prevents project outputs that were generated by a user with more liberal permissions being visible to lower-permission users in the published app.

Query caching​

Hex stores a cache for query results specific to each access token. If credential sharing is enabled, query caching will function like any other data connection, where results are pulled from the cache instead of running in your warehouse within the timeout window.

When credential sharing is disabled, caching settings will be applied on the user-level, specific to each token. If one user were to run an app multiple times within the caching timeout window, the query results of subsequent runs would be pulled from cache. If another user were to visit the app within this timeout window, the first app run would execute queries in the warehouse using their Snowflake credentials.

Known limitations​

There are a couple known limitations when using Snowflake OAuth connections which means that in some scenarios we cannot guarantee that every user will only ever see the data available to their roles.

  • Data browser — Due to the current design behind Hex's Data browser, there is a possibility that a Hex user with more restrictive Snowflake permissions may be able to schemas they do not have access to. In particular, if a user views the Data browser after another user with higher Snowflake permissions has refreshed the Data browser, all schemas and tables will be visible to that user. However, the data in those schemas and tables will not be visible or queryable by the user with restricted permissions.
  • Notion embedded project previews — Due to how Notion embeds work it is possible that if a user embeds a project using a restricted Snowflake Oauth connection that anyone who visits the Notion page would be able to view the results of that project. See full details about Notion embed permissions here.

On this page