Snowflake

When you connect Snowflake to Unblocked, Unblocked can reference data from the Snowflake databases you select when answering questions. Unblocked provides links back to the original Snowflake resources for easy reference.

The Client ID and Client Secret come from an OAuth security integration you register in your Snowflake account. Follow Set up OAuth in Snowflake below to create it before connecting.

Set up OAuth in Snowflake

Before connecting in Unblocked, create a dedicated read-only role and user, grant it access to the data you want indexed, and register an OAuth security integration restricted to that role. This gives you the Client ID and Client Secret that Unblocked needs.

Create a read-only role and user

Run the following SQL in Snowflake (using a role with the ACCOUNTADMIN privilege) to create a role and user dedicated to the Unblocked integration. Replace <your_secure_password>, <your_warehouse>, <your_database>, and <your_schema> with values for your account:

SET UNBLOCKED_LOGIN_NAME = 'unblocked_readonly_user';
SET UNBLOCKED_PASSWORD = '<your_secure_password>';

CREATE ROLE UNBLOCKED_READONLY_ROLE;

CREATE USER UNBLOCKED_READONLY_USER
  LOGIN_NAME = $UNBLOCKED_LOGIN_NAME
  DISPLAY_NAME = 'UNBLOCKED Readonly User'
  PASSWORD = $UNBLOCKED_PASSWORD
  MUST_CHANGE_PASSWORD = FALSE
  DEFAULT_ROLE = UNBLOCKED_READONLY_ROLE
  DEFAULT_WAREHOUSE = <your_warehouse>
  DEFAULT_NAMESPACE = <your_database>.<your_schema>;

GRANT ROLE UNBLOCKED_READONLY_ROLE
  TO USER UNBLOCKED_READONLY_USER;

Choose a strong, unique password for UNBLOCKED_PASSWORD. Unblocked authenticates with OAuth, so this password isn’t used after setup, but it must satisfy your account’s password policy.

Grant access to your data

Grant the role USAGE on the database and schema you want Unblocked to index, plus SELECT on current and future tables. Replace <your_database> and <your_schema> with the database and schema you want to grant access to:

SET UNBLOCKED_DATABASE = '<your_database>';
SET UNBLOCKED_SCHEMA = '<your_database>.<your_schema>';

GRANT USAGE ON DATABASE IDENTIFIER($UNBLOCKED_DATABASE)
  TO ROLE UNBLOCKED_READONLY_ROLE;

GRANT USAGE ON SCHEMA IDENTIFIER($UNBLOCKED_SCHEMA)
  TO ROLE UNBLOCKED_READONLY_ROLE;

GRANT SELECT ON ALL TABLES IN SCHEMA IDENTIFIER($UNBLOCKED_SCHEMA)
  TO ROLE UNBLOCKED_READONLY_ROLE;

GRANT SELECT ON FUTURE TABLES IN SCHEMA IDENTIFIER($UNBLOCKED_SCHEMA)
  TO ROLE UNBLOCKED_READONLY_ROLE;

Repeat this step for each additional database or schema you want Unblocked to access. For narrower access to individual tables or views, or broader access to an entire database, see the optional grant variations below.

Create the OAuth security integration

Create an OAuth security integration and restrict it to the UNBLOCKED_READONLY_ROLE role you created:

CREATE SECURITY INTEGRATION UNBLOCKED_READONLY_OAUTH
  TYPE = OAUTH
  ENABLED = TRUE
  OAUTH_CLIENT = CUSTOM
  OAUTH_CLIENT_TYPE = 'CONFIDENTIAL'
  OAUTH_REDIRECT_URI = 'https://getunblocked.com/api/auth/snowflake/exchange'
  OAUTH_ISSUE_REFRESH_TOKENS = TRUE
  OAUTH_REFRESH_TOKEN_VALIDITY = 7776000;

ALTER SECURITY INTEGRATION UNBLOCKED_READONLY_OAUTH
  SET ALLOWED_ROLES_LIST = ('UNBLOCKED_READONLY_ROLE');

For more details on this command, see Snowflake’s CREATE SECURITY INTEGRATION documentation.

Retrieve your credentials

Run the following to retrieve your Client ID, Client Secret, and account identifier in one query:

WITH oauth AS (
  SELECT PARSE_JSON(SYSTEM$SHOW_OAUTH_CLIENT_SECRETS('UNBLOCKED_READONLY_OAUTH')) AS secrets
)
SELECT
  CURRENT_ORGANIZATION_NAME() || '-' || CURRENT_ACCOUNT_NAME() AS snowflake_account,
  secrets:OAUTH_CLIENT_ID::STRING AS client_id,
  secrets:OAUTH_CLIENT_SECRET::STRING AS client_secret
FROM oauth;

Use the SNOWFLAKE_ACCOUNT value as your account identifier, CLIENT_ID as your Client ID, and CLIENT_SECRET as your Client Secret. You’ll enter these in Unblocked in the next section.

Grant access to a single table or view (optional)

If you only want Unblocked to access specific tables or views instead of an entire schema, grant SELECT on those objects individually instead of the schema-wide grants above. Replace <your_database>, <your_schema>, <your_table>, and <your_view> with your own values:

SET UNBLOCKED_DATABASE = '<your_database>';
SET UNBLOCKED_SCHEMA = '<your_database>.<your_schema>';
SET UNBLOCKED_TABLE = '<your_database>.<your_schema>.<your_table>';
SET UNBLOCKED_VIEW = '<your_database>.<your_schema>.<your_view>';

GRANT USAGE ON DATABASE IDENTIFIER($UNBLOCKED_DATABASE)
  TO ROLE UNBLOCKED_READONLY_ROLE;

GRANT USAGE ON SCHEMA IDENTIFIER($UNBLOCKED_SCHEMA)
  TO ROLE UNBLOCKED_READONLY_ROLE;

GRANT SELECT ON TABLE IDENTIFIER($UNBLOCKED_TABLE)
  TO ROLE UNBLOCKED_READONLY_ROLE;

GRANT SELECT ON VIEW IDENTIFIER($UNBLOCKED_VIEW)
  TO ROLE UNBLOCKED_READONLY_ROLE;

Repeat the last two grants for each additional table or view you want Unblocked to access.

Grant access to an entire database (optional)

If you want Unblocked to access every schema, table, and view in a database, including ones created later, grant access at the database level instead of the schema level. Replace <your_database> with your own value:

SET UNBLOCKED_DATABASE = '<your_database>';

GRANT USAGE ON DATABASE IDENTIFIER($UNBLOCKED_DATABASE)
  TO ROLE UNBLOCKED_READONLY_ROLE;

GRANT USAGE ON ALL SCHEMAS IN DATABASE IDENTIFIER($UNBLOCKED_DATABASE)
  TO ROLE UNBLOCKED_READONLY_ROLE;

GRANT USAGE ON FUTURE SCHEMAS IN DATABASE IDENTIFIER($UNBLOCKED_DATABASE)
  TO ROLE UNBLOCKED_READONLY_ROLE;

GRANT SELECT ON ALL TABLES IN DATABASE IDENTIFIER($UNBLOCKED_DATABASE)
  TO ROLE UNBLOCKED_READONLY_ROLE;

GRANT SELECT ON ALL VIEWS IN DATABASE IDENTIFIER($UNBLOCKED_DATABASE)
  TO ROLE UNBLOCKED_READONLY_ROLE;

GRANT SELECT ON FUTURE TABLES IN DATABASE IDENTIFIER($UNBLOCKED_DATABASE)
  TO ROLE UNBLOCKED_READONLY_ROLE;

GRANT SELECT ON FUTURE VIEWS IN DATABASE IDENTIFIER($UNBLOCKED_DATABASE)
  TO ROLE UNBLOCKED_READONLY_ROLE;

Revoke Unblocked's access (optional)

If you need to remove Unblocked’s access to Snowflake, run the following SQL to delete the OAuth integration, user, and role:

DROP SECURITY INTEGRATION IF EXISTS UNBLOCKED_READONLY_OAUTH;
DROP USER IF EXISTS UNBLOCKED_READONLY_USER;
DROP ROLE IF EXISTS UNBLOCKED_READONLY_ROLE;

Connect in Unblocked

With your Client ID, Client Secret, and account identifier ready, connect Snowflake in Unblocked.

Connect Snowflake

In the Unblocked sidebar, click Settings, then Data Sources, then Connect another data source, and select Snowflake.

Enter your account and OAuth credentials

Enter your Snowflake account identifier (for example, myorg-myaccount), along with the OAuth Client ID and Client Secret from the OAuth security integration you created in your Snowflake account. Click Connect.

Authorize Unblocked

You’ll be redirected to Snowflake to authorize Unblocked’s OAuth access. Click Allow to authorize.

Select your databases

After authorizing, you’ll be redirected back to Unblocked, where you can select which Snowflake databases Unblocked should index. A list of available databases is shown with a search box to filter them. Select the databases you want Unblocked to access, then click Save Settings. Unblocked will begin referencing data from your selected Snowflake databases in its answers.

You can change which databases are indexed at any time from Settings › Data Sources › Snowflake.

Getting Started with Unblocked

Product Guides

Using Unblocked

Personal Settings

Team Settings

Billing

Data Sources

Set up OAuth in Snowflake

Connect in Unblocked

​Set up OAuth in Snowflake

​Connect in Unblocked

Set up OAuth in Snowflake

Connect in Unblocked