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.

For each connection using OAuth for authentication you can also set a required Snowflake role. By setting the default role you restrict usage of the data connection to only users who are authorized to assume that role. Note that Snowflake roles are case-sensitive, so make sure to check that the role specified here matches exactly.

Additionally, decide on how you want to handle any OAuth token sharing in Hex projects via the Token sharing dropdown menu. This determines whether or not project creators can include their OAuth credentials with the Hex projects that import this data connection. This setting has implications for both the Notebook view and the published app associated with Hex projects. More on this below.

User authentication experience​

Users who run a query against a Snowflake OAuth data connection will be prompted to authenticate with their Snowflake credentials if the project creator's tokens are not shared (see above on how to enable and disable token sharing). 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). If a project Editor attempts to run a query against an OAuth Connection when the 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.

For published apps which require individual user authentication, 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 their Account > 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.

View and switch Snowflake roles​

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 left side panel. Users can also switch the role being used for a 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.

Snowflake OAuth token sharing​

Notebook view​

When token sharing is enabled for all OAuth connections in a project Notebook view functions as normal, 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 users to continue working on the project.

When token sharing is disabled for a project, there are multiple implications:

  1. No one other than the project creator may have "can edit" permission for 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 Help & support -> Share with support toggle, our support user will inherit the project creator’s OAuth credentials regardless of the token 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 token sharing is enabled for a data connection in a Hex project, 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 token sharing is disabled for the source data connection 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 token is included in the published app, any app users will be able to view and run the project without having to authenticate with Snowflake. Project runs in this scenario will use the project creator’s credentials. If the project creator’s OAuth tokens have expired, App Viewers will see an error page notifying them to reach out to the project creator.

If an OAuth token is not included with a published apps then App Viewers will be required to authenticate with Snowflake if they don’t already have the required Snowflake OAuth token in Hex.

Scheduled runs & caching​

Scheduled runs​

Scheduled runs for projects which use Snowflake OAuth data connections have some limitations depending on whether token sharing is enabled for the project. When disabled, scheduled runs can only be scheduled by the project creator. Additionally, scheduled run notifications will not include screenshots. If 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 does not enable token sharing 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 caches SQL cell results according the workspace and project level specifications for query caching timeouts (see more details about query caching here). For queries executed with an OAuth connection this means that slightly different caching behavior is expected depending on if the data connection has allowed token sharing. If enabled the project creator's token will always be associated with the query and any user who runs the cell will have access to the cache created. However, since the current Snowflake OAuth token is included in the caching details of a query that means that when token sharing is disabled that cached values will only be retrievable on a per token basis. Effectively, individual users on an app which has prevented token sharing will only be able to generate queries and access their associated cached values on a per-user basis.

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.

  • Schema browser -- Due to the current design behind Hex's schema 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 schema browser after another user with higher Snowflake permissions has refreshed the schema cache, 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