Snowflake Connection Configuration

summary

This page provides help for configuring Snowflake connection information.

Input field

Item nameRequired		content
name	Yes	Enter the name of the connection information to be used inside TROCCO.
memorandum	No	Enter a memo of the connection information used inside TROCCO.
Destination host	Yes	Enter the Snowflake host. Please refer to the image below for a specific example.
user	Yes	Please enter your Snowflake username.
Authentication method	Yes	Select User Password Authentication for the forwarding settings and Key Pair Authentication for the datamart settings.
password	Yes	Enter your Snowflake password.
Private key	No	Enter your Snowflake private key. * Required for key pair authentication.

supplement

About permissions

• To use Snowflake, grant the required privileges to the role and grant the role to the user.

Permissions required to use forwarding settings

Permissions required if you select Snowflake as the transfer source

Sample query

• Role to create
  TROCCO_INPUT_ROLE
• Who to grant the role to
  TROCCO_USER
• Target Warehouse
  TROCCO_WAREHOUSE
• Target database
  TROCCO_DATABASE
• Target Schema
  PUBLIC
• Target table
  TROCCO_TABLE
• * Please rewrite the roles, users, target warehouses, databases, schemas, and table names specified in the sample to the contents that are actually used.

-- Switch to roles that allow you to create roles

USE ROLE ACCOUNTADMIN;

-- Create a new role

CREATE ROLE TROCCO_INPUT_ROLE;

-- Make the role available to specified users

GRANT ROLE TROCCO_INPUT_ROLE TO USER TROCCO_USER;

-- Make the created role the default role for the user

ALTER USER TROCCO_USER SET DEFAULT_ROLE = TROCCO_INPUT_ROLE;

-- Grant warehouse usage rights 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 permissions to roles

GRANT USAGE ON DATABASE TROCCO_DATABASE TO ROLE TROCCO_INPUT_ROLE;

-- Grant schema usage permissions to roles

GRANT USAGE ON SCHEMA TROCCO_DATABASE.PUBLIC TO ROLE TROCCO_INPUT_ROLE;

-- Grant the role permission to execute SELECT queries on all tables in the schema

GRANT SELECT ON ALL TABLES IN SCHEMA TROCCO_DATABASE.PUBLIC TO ROLE TROCCO_INPUT_ROLE;

-- * When granting the role permission to enable SELECT queries to be executed only on individual tables

GRANT SELECT on TABLE TROCCO_DATABASE.PUBLIC.TROCCO_TABLE TO ROLE TROCCO_INPUT_ROLE;

Permissions required if you selected Snowflake as the destination

Sample query

• Role to create
  TROCCO_OUTPUT_ROLE
• Who to grant the role to
  TROCCO_USER
• Target Warehouse
  TROCCO_WAREHOUSE
• Target database
  TROCCO_DATABASE
• Target Schema
  PUBLIC
• Target table
  TROCCO_TABLE
• * Please rewrite the roles, users, target warehouses, databases, schemas, and table names specified in the sample to the contents that are actually used.

-- Switch to roles that allow you to create roles

USE ROLE ACCOUNTADMIN;

-- Create a new role

CREATE ROLE TROCCO_OUTPUT_ROLE;

-- Make the role available to specified users

GRANT ROLE TROCCO_OUTPUT_ROLE TO USER TROCCO_USER;

-- Make the created role the default role for the user

ALTER USER TROCCO_USER SET DEFAULT_ROLE = TROCCO_OUTPUT_ROLE;

-- Grant warehouse usage rights 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 permissions to roles

GRANT USAGE ON DATABASE TROCCO_DATABASE TO ROLE TROCCO_OUTPUT_ROLE;

-- Grant schema usage permissions to roles

GRANT USAGE ON SCHEMA TROCCO_DATABASE.PUBLIC TO ROLE TROCCO_OUTPUT_ROLE;

-- Grant the role permission to create tables

GRANT CREATE TABLE ON SCHEMA TROCCO_DATABASE.PUBLIC TO ROLE TROCCO_OUTPUT_ROLE;

-- Grant privileges to a role to operate stages

GRANT CREATE STAGE on SCHEMA TROCCO_DATABASE.PUBLIC to ROLE TROCCO_OUTPUT_ROLE;

-- Transfer table ownership to a role
-- * Required when executing against an existing table

GRANT OWNERSHIP ON TABLE TROCCO_DATABASE.PUBLIC.TROCCO_TABLE TO ROLE TROCCO_OUTPUT_ROLE;

Permissions required to use datamarts

Sample query

• Role to create
  TROCCO_DATAMART_ROLE
• Who to grant the role to
  TROCCO_USER
• Target Warehouse
  TROCCO_WAREHOUSE
• Target database
  TROCCO_DATABASE
• Target Schema
  PUBLIC
• Target table
  TROCCO_TABLE
• * Please rewrite the roles, users, target warehouses, databases, schemas, and table names specified in the sample to the contents that are actually used.

-- Switch to roles that allow you to create roles

USE ROLE ACCOUNTADMIN;

-- Create a new role

CREATE ROLE TROCCO_DATAMART_ROLE;

-- Make the role available to specified users

GRANT ROLE TROCCO_DATAMART_ROLE TO USER TROCCO_USER;

-- Make the created role the default role for the user

ALTER USER TROCCO_USER SET DEFAULT_ROLE = TROCCO_DATAMART_ROLE;

-- Grant warehouse usage rights 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 permissions to roles

GRANT USAGE ON DATABASE TROCCO_DATABASE TO ROLE TROCCO_DATAMART_ROLE;

-- Grant schema usage permissions to roles

GRANT USAGE ON SCHEMA TROCCO_DATABASE.PUBLIC TO ROLE TROCCO_DATAMART_ROLE;

-- Grant the role permission to create tables

GRANT CREATE TABLE ON SCHEMA TROCCO_DATABASE.PUBLIC TO ROLE TROCCO_DATAMART_ROLE;

-- Grant the role permission to execute INSERT queries to the table

GRANT INSERT ON TABLE TROCCO_DATABASE.PUBLIC.TROCCO_TABLE TO ROLE TROCCO_DATAMART_ROLE;

-- Transfer table ownership to a role
-- * Required when executing against an existing table.

GRANT OWNERSHIP ON TABLE TROCCO_DATABASE.PUBLIC.TROCCO_TABLE TO ROLE TROCCO_DATAMART_ROLE;

About Key Pair Authentication

Creating a Key Pair

Execute the following command in the terminal

• Create a private key

$ openssl genrsa 2048 | openssl pkcs8 -topk8 -inform PEM -out rsa_key.p8 -nocrypt

• Generating a public key

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

• Output the public key for setting (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 the Public Key to Snowflake

-- Link the public key to any user

alter user [username] set rsa_public_key='[設定用の公開鍵の出力結果]'