Overview
- Torii integrates with Snowflake and syncs the Users' data: email, first name, last name, role, status, license status, creation time in-app, and last visit time.
- Torii can support multiple Snowflake accounts.
Step 1: Create an OAuth Security Integration in Snowflake
This step must be done by someone with ACCOUNTADMIN role in Snowflake.
Run the following SQL command to create an OAuth security integration:
CREATE OR REPLACE SECURITY INTEGRATION TORII
TYPE = oauth
ENABLED = true
OAUTH_CLIENT = custom
OAUTH_CLIENT_TYPE = 'CONFIDENTIAL'
OAUTH_REDIRECT_URI = 'https://api.toriihq.com/api/auth/snowflake/callback'
OAUTH_ISSUE_REFRESH_TOKENS = TRUE
OAUTH_REFRESH_TOKEN_VALIDITY = 7776000;
* The number 7776000 above is the token expiry time in seconds (90 days).
💡 You can set the number to 31622400 (366 days) if you have requested Snowflake support to extend the expiration period from 90 days to a year. For more details, refer to the FAQ section below.
You will need to connect Torii to Snowflake with a user who is able to read users' emails. By default, we recommend connecting the integration with a SYSADMIN role. To verify that the role you wish to connect with has access to users' emails, please try to edit a user and make sure you can see their email (The Email field is NOT empty):
Here is an example of how to connect with the SYSADMIN role. You can swap the commands for any role as you prefer.
Connecting with a SYSADMIN role
To allow the SYSADMIN role full access to manage the integration, including modifying, using, and viewing its configuration, run the following command:
GRANT ALL ON INTEGRATION TORII TO SYSADMIN
After creating the OAuth security integration, run this command to retrieve the necessary information about the security integration, adding the chosen integration name between the quote marks (must be in all capital letters):
SELECT SYSTEM$SHOW_OAUTH_CLIENT_SECRETS('TORII');
The output will look like the following:
{"OAUTH_CLIENT_SECRET_2":".......","OAUTH_CLIENT_SECRET":".......","OAUTH_CLIENT_ID":"........"}
Copy and save the text found between the quotes following "OAUTH_CLIENT_SECRET" and "OAUTH_CLIENT_ID" — excluding the quotes. These are the "Client Secret" and "Client Id," which you will need for step 3 below.
Step 2: Gather Additional Required Information in Snowflake
The 'Base Url' for Snowflake is also necessary. Execute the following SQL command:
DESCRIBE SECURITY INTEGRATION TORII;
Look for the line with the OAUTH_AUTHORIZATION_ENDPOINT property and copy the URL from the property_value that ends in .com.
For example, if the endpoint is
https://eva12345.snowflakecomputing.com/oauth/authorize
, the 'Base Url' would be `https://eva12345.snowflakecomputing.com`.
Step 3: Connect Snowflake to Torii
Now you should have the following Snowflake information required to connect Snowflake to Torii:
- Client ID
- Client Secret
- Base Url
To connect Snowflake to Torii, Ensure that the person who connects the app will have the default role: SYSADMIN, then follow these steps:
- Go to Torii's Integrations page and click the Snowflake tile
- Click Connect
- In the Connect Snowflake window, enter the Client ID & Client Secret
- Enter the Base Url
- Click Continue
-
The "Test Connection" window will display, generating the connection test.
Click Connect to continue. -
Once the integration is connected and synced, it will display a green checkbox.
Q&A
Q: Can I Increase Token Expiry Time Beyond 90 Days?
A: By default, Snowflake sets OAuth authorizations to expire after 90 days. Once expired, your integration will cease functioning and you must re-establish the connection with Torii.
A Snowflake account administrator may extend the expiration period from 90 days to up to a year by submitting a request to Snowflake Support.
Connections that are already established will expire after the standard 90 days. However, connections that are new or re-established after the extension request has been processed will have an extended duration of one year.
Once Snowflake support has approved the increased limit for your account, execute the following command to extend the token expiration to 366 days:
ALTER SECURITY INTEGRATION TORII
SET OAUTH_REFRESH_TOKEN_VALIDITY = 31622400;
Q: I've received the error message "The role of the users who connected the integration cannot read users' emails. Check SnowFlake's help article for more information". What does this mean?
A: This error message means that the role you connected Torii to Snowflake with does not have access to view users' emails. Please run the following command in Snowflake, and reconnect the integration with a different role.
ALTER ACCOUNT SET OAUTH_ADD_PRIVILEGED_ROLES_TO_BLOCKED_LIST = FALSE
This action allow you to connect the integration with any role. Please make sure you can see users' emails with this role prior to reconnecting.