Skip to main content

OAuth data connections

Configure OAuth data connections to require users to authenticate into your data warehouse with user-specific credentials.

info
  • Available on the Enterprise plan.
  • If you are an Enterprise customer and you would like to use Snowflake, Databricks, or BigQuery 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 OAuth data connections for Snowflake, Databricks, or BigQuery, users are required to authenticate to your warehouse using their own user-specific 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 OAuth for a given connection if each Hex user who will have access to projects using that connection has their own user account configured in the respective platform (Snowflake, Databricks, or BigQuery).

If each Hex user does not have their own credentials for your data warehouse, an alternative to using OAuth would be to configure one or more data connections to use a key pair or service principal (which associates the data connection with a single user). Then, share the data connections to whoever should have access to them.

Configure a workspace OAuth token​

The first step to setting up an OAuth data connection in Hex requires creating the integration in your warehouse, and using the generated credentials to create a workspace OAuth token. The workspace OAuth token is required for testing the connection to the warehouse, and performing the initial fetch of the schema data. Additionally, successful creation of a workspace OAuth token confirms that the OAuth integration has been set up correctly.

Snowflake OAuth Setup​

  1. Create the security integration in Snowflake
    A Snowflake Account Admin must run the following SQL snippet in Snowflake and retrieve the CLIENT_ID and CLIENT_SECRET.

    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');

    Additionally, you can allow default secondary roles to be set in OAuth sessions with:

    OAUTH_USE_SECONDARY_ROLES = IMPLICIT

    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 OAUTH_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).

    For more details, refer to Snowflake’s OAuth Documentation:

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

  3. Complete the connection setup
    Enter the Client ID and Client Secret generated in step 1. Follow the prompts to authenticate—you will be redirected to the Snowflake login screen. Once authenticated, the integration set-up will be complete.

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.

Databricks OAuth Setup​

  1. Create a custom OAuth application in Databricks
    A Databricks Admin must enable a custom OAuth application and generate a CLIENT_ID and CLIENT_SECRET for the OAuth integration. Refer to the steps outlined in Databricks OAuth Documentation for detailed instructions. The allowed Redirect URL in the connection should be https://app.hex.tech/databricks-oauth-success for multi-tenant workspaces, and https://<YOUR-HEX-DOMAIN-NAME>/databricks-oauth-success for single-tenant, EU, or HIPAA deployments.

  2. Create the connection in Hex
    Once the Databricks OAuth application is set up in Databricks, a Hex Admin can navigate to Workspace Settings → Data sources, locate the Databricks OAuth Connections section, and click + Connection.

  3. Complete the setup
    Provide a connection name and host in the format of https://<your-databricks-instance>.cloud.databricks.com, and enter the Client ID and Client Secret generated in Databricks. When you select "Add connection", you will be redirected to the Databricks login screen. Once authenticated, the connection setup will be complete.

BigQuery OAuth Setup​

  1. Create an OAuth client in BigQuery
    A Google Cloud admin must configure an OAuth client within the Google Cloud Console and retrieve the CLIENT_ID and CLIENT_SECRET. In the Google Cloud console, select APIs & Services from the meny on the left, and then select Credentials. On this page, Select + Create credentials, OAuth client ID, and choose "Web application" as the Application type. Add an Authorized redirect URI for your Hex workspace—https://app.hex.tech/bigquery-oauth-success for multi-tenant, and https://<YOUR-HEX-DOMAIN-NAME>/bigquery-oauth-success for single-tenant, EU, or HIPAA deployments.

    For more details, review the Google Cloud OAuth Documentation.

  2. Connect to Hex
    Once the BigQuery OAuth client is set up, a Hex Admin can navigate to Workspace Settings → Data sources, locate the BigQuery OAuth Connections section, and click + Connection.

  3. Complete the setup
    Enter the Client ID and Client Secret generated in the Google Cloud console, and follow the prompts to authenticate. You will be redirected to the BigQuery login screen. Once authenticated, the connection setup will be complete.

Set a data connection to use OAuth​

Once the integration is established for Snowflake, Databricks, or BigQuery, you can configure data connections to use OAuth by editing the connection and setting the Authentication Type as OAuth Token.

Snowflake roles​

For Snowflake data connections using OAuth for authentication, you can also set a Snowflake role to restrict 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 OAuth credential sharing in Hex projects via the Credential sharing dropdown menu. There are four options for credential sharing:

  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 owner's OAuth token or if they must authenticate with their own credentials.

Notebook View​

When credential sharing is enabled in the Notebook view, OAuth connections function similarly to username or key pair connections, allowing multiple users to have "Can edit" access to the project. All users with access to the Notebook can execute cells using the project owner's OAuth token. If the owner's OAuth token expires, the owner must re-authenticate to the warehouse to refresh their token, allowing all users to continue working in the project.

When credential sharing is disabled in the Notebook view, multiplayer editing is disabled—only one editor can be active in the Notebook at a time. Other users with "Can Edit" permissions on the project can Take over session from the banner at the top of the project to start editing. Taking over the session restarts the kernel so that the new user's OAuth token is used to run queries, and the original editor’s results cannot be accessed. When one editor is active in the Notebook, others with "Can explore" permission will not see cell outputs to obscure data they may not have access to.

When sharing a project with our support team via ? -> Share with support, the support user inherits the project owner’s OAuth credentials, regardless of the credential sharing settings. This allows 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 View​

When credential sharing is enabled in the published app, the project owner can decide whether to include their credentials when publishing the project, or require users to authenticate into the warehouse themselves. If the project owner’s credentials are shared, app users can view and run the project without authenticating with Snowflake. In this case, app runs will use the project owner’s credentials. If the owner's OAuth token has expired, users will see an error page prompting them to contact the owner.

If credential sharing is disabled in the published app, all app viewers are required to authenticate to the warehouse and use their own token to run the app. If they don't have active token, they'll be prompted to authenticate when opening the app.

Disabling credential sharing in published apps requires them to be run from scratch, preventing results from previous runs from being displayed.

tip

By default, the user who creates a project is designated as the project owner. A project's owner can be transferred over time, which can be useful when using an OAuth connection with credential sharing disabled, to ensure that credentials can be refreshed when the project creator is unavailable. Learn more about transferring ownership.

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 existing tokens in Settings > Connected Apps, 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 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 owner. Additionally, scheduled run notifications will not include screenshots. If credential sharing is enabled for the published app, the project owner'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 severeal known limitations to be aware of when using OAuth data connections. In some scenarios, we cannot guarantee that every user will only ever see the data available to their user.

  • Data browser — Due to the current design behind Hex's Data browser, there is a possibility that a Hex user with more restrictive permissions may be able to see schemas they do not have access to. In particular, if a user views the Data browser after another user with higher 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.

Troubleshooting and FAQ​

No active warehouse error​

When an Admin configures a Snowflake connection, they are required to define a warehouse as part of that connection. At present, all users that authenticate into Snowflake will need to have access to that warehouse.

If a Hex user is seeing an error like the following when executing SQL, their current Snowflake role does not have access to the warehouse that has been defined as part of the connection.

To fix this error:

  1. Check which role the Hex user is current using by having them run a select current_role() statement.
  2. Confirm which role is defined as part of the Snowflake connection in the Data Sources page of Settings.
  3. Have a Snowflake admin grant the user's role access to the warehouse (Snowflake docs).

Expired app credentials​

When app credential sharing is enabled, the project owner's credentials are used to run the published apps. A Snowflake OAuth token expires after a set number of days (based on how the integration was configured in Snowflake).

This can lead to a case where the project owner's credentials have not been refreshed recently and the published app is no longer working.

When this happens, Hex will prompt you to contact the project owner to have them refresh their credentials. If this person is unavailable (for example, they are out of office), you can also ask a workspace admin to transfer ownership of the project so that a different user's credentials are used in the published app.

On this page