Introduction
Astrato supports security integration with Snowflake; this allows customers to use their existing authentication and authorization configuration, including roles, as they have applied them to their Snowflake platform.
Organizations using an Okta integration for Single Sign-On (SSO) with Snowflake can enable Enterprise Authentication so that each user of Astrato is individually identified in Snowflake and served only the content they are permitted to see.
Through a single Astrato dashboard or workbook, permissions and access according to your Security protocols apply to:
table or view levels
individual rows or columns
data masking for specific users (e.g. linked to geographic restrictions)
length of time Astrato caches a user's credentials.
All these features work with the ID protocol (IdP) that you’ve set up so that a single workbook in Astrato shows a different set of data for each user.
Using Snowflake's row-level permissions and data masking in Astrato
Row-level permissions in Snowflake allow customers to restrict the rows in a table to specific users. Once Security integration is configured between Astrato and SnowFlake, no further permissions set up is required in Astrato workbooks.
All queries sent from Astrato automatically pass through permissions configured in Snowflake and are applied to the results.
In the diagram in Example 1, Jeremy can only see Europe’s sales data and Olivia can only see North America’s data based on the permissions configured in Snowflake.
Example 1 - Row-level security using SSO
In addition to row-level permissions, data masking can be applied in Snowflake based on the user's permissions.
In Example 2 Angelika and Peter have access to the same HR dashboard created in a single workbook in Astrato, but Peter isn't allowed to see personal data.
Example 2 - Data masking using SSO in Snowflake applied to a dashboard in Astrato.
You can read more about Snowflake's row-level access policies, their column-level access policies, and their dynamic data masking.
*More information on Snowflake row-level policies can be found in this article
Creating access integration in Snowflake for Astrato
Define Integration in the snowflake account
Sign in to your target Snowflake account with a user who has account admin privileges.
Run the code below to set up the integration:
(you can change the token validity and blocked roles list to suit your needs.)
CREATE SECURITY INTEGRATION oauth_astrato_integration
TYPE = OAUTH
ENABLED = TRUE
OAUTH_CLIENT = CUSTOM
OAUTH_CLIENT_TYPE = 'CONFIDENTIAL'
OAUTH_REDIRECT_URI = 'https://app.astrato.io/auth/proxy/connection/snowflake-verify/callback'
OAUTH_ISSUE_REFRESH_TOKENS = TRUE
OAUTH_REFRESH_TOKEN_VALIDITY = 7776000
BLOCKED_ROLES_LIST = ('SYSADMIN', 'SECURITYADMIN', 'ACCOUNTADMIN')
Here is a short description of the different arguments in the code above.
OAUTH_REDIRECT_URI - is a URL that needs to be added so that after a successful login, Astrato is informed and can persist user tokens.
OAUTH_ISSUE_REFRESH_TOKENS - A Snowflake access token has a very short lifetime; therefore, astrato must request a refresh token to renew the connection automatically.
OAUTH_REFRESH_TOKEN_VALIDITY - We use maximum refresh token validity so that users don't need to repeat the login flow until 90 days have passed.
The unit used to set validity is defined in seconds.
BLOCKED_ROLES_LIST - we encourage limiting Snowflake user roles that can be requested. By default, Astrato requests scope=session:role-any, which means it will ask for a default user role in Snowflake.
Run the code below to get the needed information from the integration above to set the integration in Astrato.
describe security integration OAUTH_ASTRATO_INTEGRATION
Copy and save the OAUTH_CLIENT_ID and The OAUTH_AUTHORIZATION_ENDPOINT from the result table below.
Run the code below to get your CLIENT_SECRET
select SYSTEM$SHOW_OAUTH_CLIENT_SECRETS('OAUTH_ASTRATO_INTEGRATION')The Results will be something like below:
{"OAUTH_CLIENT_SECRET_2":"OU4gLeoV...","OAUTH_CLIENT_SECRET":"4glVKg...","OAUTH_CLIENT_ID":"8BYlRVfb..."}
Copy any of the two OATUH_CLIENT_SECRET.
Setup the Access Integration in Astrato
Login to Astrato with a user with Admin rights.
Go to Administration (bottom left corner. in the lobby) -> Enterprise Connection
Select the Access Integration tab.
Click on the New Integration button on the top right corner of the screen.
Click on the Snowflake tile.
Click on the Setup tab on the new screen.
In the Setup screen, fill in the information you obtained from the Snowflake integration in the previous step.
Connection Name - give the connection a user-friendly name
Issuer URL - is the snowflake account URL. Learn how to find your account URL.
You can get it from this information from the OAUTH_AUTHORIZATION_ENDPOIT, see the bold text below:
https://XXXX.us-east-1.snowflakecomputing.com/oauth/authorize
Client ID - paste the OAUTH_CLIENT_ID you obtained in the previous step.
Client secret - paste the OAUTH_CLIENT_SECRET you obtained in the previous step.
Click the Create button.
Click on the Verify connection button at the top of the screen.
You'll be redirected to Snowflake; sign in to Snowflake.
After the sign in will be successful, you'll be redirected to Astrato and see this message.
Now, you can use this integration in your data connections.