Snowflake Integration

​

Overview

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.

​

Before you start

• 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

openssl genrsa 2048 | openssl pkcs8 -topk8 -inform PEM -out cotool_key.p8 -nocrypt
openssl rsa -in cotool_key.p8 -pubout -out cotool_key.pub

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;

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

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

​

Connect in Cotool

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

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

​

Network access

​

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.

• 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

• 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

• 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

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

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

​

Troubleshooting