--- title: "Connection Configuration for Snowflake" slug: "connection-configuration-snowflake" updated: 2025-06-11T23:48:01Z published: 2025-06-11T23:48:01Z canonical: "documents.trocco.io/connection-configuration-snowflake" --- > ## Documentation Index > Fetch the complete documentation index at: https://documents.trocco.io/llms.txt > Use this file to discover all available pages before exploring further. # Connection Configuration for Snowflake ## summary Help page for creating a Connection Configuration for Snowflake. In addition, users must be given roles with the necessary permissions before using Snowflake. For more information, see [Permissions and Roles](/docs/connection-configuration-snowflake#%E6%A8%A9%E9%99%90%E3%81%A8%E3%83%AD%E3%83%BC%E3%83%AB) below. ## entry | item name | indispensable | Contents | | --- | --- | --- | | Name | Yes | Enter the name of the Connection Configuration to be used inside TROCCO. | | memo | No | You can enter a note of Connection Configuration to be used inside TROCCO. | | host | Yes | Enter the host for Snowflake. For more information, see [Host Name Entry Format](/docs/connection-configuration-snowflake#%E3%83%9B%E3%82%B9%E3%83%88%E5%90%8D%E3%81%AE%E5%85%A5%E5%8A%9B%E5%BD%A2%E5%BC%8F). | | user | Yes | Enter your Snowflake username. | | Authentication method | Yes | Select the authentication method. Select [Key Pair Authentication](/docs/connection-configuration-snowflake#%E3%82%AD%E3%83%BC%E3%83%9A%E3%82%A2%E8%AA%8D%E8%A8%BC%E3%81%AB%E3%81%A4%E3%81%84%E3%81%A6) if used for Data Mart Configuration. | | (computer) password | Yes | Enter if user/password authentication is selected as the authentication method. Enter your Snowflake password. | | private key | Yes | Enter when key pair authentication is selected as the authentication method. Enter your Snowflake private key. For more information, see [About Key Pair Authentication](/docs/connection-configuration-snowflake#%E3%82%AD%E3%83%BC%E3%83%9A%E3%82%A2%E8%AA%8D%E8%A8%BC%E3%81%AB%E3%81%A4%E3%81%84%E3%81%A6). | | roll | No | Enter the role name assigned to the user. If not entered, Snowflake's default settings are used. If there is no default setting, it must be entered. For more information on roles, see [Permissions and Roles](/docs/connection-configuration-snowflake#%E6%A8%A9%E9%99%90%E3%81%A8%E3%83%AD%E3%83%BC%E3%83%AB). | | JDBC Driver | No | You can select the version of the JDBC driver to connect to the Snowflake server.`3.14.2``3.17.0``3.24.2` | About JDBC driver version If a JDBC driver is not specified, the following versions are used, respectively. - For Data Source Snowflake: `3.13.5` - For Data Destination Snowflake: `3.13.26` However, the JDBC driver series `3.13` will no longer be supported by Snowflake as of October 31, 2023. Therefore, **we recommend the use of the `3.14` series version.** ### Host Name Input Format You can enter the information in one of the following formats - `<LOCATOR>.<REGION>.<CLOUD>.snowflakecomputing.com` - Input example: `hm*****.ap-northeast-1.aws.snowflakecomputing.com` - `<ORGANIZATION>-<ACCOUNT_NAME>.snowflakecomputing.com` - Input example `: *******-km*****.snowflakecomputing.com` - This input format is available only when **user/password authentication is** selected. Not available during **key pair authentication**. Each of the above elements can be found in the lower left corner of the Snowflake console screen. ![connection-configuration-snowflake_001](https://cdn.document360.io/3a3bae1e-f157-487f-8798-01e9d820e760/Images/Documentation/connection-configuration-snowflake-2024-08-29-17-13-0.png) ### About Key Pair Authentication A public and private key must be generated, and the public key must be set on the Snowflake side and the private key on the TROCCO side. #### Public and private key generation At the terminal, execute the following commands in order. 1. Create a private key The generated private key should be entered into the **Private Key** field of TROCCO's Snowflake Connection Configuration. ``` $ openssl genrsa 2048 | openssl pkcs8 -topk8 -inform PEM -out rsa_key.p8 -nocrypt ``` 1. Generate public key ``` $ openssl rsa -in rsa_key.p8 -pubout -out rsa_key.pub ``` 1. Format the public key (remove line breaks and unnecessary parts) ``` $ cat rsa_key.pub | \ tr -d "\n" | \ sed "s/-----BEGIN PUBLIC KEY-----//g" | \ sed "s/-----END PUBLIC KEY-----//g" ``` #### Setting up a public key to Snowflake Execute the following query on the Snowflake side based on the public key you created and formatted earlier. ``` ALTER USER {USERNAME} SET RSA_PUBLIC_KEY='{PUBLIC_KEY}' ``` ## Permissions and Roles In order to use Snowflake, it is necessary to grant the necessary permissions to roles and to grant roles to users. Note that the privileges that must be granted to roles differ between ETL Configuration (ETL Source and ETL Destination) and Data Mart Configuration. ### When using ETL Configuration (Data Source) #### Required Authority | (data) item | authority | Contents | | --- | --- | --- | | [Virtual Warehouse Authority](https://docs.snowflake.com/ja/user-guide/security-access-control-privileges.html#virtual-warehouse-privileges) | OPERATE | Change the state of the warehouse (stopped, started, paused, resumed) so that running queries can be aborted. | | [Virtual Warehouse Authority](https://docs.snowflake.com/ja/user-guide/security-access-control-privileges.html#virtual-warehouse-privileges) | USAGE | virtual warehouses and, as a result, execute queries in the warehouses. | | [Database Authority](https://docs.snowflake.com/ja/user-guide/security-access-control-privileges.html#database-privileges) | USAGE | database. Additional permissions are required to view or perform actions on objects in the database. | | [schema authorization](https://docs.snowflake.com/ja/user-guide/security-access-control-privileges.html#schema-privileges) | USAGE | schema. | | [Table Authority](https://docs.snowflake.com/ja/user-guide/security-access-control-privileges.html#table-privileges) | SELECT | table so that you can execute [SELECT](https://docs.snowflake.com/ja/sql-reference/sql/select.html) statements on it. | #### Query Sample The following is a sample query to grant privileges to a role, using the following conditions as examples. When actually granting privileges to roles, please reread the contents accordingly. - Role to be created: `TROCCO_INPUT_ROLE` - User to whom the role is granted: `TROCCO_USER` - Target `warehouse:TROCCO_WAREHOUSE` - Target database: `TROCCO_DATABASE` - Target schema: `PUBLIC` - Target table: `TROCCO_TABLE` -- Switching roles to roles that can be created ``` USE ROLE ACCOUNTADMIN; ``` -- Create a new role ``` CREATE ROLE TROCCO_INPUT_ROLE; ``` -- make the role available to the specified user ``` GRANT ROLE TROCCO_INPUT_ROLE TO USER TROCCO_USER; ``` -- Make the role you create the default role for users ``` ALTER USER TROCCO_USER SET DEFAULT_ROLE = TROCCO_INPUT_ROLE; ``` -- Grant warehouse access privileges to roles ``` GRANT USAGE ON WAREHOUSE TROCCO_WAREHOUSE TO ROLE TROCCO_INPUT_ROLE; GRANT OPERATE ON WAREHOUSE TROCCO_WAREHOUSE TO ROLE TROCCO_INPUT_ROLE; ``` -- Grant database usage privileges to roles ``` GRANT USAGE ON DATABASE TROCCO_DATABASE TO ROLE TROCCO_INPUT_ROLE; ``` -- Granting schema usage privileges to a role ``` GRANT USAGE ON SCHEMA TROCCO_DATABASE.PUBLIC TO ROLE TROCCO_INPUT_ROLE; ``` -- If you want to grant a role the permission to execute SELECT queries against all tables in the schema ``` GRANT SELECT ON ALL TABLES IN SCHEMA TROCCO_DATABASE.PUBLIC TO ROLE TROCCO_INPUT_ROLE; ``` -- To grant a role permission to execute SELECT queries only on individual tables ``` GRANT SELECT on TABLE TROCCO_DATABASE.PUBLIC.TROCCO_TABLE TO ROLE TROCCO_INPUT_ROLE; ``` ### When using ETL Configuration (Data Destination) #### Required Authority | (data) item | authority | Contents | remarks | | --- | --- | --- | --- | | [Virtual Warehouse Authority](https://docs.snowflake.com/ja/user-guide/security-access-control-privileges.html#virtual-warehouse-privileges) | OPERATE | Change the state of the warehouse (stopped, started, paused, resumed) so that running queries can be aborted. | | | [Virtual Warehouse Authority](https://docs.snowflake.com/ja/user-guide/security-access-control-privileges.html#virtual-warehouse-privileges) | USAGE | virtual warehouses and, as a result, execute queries in the warehouses. | | | [Database Authority](https://docs.snowflake.com/ja/user-guide/security-access-control-privileges.html#database-privileges) | USAGE | database. Additional permissions are required to view or perform actions on objects in the database. | | | [schema authorization](https://docs.snowflake.com/ja/user-guide/security-access-control-privileges.html#schema-privileges) | USAGE | schema. | | | [schema authorization](https://docs.snowflake.com/ja/user-guide/security-access-control-privileges.html#schema-privileges) | CREATE TABLE | Allows new tables to be created in the schema, such as duplicating a table. | | | [schema authorization](https://docs.snowflake.com/ja/user-guide/security-access-control-privileges.html#schema-privileges) | CREATE STAGE | Allows new stages to be created in the schema, such as duplicating a stage. | | | [Table Authority](https://docs.snowflake.com/ja/user-guide/security-access-control-privileges.html#table-privileges) | OWNERSHIP | Transfers ownership of the table. This grants full control over the table. | If the transfer mode is REPLACE, This is required to execute a DROP TABLE query. To use a DROP [TABLE](https://docs.snowflake.com/ja/sql-reference/sql/drop-table.html#drop-table) query, you must use a role that has ownership of the table. [Snowflake documentation - Usage notes](https://docs.snowflake.com/ja/sql-reference/sql/drop-table.html#usage-notes) This is necessary only when executing on a table that already exists. | #### Query Sample The following is a sample query to grant privileges to a role, using the following conditions as examples. When actually granting privileges to roles, please reread the contents accordingly. - Role to be created: `TROCCO_OUTPUT_ROLE` - User to whom the role is granted: `TROCCO_USER` - Target `warehouse:TROCCO_WAREHOUSE` - Target database: `TROCCO_DATABASE` - Target schema: `PUBLIC` - Target table: `TROCCO_TABLE` -- Switching roles to roles that can be created ``` USE ROLE ACCOUNTADMIN; ``` -- Create a new role ``` CREATE ROLE TROCCO_OUTPUT_ROLE; ``` -- make the role available to the specified user ``` GRANT ROLE TROCCO_OUTPUT_ROLE TO USER TROCCO_USER; ``` -- Make the role you create the default role for users ``` ALTER USER TROCCO_USER SET DEFAULT_ROLE = TROCCO_OUTPUT_ROLE; ``` -- Grant warehouse access privileges to roles ``` GRANT USAGE ON WAREHOUSE TROCCO_WAREHOUSE TO ROLE TROCCO_OUTPUT_ROLE; GRANT OPERATE ON WAREHOUSE TROCCO_WAREHOUSE TO ROLE TROCCO_OUTPUT_ROLE; ``` -- Grant database usage privileges to roles ``` GRANT USAGE ON DATABASE TROCCO_DATABASE TO ROLE TROCCO_OUTPUT_ROLE; ``` -- Granting schema usage privileges to a role ``` GRANT USAGE ON SCHEMA TROCCO_DATABASE.PUBLIC TO ROLE TROCCO_OUTPUT_ROLE; ``` -- Grant table creation privileges to roles ``` GRANT CREATE TABLE ON SCHEMA TROCCO_DATABASE.PUBLIC TO ROLE TROCCO_OUTPUT_ROLE; ``` -- Granting roles the authority for stage operations ``` GRANT CREATE STAGE on SCHEMA TROCCO_DATABASE.PUBLIC to ROLE TROCCO_OUTPUT_ROLE; ``` -- Transfers ownership of a table to a role -- * Required when executing on a table that already exists. ``` GRANT OWNERSHIP ON TABLE TROCCO_DATABASE.PUBLIC.TROCCO_TABLE TO ROLE TROCCO_OUTPUT_ROLE; ``` ### When using Data Mart Configuration #### Required Authority | (data) item | authority | Contents | remarks | | --- | --- | --- | --- | | [Virtual Warehouse Authority](https://docs.snowflake.com/ja/user-guide/security-access-control-privileges.html#virtual-warehouse-privileges) | OPERATE | Change the state of the warehouse (stopped, started, paused, resumed) so that running queries can be aborted. | | | [Virtual Warehouse Authority](https://docs.snowflake.com/ja/user-guide/security-access-control-privileges.html#virtual-warehouse-privileges) | USAGE | virtual warehouses and, as a result, execute queries in the warehouses. | | | [Database Authority](https://docs.snowflake.com/ja/user-guide/security-access-control-privileges.html#database-privileges) | USAGE | database. Additional permissions are required to view or perform actions on objects in the database. | | | [schema authorization](https://docs.snowflake.com/ja/user-guide/security-access-control-privileges.html#schema-privileges) | USAGE | schema. | | | [schema authorization](https://docs.snowflake.com/ja/user-guide/security-access-control-privileges.html#schema-privileges) | CREATE TABLE | Allows new tables to be created in the schema, such as duplicating a table. | | | [Table Authority](https://docs.snowflake.com/ja/user-guide/security-access-control-privileges.html#table-privileges) | SELECT | table so that you can execute [SELECT](https://docs.snowflake.com/ja/sql-reference/sql/select.html) statements on it. | | | [Table Authority](https://docs.snowflake.com/ja/user-guide/security-access-control-privileges.html#table-privileges) | INSERT | Allows [INSERT](https://docs.snowflake.com/ja/sql-reference/sql/insert.html) statements to be executed on tables. | When the write mode is append, this is required to register data in the destination table. | | [Table Authority](https://docs.snowflake.com/ja/user-guide/security-access-control-privileges.html#table-privileges) | OWNERSHIP | Transfers ownership of the table. This grants full control over the table. | This is required to perform deletion of the output destination table when the write mode is all cases washing. This is necessary only when executing on a table that already exists. | #### Query Sample The following is a sample query to grant privileges to a role, using the following conditions as examples. When actually granting privileges to roles, please reread the contents accordingly. - Role to be created: `TROCCO_DATAMART_ROLE` - User to whom the role is granted: `TROCCO_USER` - Target `warehouse:TROCCO_WAREHOUSE` - Target database: `TROCCO_DATABASE` - Target schema: `PUBLIC` - Target table: `TROCCO_TABLE` -- Switching roles to roles that can be created ``` USE ROLE ACCOUNTADMIN; ``` -- Create a new role ``` CREATE ROLE TROCCO_DATAMART_ROLE; ``` -- make the role available to the specified user ``` GRANT ROLE TROCCO_DATAMART_ROLE TO USER TROCCO_USER; ``` -- Make the role you create the default role for users ``` ALTER USER TROCCO_USER SET DEFAULT_ROLE = TROCCO_DATAMART_ROLE; ``` -- Grant warehouse access privileges to roles ``` GRANT USAGE ON WAREHOUSE TROCCO_WAREHOUSE TO ROLE TROCCO_DATAMART_ROLE; GRANT OPERATE ON WAREHOUSE TROCCO_WAREHOUSE TO ROLE TROCCO_DATAMART_ROLE; ``` -- Grant database usage privileges to roles ``` GRANT USAGE ON DATABASE TROCCO_DATABASE TO ROLE TROCCO_DATAMART_ROLE; ``` -- Granting schema usage privileges to a role ``` GRANT USAGE ON SCHEMA TROCCO_DATABASE.PUBLIC TO ROLE TROCCO_DATAMART_ROLE; ``` -- Grant table creation privileges to roles ``` GRANT CREATE TABLE ON SCHEMA TROCCO_DATABASE.PUBLIC TO ROLE TROCCO_DATAMART_ROLE; ``` -- Grant a role permission to perform INSERT queries on a table ``` GRANT INSERT ON TABLE TROCCO_DATABASE.PUBLIC.TROCCO_TABLE TO ROLE TROCCO_DATAMART_ROLE; ``` -- Transfers ownership of a table to a role -- * Required when executing on a table that already exists. ``` GRANT OWNERSHIP ON TABLE TROCCO_DATABASE.PUBLIC.TROCCO_TABLE TO ROLE TROCCO_DATAMART_ROLE; ```