Create Snowflake User and Privileges

In this section, we'll take care of having the prerequisites for creating a Snowflake - Seemore integration.

Create a role

💭 Create a role in Snowflake using the following commands:

-- Switch to the ACCOUNTADMIN role to perform administrative tasks such as creating warehouses.
USE ROLE ACCOUNTADMIN;
CREATE OR REPLACE ROLE seemore_user_role;
GRANT OPERATE, USAGE ON WAREHOUSE "<warehouse_name>" TO ROLE seemore_user_role;

Replace <warehouse_name> with the default warehouse to use when running the Seemore snowflake "metadata fetcher".

Create a user

To create a user with a password, replace <password> and run the following:

CREATE USER seemore_user password='<password>' default_role=seemore_user_role default_warehouse='<warehouse_name>' display_name='SeemoreData';

2 types of authentication supported - Basic (using password) or Key-pair (using private/public key pair)

Key-pair authentication (Recommended)

To use key-pair authentication instead of password:

Generate the private key

To start, open a terminal window and generate a private key. You can generate either an encrypted version of the private key or an unencrypted version of the private key. To generate an unencrypted version, use the following command:

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

To generate an encrypted version, use the following command, which omits -nocrypt:

openssl genrsa 2048 | openssl pkcs8 -topk8 -v2 des3 -inform PEM -out rsa_key.p8

Generate a public key

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

Store the private and public keys securely !!!

Assign the public key to a Snowflake user

ALTER USER example_user SET RSA_PUBLIC_KEY='MIIBIjANBgkqh...';

For more details access official Snoflake documentation.

Grant role to user

To grant the user_role to the new user:

GRANT ROLE seemore_user_role TO USER seemore_user;

Grant Privileges

In order to fetch metadata from snowflake, Seemore needs access only to SNOWFLAKE

Option 1 (Using GRANT IMPORTED PRIVILEGES)

-- Grant the role permission to import metadata from the Snowflake database.
GRANT IMPORTED PRIVILEGES ON DATABASE SNOWFLAKE TO ROLE seemore_user_role;

-- Grant the role permission to monitor usage on the entire account.
GRANT MONITOR USAGE ON ACCOUNT TO ROLE seemore_user_role;

Option 2 (Using Secure Views)

USE ROLE ACCOUNTADMIN;
-- Grant the role permission to monitor usage on the entire account.
GRANT MONITOR USAGE ON ACCOUNT TO ROLE seemore_user_role;

CREATE DATABASE IF NOT EXISTS seemore_database;
CREATE OR REPLACE SCHEMA seemore_database.account_usage;
CREATE OR REPLACE SCHEMA seemore_database.organization_usage;

-- Create SECURE VIEWs for each table needed from SNOWFLAKE.ACCOUNT_USAGE
CREATE OR REPLACE SECURE VIEW seemore_database.account_usage.query_history AS
    SELECT * FROM SNOWFLAKE.ACCOUNT_USAGE.QUERY_HISTORY;

CREATE OR REPLACE SECURE VIEW seemore_database.account_usage.warehouse_metering_history AS
    SELECT * FROM SNOWFLAKE.ACCOUNT_USAGE.WAREHOUSE_METERING_HISTORY;

CREATE OR REPLACE SECURE VIEW seemore_database.account_usage.tables AS
    SELECT * FROM SNOWFLAKE.ACCOUNT_USAGE.TABLES;

CREATE OR REPLACE SECURE VIEW seemore_database.account_usage.columns AS
    SELECT * FROM SNOWFLAKE.ACCOUNT_USAGE.COLUMNS;

CREATE OR REPLACE SECURE VIEW seemore_database.account_usage.views AS
    SELECT * FROM SNOWFLAKE.ACCOUNT_USAGE.VIEWS;

CREATE OR REPLACE SECURE VIEW seemore_database.account_usage.pipes AS
    SELECT * FROM SNOWFLAKE.ACCOUNT_USAGE.PIPES;

CREATE OR REPLACE SECURE VIEW seemore_database.account_usage.procedures AS
    SELECT * FROM SNOWFLAKE.ACCOUNT_USAGE.PROCEDURES;

CREATE OR REPLACE SECURE VIEW seemore_database.account_usage.functions AS
    SELECT * FROM SNOWFLAKE.ACCOUNT_USAGE.FUNCTIONS;

CREATE OR REPLACE SECURE VIEW seemore_database.account_usage.task_versions AS
    SELECT * FROM SNOWFLAKE.ACCOUNT_USAGE.TASK_VERSIONS;

CREATE OR REPLACE SECURE VIEW seemore_database.account_usage.task_history AS
    SELECT * FROM SNOWFLAKE.ACCOUNT_USAGE.TASK_HISTORY;

CREATE OR REPLACE SECURE VIEW seemore_database.account_usage.database_storage_usage_history AS
    SELECT * FROM SNOWFLAKE.ACCOUNT_USAGE.DATABASE_STORAGE_USAGE_HISTORY;

CREATE OR REPLACE SECURE VIEW seemore_database.account_usage.databases AS
    SELECT * FROM SNOWFLAKE.ACCOUNT_USAGE.DATABASES;

CREATE OR REPLACE SECURE VIEW seemore_database.account_usage.stages AS
    SELECT * FROM SNOWFLAKE.ACCOUNT_USAGE.STAGES;

CREATE OR REPLACE SECURE VIEW seemore_database.account_usage.warehouse_events_history AS
    SELECT * FROM SNOWFLAKE.ACCOUNT_USAGE.WAREHOUSE_EVENTS_HISTORY;

CREATE OR REPLACE SECURE VIEW seemore_database.account_usage.users AS
    SELECT * FROM SNOWFLAKE.ACCOUNT_USAGE.USERS;
    
CREATE OR REPLACE SECURE VIEW seemore_database.account_usage.query_acceleration_eligible AS
    SELECT * FROM SNOWFLAKE.ACCOUNT_USAGE.QUERY_ACCELERATION_ELIGIBLE;

CREATE OR REPLACE SECURE VIEW seemore_database.account_usage.query_acceleration_history AS
    SELECT * FROM SNOWFLAKE.ACCOUNT_USAGE.QUERY_ACCELERATION_HISTORY;     
    
-- Create SECURE VIEWs for each table needed from SNOWFLAKE.ORGANIZATION_USAGE
CREATE OR REPLACE SECURE VIEW seemore_database.organization_usage.accounts AS
    SELECT * FROM SNOWFLAKE.ORGANIZATION_USAGE.ACCOUNTS;

CREATE OR REPLACE SECURE VIEW seemore_database.organization_usage.metering_daily_history AS
    SELECT * FROM SNOWFLAKE.ORGANIZATION_USAGE.METERING_DAILY_HISTORY;

CREATE OR REPLACE SECURE VIEW seemore_database.organization_usage.rate_sheet_daily AS
    SELECT * FROM SNOWFLAKE.ORGANIZATION_USAGE.RATE_SHEET_DAILY;

CREATE OR REPLACE SECURE VIEW seemore_database.organization_usage.warehouse_metering_history AS
    SELECT * FROM SNOWFLAKE.ORGANIZATION_USAGE.WAREHOUSE_METERING_HISTORY;

-- Grant usage on the new database and schema to the role
GRANT USAGE ON DATABASE seemore_database TO ROLE seemore_user_role;
GRANT USAGE ON SCHEMA seemore_database.account_usage TO ROLE seemore_user_role;
GRANT USAGE ON SCHEMA seemore_database.organization_usage TO ROLE seemore_user_role;

-- Grant SELECT on all the created views
GRANT SELECT ON ALL VIEWS IN SCHEMA seemore_database.account_usage TO ROLE seemore_user_role;
GRANT SELECT ON FUTURE VIEWS IN SCHEMA seemore_database.account_usage TO ROLE seemore_user_role;
GRANT SELECT ON ALL VIEWS IN SCHEMA seemore_database.organization_usage TO ROLE seemore_user_role;
GRANT SELECT ON FUTURE VIEWS IN SCHEMA seemore_database.organization_usage TO ROLE seemore_user_role;

Comprehensive Setup Script

This script encompasses the entire setup process, including creating a dedicated warehouse for Seemore, establishing roles, creating a user, and assigning the necessary privileges for operations and monitoring. Please make sure to replace <Your Password>. If you don't need the warehouse just comment out the (lines 8-19), and replace seemore_warehouse (line 26) with your warehouse.

-- Switch to the ACCOUNTADMIN role to perform administrative tasks such as creating warehouses.
USE ROLE ACCOUNTADMIN;
SET var_password = '<Your Password>';

-- Optionally create a new warehouse named "seemore_warehouse" if it does not already exist.
-- The warehouse is set to 'XSMALL' size, with auto-suspension after 60 seconds of inactivity.
-- It's initially suspended to avoid unnecessary costs. A comment is added for clarity.
CREATE WAREHOUSE IF NOT EXISTS seemore_warehouse
    WAREHOUSE_SIZE='XSMALL'
    AUTO_SUSPEND=60
    INITIALLY_SUSPENDED=TRUE
    COMMENT = 'Used by seemore';

CREATE OR REPLACE RESOURCE MONITOR seemore_monitor
    WITH CREDIT_QUOTA = 50
    FREQUENCY = 'MONTHLY'
    START_TIMESTAMP = 'IMMEDIATELY'
    TRIGGERS ON 100 PERCENT DO SUSPEND;

-- Replace YOUR_PREFERRED_WAREHOUSE_NAME with the name of your warehouse
ALTER WAREHOUSE seemore_warehouse SET RESOURCE_MONITOR = seemore_monitor;
              
-- Create a new role for the seemore user. This role will manage permissions for the seemore user.
CREATE OR REPLACE ROLE seemore_user_role;

-- Grant the new role permissions to operate and use the "seemore_warehouse".
GRANT OPERATE, USAGE ON WAREHOUSE seemore_warehouse TO ROLE seemore_user_role;

-- Create a new user named "seemore_user" with the specified settings.
-- The user's default role and warehouse are set to "seemore_user_role" and "seemore_warehouse", respectively.
CREATE OR REPLACE USER seemore_user 
    PASSWORD = $var_password
    DEFAULT_ROLE = seemore_user_role 
    DEFAULT_WAREHOUSE = 'seemore_warehouse' 
    DISPLAY_NAME = 'SeemoreData';

-- Grant the previously created role to the seemore user.
GRANT ROLE seemore_user_role TO USER seemore_user;

-- Grant the role permission to import metadata from the Snowflake database.
GRANT IMPORTED PRIVILEGES ON DATABASE SNOWFLAKE TO ROLE seemore_user_role;

-- Grant the role permission to monitor usage on the entire account.
GRANT MONITOR USAGE ON ACCOUNT TO ROLE seemore_user_role;

Allowlist the Seemore IP

If you are using the IP allowlist in your Snowflake instance, you must add the Seemore IP to the allowlist.

(If you are not using the IP Allowlist in your Snowflake instance, you can skip this step.)

CREATE NETWORK POLICY seemore_porduction ALLOWED_IP_LIST=('3.78.110.132', '3.73.171.134');
ALTER USER seemore_user SET NETWORK_POLICY = seemore_porduction;

PreviousSnowflake NextIntegration with Seemore

Last updated 17 hours ago