Guides

Permission and Role

Suggest Edits

Option 1 - Use Snowflake pre-defined role

Snowflake comes with a set of pre-defined roles that are always available by default. These system-defined roles provide a foundational layer for access control and administration within your Snowflake account. You cannot drop these roles, and the privileges initially granted to them by Snowflake cannot be revoked. You can use any of these roles for our Snowflake integration

  • ACCOUNTADMIN: This is the highest-level role, often referred to as the "god-mode" role. It encapsulates the SYSADMIN and SECURITYADMIN roles and can perform any account-level operations. It's recommended to limit the number of users with this role and not use it for daily tasks.
  • SECURITYADMIN: This role is primarily responsible for managing grants and security. It inherits privileges from USERADMIN and can grant or revoke privileges on any object in the account.
  • USERADMIN: As the name suggests, this role is dedicated to user and role management. It has the privileges to create and manage users and roles that it owns.
  • SYSADMIN: This role has the authority to create and manage databases, warehouses, and other database objects (like schemas and tables) within the account. It's a common practice for custom roles that own objects to be granted to SYSADMIN.

Step 1 - Create an integration in Snowflake

ACCOUNTADMIN to run this script

create security integration INTEGRATION_DEMO
    type = oauth
    oauth_client = custom
    oauth_client_type = 'public'
    oauth_redirect_uri = 'https://oauth2.integration-platform.anduin.app/callback'
    enabled = true
    pre_authorized_roles_list = ( 'USERADMIN','SYSADMIN')
    oauth_issue_refresh_tokens = true
    oauth_refresh_token_validity = 864000
  • The 'pre_authorized_roles_list' parameter specifies which role can be used to authenticate the integration connection. By including the roles in this parameter, all users under these roles can configure and setup the Snowflake integration. Note that ACCOUNTADMIN can not be added here due to Snowflake policy. ACCOUNTADMIN encapsulates SYSADMIN and SECURITYADMIN so you can specify these roles instead.

Step 2 - Ensure required privileges (permissions)

The integration expects to modify your Snowflake warehouse, database, schema and tables. Please ensure the roles specified in step 1 have below privileges (permissions)

  • USAGE ON WAREHOUSE
  • USAGE ON DATABASE
  • USAGE ON SCHEMA
  • SELECT ON ALL TABLES IN SCHEMA

ACCOUNTADMIN can grant privileges to roles that miss any requirements by running this script

GRANT USAGE ON WAREHOUSE my_wh TO ROLE [Input the role name];
GRANT USAGE ON DATABASE my_db TO ROLE [Input the role name];
GRANT USAGE ON SCHEMA my_db.public TO ROLE [Input the role name]; 
GRANT SELECT ON ALL TABLES IN SCHEMA my_db.public TO ROLE [Input the role name];

Option 2 - Use custom role

Custom role can also be used for this integration setup. By creating custom roles, you can tailor permissions specifically to the needs of different users or groups of users, rather than relying solely on the pre-defined system roles.

Step 1 - Create custom role in Snowflake

ACCOUNTADMIN to run this script

CREATE ROLE [Input the role name];

Step 2 - Grant privileges to the custom role in Snowflake

ACCOUNTADMIN to run this script

GRANT USAGE ON WAREHOUSE my_wh TO ROLE [Input the role name];
GRANT USAGE ON DATABASE my_db TO ROLE [Input the role name];
GRANT USAGE ON SCHEMA my_db.public TO ROLE [Input the role name]; 
GRANT SELECT ON ALL TABLES IN SCHEMA my_db.public TO ROLE [Input the role name];

Step 3 - Assign user to the custom role in Snowflake

ACCOUNTADMIN to run this script

GRANT ROLE [Input the role name] TO USER [Input user];

Step 4 - Create an integration in Snowflake

create security integration INTEGRATION_DEMO
    type = oauth
    oauth_client = custom
    oauth_client_type = 'public'
    oauth_redirect_uri = 'https://oauth2.integration-platform.anduin.app/callback'
    enabled = true
    pre_authorized_roles_list = ( 'Input the role name')
    oauth_issue_refresh_tokens = true
    oauth_refresh_token_validity = 864000

Updated 4 days ago