# Create Snowflake User and Privileges

### Create a role <a href="#create-a-user-0-1" id="create-a-user-0-1"></a>

Create a role in Snowflake using the following commands:

{% code lineNumbers="true" %}

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

{% endcode %}

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

### Create a user <a href="#create-a-user-0-1" id="create-a-user-0-1"></a>

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

{% code overflow="wrap" lineNumbers="true" %}

```sql
CREATE USER IF NOT EXISTS seemore_user password='<password>' default_role=seemore_user_role default_warehouse='<warehouse_name>' display_name='SeemoreData';
```

{% endcode %}

{% hint style="info" %}
2 types of authentication supported - Basic (using password) or Key-pair (using private/public key pair)
{% endhint %}

### 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:

```bash
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`:

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

#### Generate a public key

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

{% hint style="danger" %} <mark style="color:$danger;">**Store the private and public keys securely !!!**</mark>
{% endhint %}

{% hint style="warning" %}
When providing the private key to Seemore, make sure to include the full PEM format **with the header and footer lines**:

```
-----BEGIN ENCRYPTED PRIVATE KEY-----
MIIFJDBWBgkqhkiG9w0BBQ0wSTAxBgkqhkiG9w0BBQwwJAQQ/rZQBkrrC7xzJPDV...
-----END ENCRYPTED PRIVATE KEY-----
```

For unencrypted keys the header/footer will be `-----BEGIN PRIVATE KEY-----` / `-----END PRIVATE KEY-----`.
{% endhint %}

#### Assign the public key to a Snowflake user

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

For more details access official Snowflake [documentation](https://docs.snowflake.com/en/user-guide/key-pair-auth).

### Grant role to user <a href="#grant-role-to-user-0-5" id="grant-role-to-user-0-5"></a>

To grant the `user_role` to the new user:

{% code lineNumbers="true" %}

```sql
GRANT ROLE seemore_user_role TO USER seemore_user;
```

{% endcode %}

### Grant Privileges <a href="#choose-metadata-fetching-method-0-6" id="choose-metadata-fetching-method-0-6"></a>

In order to fetch metadata from snowflake,\
Seemore needs access only to SNOWFLAKE using GRANT IMPORTED PRIVILEGES.

{% code lineNumbers="true" %}

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

{% endcode %}

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

{% code lineNumbers="true" expandable="true" %}

```sql
-- 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 a new role for the seemore user, only if it doesn't exist.
CREATE ROLE IF NOT EXISTS 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" only if it doesn't exist.
-- The user's default role and warehouse are set to "seemore_user_role" and "seemore_warehouse", respectively.
CREATE USER IF NOT EXISTS 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;

-- Grant the ability to monitor warehouse settings through the manage warehouses permission
-- This permission is also required for Seemore to retrieve query statistics using get_query_operator_stats, and for the smart pulse, auto-shutdown, auto-scaler
GRANT MANAGE WAREHOUSES ON ACCOUNT TO ROLE seemore_user_role;
```

{% endcode %}

### Monitoring Additional Snowflake Services <a href="#allowlist-the-datamaze-ip-0-15" id="allowlist-the-datamaze-ip-0-15"></a>

#### Tune Warehouses Automations

To allow Seemore to analyze, optimize, and implement the best configurations for your warehouses, please grant the necessary permissions. These permissions are also needed for Seemore to access query statistics via `get_query_operator_stats` and to perform smart pulse, auto-shutdown, and auto-scaling operations.

```sql
-- Grant the ability to monitor warehouse settings through the manage warehouses permission
-- This permission is also required for Seemore to retrieve query statistics using get_query_operator_stats, and for the smart pulse, auto-shutdown, auto-scaler
GRANT MANAGE WAREHOUSES ON ACCOUNT TO ROLE seemore_user_role;
```

#### Streams, Dynamic tables & Materialized Views <a href="#allowlist-the-datamaze-ip-0-15" id="allowlist-the-datamaze-ip-0-15"></a>

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

  ```sql
  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:

  ```sql
  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:

{% code expandable="true" %}

```sql
CREATE OR REPLACE PROCEDURE seemore.utils.list_object(OBJECT_KEY VARCHAR)
RETURNS VARIANT
LANGUAGE JAVASCRIPT
EXECUTE AS OWNER
AS
$$
    var sql_command;
    var key = OBJECT_KEY.toLowerCase();

    if (key === 'streams') {
        sql_command = 'SHOW STREAMS IN ACCOUNT';
    } else if (key === 'dynamic_tables') {
        sql_command = 'SHOW DYNAMIC TABLES IN ACCOUNT';
    } else if (key === 'materialized_views') {
        sql_command = 'SHOW MATERIALIZED VIEWS IN ACCOUNT';
    } else {
        throw 'Invalid object type. Use one of: "streams", "dynamic_tables", "materialized_views"';
    }

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

{% endcode %}

### Auto clustering recommendations <a href="#allowlist-the-datamaze-ip-0-15" id="allowlist-the-datamaze-ip-0-15"></a>

#### Permissions to Auto Clustering monitoring and recommendations <a href="#allowlist-the-datamaze-ip-0-15" id="allowlist-the-datamaze-ip-0-15"></a>

By creating the below procedures and give Seemore the access to it we will be able to help you cluster your tables in a more efficient way and also improve Snowflake's auto-clustering feature,\
Without the need of exposing data at all.

{% hint style="success" %}
**This configuration grants access to table metadata only, ensuring your actual data remains private and inaccessible**
{% endhint %}

* First, create the Seemore database and schema if not exists:

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

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

* **It is important to create the following procedures using \`ACCOUNTADMIN\` role.**\
  This high-level role is needed to grant the procedure access to all the tables clustering information.
* **GET\_CLUSTERING\_INFO** -\
  Create a procedure that gives Seemore an ability to access [snowflake clustering information method](https://docs.snowflake.com/en/sql-reference/functions/system_clustering_information), which allows us optimizing our recommendation of clustering columns candidates,\
  based on actual cardinality stats of the tables.

{% code expandable="true" %}

```sql
CREATE OR REPLACE PROCEDURE SEEMORE.UTILS.GET_CLUSTERING_INFO(
  table_fqn STRING,
  column_combinations ARRAY
)
RETURNS VARIANT
LANGUAGE JAVASCRIPT
EXECUTE AS OWNER
AS
$$
  // Validate inputs
  if (!TABLE_FQN || typeof TABLE_FQN !== 'string' || TABLE_FQN.trim() === '') {
    throw new Error('table_fqn must be a non-empty string');
  }
  
  if (!COLUMN_COMBINATIONS || !Array.isArray(COLUMN_COMBINATIONS) || COLUMN_COMBINATIONS.length === 0) {
    throw new Error('column_combinations must be a non-empty array');
  }
  
  const results = [];
  
  // Process each column combination
  for (let i = 0; i < COLUMN_COMBINATIONS.length; i++) {
    const combination = COLUMN_COMBINATIONS[i];
    
    // Validate combination is an array
    if (!Array.isArray(combination) || combination.length === 0) {
      // Skip invalid combinations but log them
      results.push({
        combination: [],
        error: 'Invalid combination: must be a non-empty array',
        metrics: null
      });
      continue;
    }
    
    try {
      // Escape column names for SQL (handle quotes and special characters)
      const escapedColumns = combination.map(col => {
        if (typeof col !== 'string') {
          throw new Error(`Column name must be a string, got: ${typeof col}`);
        }
        return col;
      });
      
      // Build column list string for SYSTEM$CLUSTERING_INFORMATION
      // Format: (col1, col2, col3)
      const columnList = '(' + escapedColumns.join(', ') + ')';
      
      // Build the SQL statement - use parameterized query to prevent SQL injection
      const sql = `SELECT SYSTEM$CLUSTERING_INFORMATION('${TABLE_FQN}', '${columnList}') AS clustering_info`;
      
      // Execute the statement
      const stmt = snowflake.createStatement({ sqlText: sql });
      const resultSet = stmt.execute();
      
      if (resultSet.next()) {
        const clusteringInfo = resultSet.getColumnValue(1);
        
        // Parse the JSON response from SYSTEM$CLUSTERING_INFORMATION
        let metrics;
        if (typeof clusteringInfo === 'string') {
          try {
            metrics = JSON.parse(clusteringInfo);
          } catch (parseErr) {
            throw new Error(`Failed to parse clustering info JSON: ${parseErr.message}`);
          }
        } else if (clusteringInfo && typeof clusteringInfo === 'object') {
          metrics = clusteringInfo;
        } else {
          throw new Error(`Unexpected clustering info format: ${typeof clusteringInfo}`);
        }
        
        // Build result object with all relevant metrics
        results.push({
          combination: combination,
          metrics: {
            average_depth: metrics.average_depth !== undefined ? metrics.average_depth : null,
            average_overlaps: metrics.average_overlaps !== undefined ? metrics.average_overlaps : null,
            total_partition_count: metrics.total_partition_count !== undefined ? metrics.total_partition_count : null,
            total_constant_partition_count: metrics.total_constant_partition_count !== undefined ? metrics.total_constant_partition_count : null,
            cluster_by_keys: metrics.cluster_by_keys || null,
            notes: metrics.notes || null,
            partition_depth_histogram: metrics.partition_depth_histogram || null
          }
        });
      } else {
        // No result returned
        results.push({
          combination: combination,
          error: 'No result returned from SYSTEM$CLUSTERING_INFORMATION',
          metrics: null
        });
      }
    } catch (err) {
      // Log error but continue with other combinations
      const errorMessage = err.message || String(err);
      results.push({
        combination: combination,
        error: errorMessage,
        metrics: null
      });
    }
  }
  
  // Return results as VARIANT (automatically converted from JavaScript array/object)
  return results;
$$;

GRANT USAGE ON PROCEDURE SEEMORE.UTILS.GET_CLUSTERING_INFO(VARCHAR, ARRAY) TO ROLE seemore_user_role;
```

{% endcode %}

* **GET\_CLUSTERING\_COST\_ESTIMATE**\
  Create a procedure that gives Seemore an access to [auto clustering cost estimate method](https://docs.snowflake.com/en/sql-reference/functions/system_estimate_automatic_clustering_costs),\
  So Seemore can use it for estimating the cost of turning on auto clustering based on out recommendations.

{% code expandable="true" %}

```sql
CREATE OR REPLACE PROCEDURE SEEMORE.UTILS.GET_CLUSTERING_COST_ESTIMATE(
  table_fqn STRING,  
  column_combination ARRAY
)
RETURNS VARIANT
LANGUAGE JAVASCRIPT
EXECUTE AS OWNER
AS
$$
  // Validate inputs
  if (!TABLE_FQN || typeof TABLE_FQN !== 'string' || TABLE_FQN.trim() === '') {
    throw new Error('table_fqn must be a non-empty string');
  }
  
  if (!COLUMN_COMBINATION || !Array.isArray(COLUMN_COMBINATION) || COLUMN_COMBINATION.length === 0) {
    throw new Error('column_combination must be a non-empty array');
  }
  
  try {
    // Escape column names for SQL (handle quotes and special characters)
    const escapedColumns = COLUMN_COMBINATION.map(col => {
      if (typeof col !== 'string') {
        throw new Error(`Column name must be a string, got: ${typeof col}`);
      }
      // Escape double quotes by doubling them
      const escaped = col.replace(/"/g, '""');
      return `"${escaped}"`;
    });
    
    // Build column list string for SYSTEM$ESTIMATE_AUTOMATIC_CLUSTERING_COSTS
    // Format: (col1, col2, col3)
    const columnList = '(' + escapedColumns.join(', ') + ')';
    
    // Build the SQL statement
    const sql = `SELECT SYSTEM$ESTIMATE_AUTOMATIC_CLUSTERING_COSTS('${TABLE_FQN}', '${columnList}') AS cost_estimation`;
    
    // Execute the statement
    const stmt = snowflake.createStatement({ sqlText: sql });
    const resultSet = stmt.execute();
    
    if (resultSet.next()) {
      const costEstimationJson = resultSet.getColumnValue(1);
      
      // Parse the JSON response from SYSTEM$ESTIMATE_AUTOMATIC_CLUSTERING_COSTS
      let costEstimation;
      if (typeof costEstimationJson === 'string') {
        try {
          costEstimation = JSON.parse(costEstimationJson);
        } catch (parseErr) {
          throw new Error(`Failed to parse cost estimation JSON: ${parseErr.message}`);
        }
      } else if (costEstimationJson && typeof costEstimationJson === 'object') {
        costEstimation = costEstimationJson;
      } else {
        throw new Error(`Unexpected cost estimation format: ${typeof costEstimationJson}`);
      }
      
      // Return result object with combination and costs
      return {
        combination: COLUMN_COMBINATION,
        costs: {
          initial: costEstimation.initial !== undefined ? costEstimation.initial : null,
          maintenance: costEstimation.maintenance !== undefined ? costEstimation.maintenance : null
        }
      };
    } else {
      // No result returned
      throw new Error('No result returned from SYSTEM$ESTIMATE_AUTOMATIC_CLUSTERING_COSTS');
    }
  } catch (err) {
    // Return error information
    const errorMessage = err.message || String(err);
    return {
      combination: COLUMN_COMBINATION,
      error: errorMessage,
      costs: null
    };
  }
$$;


GRANT USAGE ON PROCEDURE SEEMORE.UTILS.GET_CLUSTERING_COST_ESTIMATE(VARCHAR, ARRAY) TO ROLE seemore_user_role;

```

{% endcode %}

### Enable ORGADMIN <a href="#enable-orgadmin" id="enable-orgadmin"></a>

To unlock **organization-level observability** and **contract observability** in Seemore, the Snowflake account must be attached to the `ORGADMIN` role.

1. Log in to your Snowflake organization account with a user that has the `ORGADMIN` role.
2. Navigate to **Admin → Accounts**.
3. Click the **`...`** menu on the account you want to connect to Seemore.
4. Select **Enable ORGADMIN**.

<figure><img src="https://3620459840-files.gitbook.io/~/files/v0/b/gitbook-x-prod.appspot.com/o/spaces%2FnSbIoBjUxWTGNWa9gGw7%2Fuploads%2Fgit-blob-b4c80fbfec5b4aa51bc8c54ba53ade869624e871%2Fenable-orgadmin.png?alt=media" alt="Enable ORGADMIN on a Snowflake account"><figcaption><p>Enable ORGADMIN from the Accounts page</p></figcaption></figure>

Alternatively, you can enable it via SQL:

```sql
USE ROLE ORGADMIN;

-- Verify current account
SELECT CURRENT_ACCOUNT_NAME();

-- Enable ORGADMIN on specific accounts
-- Replace <ACCOUNT_NAME> with the target account name
-- Run SHOW ACCOUNTS; to find available account names
ALTER ACCOUNT <ACCOUNT_NAME> SET IS_ORG_ADMIN = TRUE;
```

{% hint style="info" %}
Once ORGADMIN is enabled, grant the `ORGADMIN` role to the Seemore user role so Seemore can query organization-level usage and contract data:

```sql
GRANT ROLE ORGADMIN TO ROLE seemore_user_role;
```

{% endhint %}

### Allowlist the Seemore IP <a href="#allowlist-the-datamaze-ip-0-15" id="allowlist-the-datamaze-ip-0-15"></a>

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

{% code lineNumbers="true" %}

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

{% endcode %}