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) - Not Recommended

This approach requires manual updates if the underlying table schemas change.

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;

-- 1. Create a helper temporary table with view definitions (database_name, schema_name, table_name).
CREATE OR REPLACE TEMPORARY TABLE view_definitions (
    database_name STRING,
    schema_name   STRING,
    table_name    STRING
);

-- 2. Insert definitions for each view.
INSERT INTO view_definitions (database_name, schema_name, table_name)
VALUES
  ('SNOWFLAKE', 'ACCOUNT_USAGE', 'QUERY_HISTORY'),
  ('SNOWFLAKE', 'ACCOUNT_USAGE', 'WAREHOUSE_METERING_HISTORY'),
  ('SNOWFLAKE', 'ACCOUNT_USAGE', 'TABLES'),
  ('SNOWFLAKE', 'ACCOUNT_USAGE', 'COLUMNS'),
  ('SNOWFLAKE', 'ACCOUNT_USAGE', 'VIEWS'),
  ('SNOWFLAKE', 'ACCOUNT_USAGE', 'PIPES'),
  ('SNOWFLAKE', 'ACCOUNT_USAGE', 'PROCEDURES'),
  ('SNOWFLAKE', 'ACCOUNT_USAGE', 'FUNCTIONS'),
  ('SNOWFLAKE', 'ACCOUNT_USAGE', 'TASK_VERSIONS'),
  ('SNOWFLAKE', 'ACCOUNT_USAGE', 'TASK_HISTORY'),
  ('SNOWFLAKE', 'ACCOUNT_USAGE', 'DATABASE_STORAGE_USAGE_HISTORY'),
  ('SNOWFLAKE', 'ACCOUNT_USAGE', 'DATABASES'),
  ('SNOWFLAKE', 'ACCOUNT_USAGE', 'STAGES'),
  ('SNOWFLAKE', 'ACCOUNT_USAGE', 'WAREHOUSE_EVENTS_HISTORY'),
  ('SNOWFLAKE', 'ACCOUNT_USAGE', 'USERS'),
  ('SNOWFLAKE', 'ACCOUNT_USAGE', 'QUERY_ACCELERATION_ELIGIBLE'),
  ('SNOWFLAKE', 'ACCOUNT_USAGE', 'QUERY_ACCELERATION_HISTORY'),
  ('SNOWFLAKE', 'ORGANIZATION_USAGE', 'ACCOUNTS'),
  ('SNOWFLAKE', 'ORGANIZATION_USAGE', 'METERING_DAILY_HISTORY'),
  ('SNOWFLAKE', 'ORGANIZATION_USAGE', 'RATE_SHEET_DAILY'),
  ('SNOWFLAKE', 'ORGANIZATION_USAGE', 'WAREHOUSE_METERING_HISTORY');

-- 3. Create a stored procedure that builds and executes the secure view DDL using INFORMATION_SCHEMA.
CREATE OR REPLACE PROCEDURE create_secure_views_from_metadata()
RETURNS STRING
LANGUAGE JAVASCRIPT
EXECUTE AS CALLER
AS
$$
  // Retrieve view definitions from the helper table.
  var sql_get_definitions = "SELECT database_name, schema_name, table_name FROM view_definitions";
  var stmt = snowflake.createStatement({ sqlText: sql_get_definitions });
  var rs = stmt.execute();
  var output = "";

  while (rs.next()) {
    var source_database = rs.getColumnValue("DATABASE_NAME");
    var source_schema   = rs.getColumnValue("SCHEMA_NAME");
    var source_table    = rs.getColumnValue("TABLE_NAME");

    // Build the column list by querying INFORMATION_SCHEMA.COLUMNS.
    // We now include a condition for TABLE_CATALOG to match the source database.
    var col_query = "SELECT COLUMN_NAME, ORDINAL_POSITION " +
                    "FROM \"" + source_database.toUpperCase() + "\".INFORMATION_SCHEMA.COLUMNS " +
                    "WHERE UPPER(TABLE_CATALOG) = '" + source_database.toUpperCase() + "' " +
                    "AND UPPER(TABLE_SCHEMA) = '" + source_schema.toUpperCase() + "' " +
                    "AND UPPER(TABLE_NAME) = '" + source_table.toUpperCase() + "' " +
                    "ORDER BY ORDINAL_POSITION";
    var col_stmt = snowflake.createStatement({ sqlText: col_query });
    var col_rs = col_stmt.execute();
    
    var colsArray = [];
    var seen = {};  // To filter duplicate column names.
    while (col_rs.next()) {
      var colName = col_rs.getColumnValue("COLUMN_NAME");
      if (!seen.hasOwnProperty(colName)) {
        seen[colName] = true;
        colsArray.push('"' + colName + '"');
      }
    }
    
    // If no columns are found, log a message and skip this view.
    if (colsArray.length === 0) {
      output += "No columns found for " + source_database + "." + source_schema + "." + source_table + "\n";
      continue;
    }
    
    var cols = colsArray.join(', ');

    // Build the CREATE OR REPLACE SECURE VIEW statement.
    var view_sql = "CREATE OR REPLACE SECURE VIEW SEEMORE_DATABASE.\"" + source_schema.toUpperCase() + "\".\"" + source_table.toUpperCase() + "\" AS " +
                   "SELECT " + cols + " " +
                   "FROM \"" + source_database.toUpperCase() + "\".\"" + source_schema.toUpperCase() + "\".\"" + source_table.toUpperCase() + "\"";
    output += view_sql + "\n";

    var create_stmt = snowflake.createStatement({ sqlText: view_sql });
    create_stmt.execute();
  }
  
  return output;
$$;

-- 4. Execute the stored procedure.
CALL create_secure_views_from_metadata();


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

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;

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;