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 below.

entry

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.

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

2. Generate public key

$ openssl rsa -in rsa_key.p8 -pubout -out rsa_key.pub

3. 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

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

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

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;