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 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;

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;

Add additional warehouse data permissions

Seemore needs additional permissions to read warehouse configurations such as timeouts.

Per warehouse we need USAGEpermissions.

DECLARE
  res RESULTSET DEFAULT (SHOW WAREHOUSES);
  warehouse_name STRING;
  sql_command STRING;
BEGIN
  FOR warehouse IN res DO
    warehouse_name := warehouse."name";
    sql_command := 'GRANT USAGE ON WAREHOUSE "' || warehouse_name || '" TO ROLE ' || 'seemore_user_role' || ';';
    EXECUTE IMMEDIATE sql_command;
  END FOR;
END;

Additional Snowflake services

Streams and Dynamic tables

• Create (or use) a dedicated database and schema to house the procedure:

  CREATE DATABASE IF NOT EXISTS seemore;
  CREATE SCHEMA IF NOT EXISTS seemore.utils;

• Grant the necessary privileges to seemore_user_role so it can execute the procedure and view the results:

  GRANT USAGE ON DATABASE seemore TO ROLE seemore_user_role;
  GRANT USAGE ON SCHEMA seemore.utils TO ROLE seemore_user_role;

  (Adjust privileges if additional permissions are required by your security model.)

• Use a role with `ACCOUNTADMIN` to create or replace the procedure. This high-level role is needed to grant the procedure enough access to list all streams.

• Create (or replace) the stored procedure with EXECUTE AS OWNER. This setting ensures the procedure runs with the privileges of the owner (the role that created it). That way, even if the caller’s role does not have the privileges to see all streams directly, the procedure can still return the entire list:

  CREATE OR REPLACE PROCEDURE seemore.utils.list_object(OBJECT_KEY VARCHAR)
  RETURNS VARIANT
  LANGUAGE JAVASCRIPT
  EXECUTE AS OWNER
  AS
  $$
      var sql_command;
      if (OBJECT_KEY.toLowerCase() === 'streams') {
          sql_command = 'SHOW STREAMS IN ACCOUNT';
      } else if (OBJECT_KEY.toLowerCase() === 'dynamic_tables') {
          sql_command = 'SHOW DYNAMIC TABLES IN ACCOUNT';
      } else {
          throw 'Invalid object type. Use either "streams" or "dynamic_tables"';
      }
  
      var stmt = snowflake.createStatement({ sqlText: sql_command });
      var result = stmt.execute();
  
      var objectsList = [];
      while (result.next()) {
          var rowObj = {};
          for (var i = 1; i <= result.getColumnCount(); i++) {
              var colName = result.getColumnName(i);
              var colValue = result.getColumnValue(i);
              rowObj[colName] = colValue;
          }
          objectsList.push(rowObj);
      }
  
      return objectsList;
  $$
  ;
  
  GRANT USAGE ON PROCEDURE seemore.utils.list_object(VARCHAR) 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;