> ## Documentation Index
> Fetch the complete documentation index at: https://docs.cotool.ai/llms.txt
> Use this file to discover all available pages before exploring further.

# Snowflake Integration

> Connect Cotool to Snowflake for read-only investigation, detection, and hunting queries over security data in your warehouse, and to ingest Snowflake's login, query, and access history

## Overview

The Snowflake integration lets Cotool agents query security data in your Snowflake account, and collects Snowflake's own login, query, and access history into Cotool. It provides:

1. **Data discovery**: browse databases, schemas, tables, and views, search for tables by name, and inspect a table's columns, clustering key, size, comments, and sample rows.
2. **Read-only SQL**: run Snowflake SQL for investigations, alert triage, threat hunting, and code detections. Results include typed columns and a `truncated` flag when more rows matched than were returned.
3. **Log ingest**: collect `LOGIN_HISTORY`, `QUERY_HISTORY`, and `ACCESS_HISTORY` from `SNOWFLAKE.ACCOUNT_USAGE` for search and detections. See [Log ingest](#log-ingest).

Cotool signs in as a dedicated Snowflake user, preferably with **key-pair authentication**. Snowflake has no read-only session mode, so the user's role is what keeps Cotool read-only, and it decides what Cotool can see: grant only what is listed below. To use Snowflake only for log ingest, without giving agents access to your data, see [Ingest-only access](#ingest-only-access).

## Before you start

You will need:

* A role with `USERADMIN` (or `SECURITYADMIN`) to create a user and role, and the **owner** of the databases holding your security data to grant read access.
* A **warehouse** Cotool can use. We recommend a dedicated X-Small warehouse with auto-suspend and auto-resume:
  * Every Cotool query runs on that warehouse and consumes credits. A dedicated warehouse keeps Cotool's cost separate from your other workloads.
  * Discovery uses `SHOW` and `DESCRIBE`, which do not need a running warehouse. Queries and sample rows do.

## Set up Snowflake

<Steps>
  <Step title="Create a key pair">
    Generate an RSA key pair on your machine. The private key goes into Cotool; the public key goes on the Snowflake user.

    ```bash theme={null}
    openssl genrsa 2048 | openssl pkcs8 -topk8 -inform PEM -out cotool_key.p8 -nocrypt
    openssl rsa -in cotool_key.p8 -pubout -out cotool_key.pub
    ```

    To encrypt the private key, drop `-nocrypt` and enter the passphrase in Cotool as well.
  </Step>

  <Step title="Create a role, a warehouse, and a service user">
    ```sql theme={null}
    USE ROLE USERADMIN;
    CREATE ROLE IF NOT EXISTS COTOOL_READER;
    CREATE USER IF NOT EXISTS COTOOL_SVC
      TYPE = SERVICE
      DEFAULT_ROLE = COTOOL_READER
      DEFAULT_WAREHOUSE = COTOOL_WH
      RSA_PUBLIC_KEY = '<contents of cotool_key.pub, without the BEGIN and END lines>';
    GRANT ROLE COTOOL_READER TO USER COTOOL_SVC;

    USE ROLE SYSADMIN;
    CREATE WAREHOUSE IF NOT EXISTS COTOOL_WH
      WAREHOUSE_SIZE = XSMALL AUTO_SUSPEND = 60 AUTO_RESUME = TRUE INITIALLY_SUSPENDED = TRUE;
    GRANT USAGE ON WAREHOUSE COTOOL_WH TO ROLE COTOOL_READER;
    ```

    <Note>
      Snowflake blocks single-factor password sign-in for `TYPE = SERVICE` users. Password authentication remains available in Cotool for human users, but Snowflake is phasing it out; use key-pair authentication.
    </Note>
  </Step>

  <Step title="Grant read access to your security data">
    Skip this step for [ingest-only access](#ingest-only-access).

    ```sql theme={null}
    -- Replace SECURITY with the database that holds your security data.
    GRANT USAGE ON DATABASE SECURITY TO ROLE COTOOL_READER;
    GRANT USAGE ON ALL SCHEMAS IN DATABASE SECURITY TO ROLE COTOOL_READER;
    GRANT SELECT ON ALL TABLES IN DATABASE SECURITY TO ROLE COTOOL_READER;
    GRANT SELECT ON ALL VIEWS IN DATABASE SECURITY TO ROLE COTOOL_READER;
    GRANT USAGE ON FUTURE SCHEMAS IN DATABASE SECURITY TO ROLE COTOOL_READER;
    GRANT SELECT ON FUTURE TABLES IN DATABASE SECURITY TO ROLE COTOOL_READER;
    GRANT SELECT ON FUTURE VIEWS IN DATABASE SECURITY TO ROLE COTOOL_READER;
    ```

    Do not grant ownership, `INSERT`, `UPDATE`, `DELETE`, or `ALL PRIVILEGES`. Cotool only needs to read.
  </Step>

  <Step title="Grant access to Snowflake's audit views">
    Log ingest reads `SNOWFLAKE.ACCOUNT_USAGE`, and agents can query these views too. Grant the database roles for the streams you will turn on:

    ```sql theme={null}
    USE ROLE ACCOUNTADMIN;
    GRANT DATABASE ROLE SNOWFLAKE.SECURITY_VIEWER TO ROLE COTOOL_READER;   -- LOGIN_HISTORY, SESSIONS
    GRANT DATABASE ROLE SNOWFLAKE.GOVERNANCE_VIEWER TO ROLE COTOOL_READER; -- QUERY_HISTORY, ACCESS_HISTORY
    ```

    Prefer these database roles over `GRANT IMPORTED PRIVILEGES ON DATABASE SNOWFLAKE`, which works but exposes every view in the `SNOWFLAKE` database.
  </Step>
</Steps>

## Connect in Cotool

1. In Cotool, go to **Platform > Integrations > Snowflake** and click **Connect**.
2. Enter the values you collected:

| Cotool field | Where it comes from |
| - | - |
| Account | Your account identifier, for example `myorg-myaccount` (or the legacy `xy12345.us-east-1`) |
| Username | The service user, for example `COTOOL_SVC` |
| Authentication method | **Key pair** (recommended) |
| Private key (PEM) | The contents of `cotool_key.p8`, including the `BEGIN` and `END` lines |
| Private key passphrase | Only if the key is encrypted |
| Warehouse | Optional. Leave blank to let Cotool pick one, or enter a warehouse to pin it |
| Default database, schema, and role | Optional. Agents fully qualify table names, so these are rarely needed. The role defaults to the user's default role |

3. Click **Connect**. Cotool signs in and confirms the warehouse before saving. If the check fails, the error explains which part to fix (see [Troubleshooting](#troubleshooting)).

If you leave the warehouse blank, Cotool uses the user's default warehouse when the role can use it. Otherwise it picks a warehouse the role can use: a running one first, then the smallest standard warehouse. It never picks a suspended warehouse without auto-resume. If that warehouse is later dropped or the grant is revoked, Cotool switches to another one. Enter a warehouse when Cotool's queries and their cost must stay on a specific one.

## Network access

If a Snowflake network policy restricts access, allow Cotool's egress IP addresses, listed on the Snowflake connect form in Cotool (**Platform > Integrations > Snowflake > Connect**). Cotool cannot reach accounts that only accept PrivateLink connections.

A blocked connection fails with "IP ... is not allowed to access Snowflake".

## How Cotool queries Snowflake

* **Discovery is incremental.** Agents list databases, then schemas, then tables and views, and describe only the tables they need. Discovery uses `SHOW` and `DESCRIBE`, which read metadata and do not resume the warehouse. Sampling rows does use the warehouse.
* **Queries are read-only and single-statement.** Cotool accepts only `SELECT`, `WITH`, `SHOW`, `DESCRIBE`, and `EXPLAIN`, and Snowflake rejects more than one statement per call. The role's grants remain the real boundary.
* **Results are capped.** Queries return 500 rows by default and at most 10,000. When more rows match, the response says `truncated: true`, and the agent narrows or aggregates the query.
* **Queries time out and are cancelled.** Each statement has a timeout (300 seconds by default, at most 30 minutes), enforced by Snowflake and by Cotool, so abandoned queries do not keep running on your warehouse.
* **Queries are attributable.** Cotool's statements appear in **Query History** under the service user with the query tag `cotool-agent`, so you can audit and cost them.
* **Values are exact.** Timestamps come back as UTC ISO-8601. Numbers beyond 2^53 and decimals come back as strings, so IDs and amounts keep full precision.

Agents query fastest when security tables are laid out for time-bounded reads:

* Cluster large log tables on an event date or timestamp column. Cotool shows agents the clustering key and tells them to filter on it first.
* Add table and column comments describing each source (for example, "Okta System Log events"). Agents see comments during discovery and use them to pick the right table.
* Use one table per log source rather than one mixed table, so agents do not have to filter by source type.

## Environment map

After you connect, and weekly after that, Cotool maps the account's tables and views: each one's columns, clustering key, row count, a few sample rows, and a summary of what it holds (for example, which log source it is). Agents start from this map instead of rediscovering the account, and detection planning uses it to know which log sources you have. View it on the Snowflake integration page in Cotool.

* The map covers the tables and views the role can see, up to 300. It skips the `SNOWFLAKE` and `SNOWFLAKE_SAMPLE_DATA` databases, Native App databases, and `INFORMATION_SCHEMA`; agents can still query those directly, and already know the `SNOWFLAKE.ACCOUNT_USAGE` views.
* Listing and describing tables does not resume the warehouse. Sampling runs one `SELECT * ... LIMIT 3` per table, so mapping briefly resumes it. If sampling keeps failing, Cotool maps the remaining tables without sample rows.
* Tables created between runs are not in the map until the next run, but agents can still find them with discovery.

## Log ingest

Cotool can collect three of Snowflake's `ACCOUNT_USAGE` views using the same connection. Each stream is off until you turn it on in the integration's **Log Ingest** settings (see [Connect log sources](/log-ingest/connect-sources)).

| Stream | Source view | Database role needed | Snowflake publishes rows up to |
| - | - | - | - |
| Login history | `SNOWFLAKE.ACCOUNT_USAGE.LOGIN_HISTORY` | `SNOWFLAKE.SECURITY_VIEWER` | 2 hours late |
| Query history | `SNOWFLAKE.ACCOUNT_USAGE.QUERY_HISTORY` | `SNOWFLAKE.GOVERNANCE_VIEWER` | 45 minutes late |
| Access history | `SNOWFLAKE.ACCOUNT_USAGE.ACCESS_HISTORY` | `SNOWFLAKE.GOVERNANCE_VIEWER` | 3 hours late |

* Access history requires Snowflake Enterprise Edition or higher.
* Cotool polls each stream every 15 minutes, and stays behind real time by that stream's publication delay so it reads only windows Snowflake has finished publishing. Events and detections on a stream therefore run up to that delay behind. An hourly pass re-reads older windows to pick up rows published even later. Duplicate rows are removed.
* Every poll runs on the connected warehouse and appears in **Query History** with the query tag `cotool-log-ingest`.
* Query history stores each query's full text. That text can include secrets written into statements, for example credentials in `CREATE STAGE` or `ALTER USER`.

### Ingest-only access

To collect logs without giving agents access to your own data, give the role only:

* `USAGE` on the warehouse (step 2).
* The database roles for your streams (step 4).

Skip step 3, and do not grant `IMPORTED PRIVILEGES ON DATABASE SNOWFLAKE`.

With this role:

* **Log ingest works** for every stream whose database role is granted.
* **Agents cannot read your databases.** The role has no grants on them, so Snowflake hides them from discovery and rejects queries against them.
* **The environment map is empty.** The only database the role can see is `SNOWFLAKE`, which the map skips. Mapping still runs on connect and weekly, but it only lists metadata, so it does not resume the warehouse and samples no rows.
* **Agents can still query the audit views.** The Snowflake query and discovery tools stay available, and can read the `SNOWFLAKE.ACCOUNT_USAGE` views the granted database roles cover, including query text in `QUERY_HISTORY`. Most of this is the same data log ingest collects.

You cannot turn off agent access while keeping ingest: disabling the Snowflake integration also stops its log ingest.

## Troubleshooting

| Error mentions | What to fix |
| - | - |
| `390144`, or "JWT token is invalid" | The public key on the user does not match the private key, or the account or username is wrong. `DESC USER COTOOL_SVC` shows `RSA_PUBLIC_KEY_FP` to compare. |
| `390100`, or "Incorrect username or password" | The password is wrong, or the user is `TYPE = SERVICE`, which cannot use passwords. Switch to key-pair authentication. |
| "is not allowed to access Snowflake" | A network policy blocks Cotool. See [Network access](#network-access). |
| "is not granted to this user" | The role entered in Cotool is not granted to the user. `GRANT ROLE <role> TO USER <user>`, or clear the role field. |
| "does not exist, or ... lacks USAGE on it" on connect | The entered warehouse is wrong or the role cannot use it. `GRANT USAGE ON WAREHOUSE <warehouse> TO ROLE <role>`, or leave the warehouse blank. |
| "No warehouse is usable by ..." | The role cannot use any warehouse. Grant it `USAGE` on one (step 2). |
| `000606`, or "No active warehouse selected" | The pinned warehouse was dropped or its grant revoked. Fix the grant, or edit the integration and clear the warehouse. |
| `002003`, or "does not exist or not authorized" | The role lacks `USAGE` on the database or schema, or `SELECT` on the table or view (step 3). Snowflake hides objects a role cannot access. |
| "did not finish within N seconds" | The query scanned too much data. Filter on the table's event-time or clustering column, aggregate, or give the warehouse more capacity. |
| "could not reach Snowflake" | The account identifier is wrong, or the account only accepts PrivateLink connections. |


This documentation is built and hosted on [Mintlify](https://mintlify.com), a developer documentation platform.