Skip to main content

Snowflake Permissions

Creating the data source covers the connection form. This page covers the other half: what the Snowflake user on the other end of that form needs to be allowed to do.

Every grant below was derived from the queries DataDios actually runs and verified against a live Snowflake Enterprise account. Nothing here is a precaution "just in case".

The short version — for workload and cost extraction you need two Snowflake-supplied database roles and a warehouse. You do not need ACCOUNTADMIN, you do not need ORGADMIN, and you do not need GRANT IMPORTED PRIVILEGES ON DATABASE SNOWFLAKE.

1. What DataDios reads​

Workload extraction reads Snowflake's own bookkeeping views — your account's record of which queries ran, how long they took, and how much storage each database uses. These live in the shared SNOWFLAKE database and describe your account, not your business data.

What DataDios collectsSnowflake view
Queries that ran, and which tables they touchedACCOUNT_USAGE.QUERY_HISTORY, ACCOUNT_USAGE.ACCESS_HISTORY
Warehouse credits consumed per hourACCOUNT_USAGE.WAREHOUSE_METERING_HISTORY
Storage per databaseACCOUNT_USAGE.DATABASE_STORAGE_USAGE_HISTORY
Storage per account, per dayACCOUNT_USAGE.STORAGE_USAGE
Storage per tableACCOUNT_USAGE.TABLE_STORAGE_METRICS
Your negotiated $/credit (optional — see §5)ORGANIZATION_USAGE.RATE_SHEET_DAILY
With only the grants in §3, the connection cannot read a single row of your business data.

Reading table contents requires SELECT, which §6 covers separately and only if you want the features that need it.

2. Two things about the connection form​

Both are common causes of "it connects, but nothing comes back".

There is no role field, and DataDios never issues USE ROLE. Every query runs as the user's DEFAULT_ROLE. If DEFAULT_ROLE is unset, everything runs as PUBLIC and fails — and it fails looking like a missing grant rather than a missing default. Confirm with:

DESC USER DATADIOS;

Leaving Database blank means "discover them". DataDios runs SHOW DATABASES, which returns only the databases the role can see. With workload-only grants that list comes back empty and the data-source screen shows nothing to pick. If you want to browse databases, add the §6(a) grants.

3. The grants​

Run these as ACCOUNTADMIN. Granting SNOWFLAKE database roles requires it; nothing afterwards does.

USE ROLE ACCOUNTADMIN;

-- A dedicated role and user for DataDios.
CREATE ROLE IF NOT EXISTS DATADIOS_ROLE;
CREATE USER IF NOT EXISTS DATADIOS
    TYPE           = SERVICE      -- key-pair only; see Authentication
    RSA_PUBLIC_KEY = '<contents of rsa_key.pub, without the BEGIN/END lines>';

-- The two Snowflake-supplied database roles that open the views in §1.
GRANT DATABASE ROLE SNOWFLAKE.GOVERNANCE_VIEWER TO ROLE DATADIOS_ROLE;
GRANT DATABASE ROLE SNOWFLAKE.USAGE_VIEWER      TO ROLE DATADIOS_ROLE;

-- A warehouse to run the reads on.
GRANT USAGE ON WAREHOUSE DATADIOS_WH TO ROLE DATADIOS_ROLE;

GRANT ROLE DATADIOS_ROLE TO USER DATADIOS;

-- The role must be the user's default — DataDios never issues USE ROLE.
ALTER USER DATADIOS SET DEFAULT_ROLE = DATADIOS_ROLE, DEFAULT_WAREHOUSE = DATADIOS_WH;
A SERVICE user cannot sign in with a password

Snowflake allows only key-pair or OAuth authentication for TYPE = SERVICE. Generate the key pair before running the block above — see Authentication for the commands and for the password-based alternative.

Why not GRANT IMPORTED PRIVILEGES ON DATABASE SNOWFLAKE? That one statement hands over every view in the SNOWFLAKE database — login history, sessions, grants, masking and row-access policy definitions. The two database roles above cover exactly what extraction reads and stop there.

GRANT OPERATE ON WAREHOUSE is only needed if the warehouse has AUTO_RESUME = FALSE. With AUTO_RESUME = TRUE (the default), skip it.

Give DataDios its own warehouse​

Point DataDios at a small, dedicated warehouse so its cost is a separate line on your bill instead of being mixed into a shared one. The reads are metadata-sized — XSMALL is plenty.

CREATE WAREHOUSE IF NOT EXISTS DATADIOS_WH
    WAREHOUSE_SIZE      = XSMALL
    AUTO_SUSPEND        = 60      -- seconds; Snowflake still bills a 1-minute minimum per resume
    AUTO_RESUME         = TRUE
    INITIALLY_SUSPENDED = TRUE;

4. Verify it worked​

Run this as the DataDios user (or after USE ROLE DATADIOS_ROLE). Every line must return a count. Any does not exist or not authorized is a grant that did not land.

SELECT 'QUERY_HISTORY',              COUNT(*) FROM SNOWFLAKE.ACCOUNT_USAGE.QUERY_HISTORY
UNION ALL SELECT 'ACCESS_HISTORY',   COUNT(*) FROM SNOWFLAKE.ACCOUNT_USAGE.ACCESS_HISTORY
UNION ALL SELECT 'WAREHOUSE_METER',  COUNT(*) FROM SNOWFLAKE.ACCOUNT_USAGE.WAREHOUSE_METERING_HISTORY
UNION ALL SELECT 'DATABASE_STORAGE', COUNT(*) FROM SNOWFLAKE.ACCOUNT_USAGE.DATABASE_STORAGE_USAGE_HISTORY
UNION ALL SELECT 'STORAGE_USAGE',    COUNT(*) FROM SNOWFLAKE.ACCOUNT_USAGE.STORAGE_USAGE
UNION ALL SELECT 'TABLE_STORAGE',    COUNT(*) FROM SNOWFLAKE.ACCOUNT_USAGE.TABLE_STORAGE_METRICS;

ACCESS_HISTORY returning a count also tells you the account is Enterprise edition or higher — that view does not exist on Standard, and it decides which lineage route DataDios takes (§7).

Two more checks worth running once:

SHOW GRANTS TO ROLE DATADIOS_ROLE;   -- expect GOVERNANCE_VIEWER and USAGE_VIEWER in the list
DESC USER DATADIOS;                  -- expect DEFAULT_ROLE = DATADIOS_ROLE

5. Optional: your negotiated credit rate​

The SF Cost Details metric group prices compute using your negotiated rate, which lives in ORGANIZATION_USAGE.RATE_SHEET_DAILY. Opening it needs one more grant, issued from the account that holds ORGADMIN:

GRANT DATABASE ROLE SNOWFLAKE.ORGANIZATION_BILLING_VIEWER TO ROLE DATADIOS_ROLE;

You can safely skip this. Without it:

  • Everything else runs normally — this is the only metric group affected.
  • Compute is priced at Snowflake's published list price (around $2.20/credit at the time of writing — check Snowflake's current pricing) instead of your rate, so cost figures are directionally right but not exact.
  • Storage cost columns come out blank.
  • The SF Cost Details group reports a failure each run. Turn that group off for the data source to keep your logs clean.

The grant is read-only and covers billing rates only — no query text, no data.

If your rate sheet is billed in a currency other than USD, DataDios uses your own rate and

reports cost in that currency rather than substituting a USD list price. The currency is recorded in the extraction log.

6. Optional: features beyond workload extraction​

(a) Browsing databases in the UI — needed if you want the data-source screen to list databases:

GRANT USAGE ON DATABASE <db> TO ROLE DATADIOS_ROLE;
GRANT USAGE ON ALL SCHEMAS IN DATABASE <db> TO ROLE DATADIOS_ROLE;

(b) Metadata sync, profiling, SmartDiff — anything that reads real table contents. This is the point where the account stops being usage-metadata-only, so grant per schema rather than account-wide, and only where you want those features:

GRANT SELECT ON ALL TABLES    IN SCHEMA <db>.<schema> TO ROLE DATADIOS_ROLE;
GRANT SELECT ON FUTURE TABLES IN SCHEMA <db>.<schema> TO ROLE DATADIOS_ROLE;

(c) Stable table identity across renames — DataDios reads SNOWFLAKE.ACCOUNT_USAGE.TABLES to map each table to Snowflake's internal id. Without access it falls back to an internal identifier, so listings still work. This is a nice-to-have, not a requirement.

7. Query-to-table lineage: two routes, one much cheaper​

Linking a query to the tables it touched can happen two ways, and the difference in cost is large.

The cheap route — Enterprise edition and above. ACCESS_HISTORY already records the tables each query read, so lineage comes out of the same result set DataDios has already fetched — zero extra calls. GOVERNANCE_VIEWER covers it. This is the route you want.

The expensive route — Standard edition. ACCESS_HISTORY does not exist, so DataDios falls back to calling GET_QUERY_OPERATOR_STATS() once per query. In one of our test runs that was 5,614 extra round trips. That route also needs:

GRANT MONITOR ON WAREHOUSE <warehouse> TO ROLE DATADIOS_ROLE;

On Enterprise this MONITOR grant is unnecessary — if you granted it defensively, you can drop it.

DataDios picks the route per batch by checking whether ACCESS_HISTORY actually returned data, so there is nothing to configure. If the fallback is denied, it is recorded once per connection rather than logged as a failure for every query.

8. Authentication​

Key-pair authentication is the recommended option and the only one a TYPE = SERVICE user supports. Generate the pair, then attach the public key to the user:

openssl genrsa 2048 | openssl pkcs8 -topk8 -inform PEM -out rsa_key.p8 -nocrypt
openssl rsa -in rsa_key.p8 -pubout -out rsa_key.pub
ALTER USER DATADIOS SET RSA_PUBLIC_KEY = '<contents of rsa_key.pub, without the BEGIN/END lines>';

Upload rsa_key.p8 in the connection form — see Key Pair Authentication.

If you need password authentication, the user cannot be TYPE = SERVICE. Snowflake is phasing out single-factor password sign-in for TYPE = PERSON, so a password connection that works today can start failing on a policy deadline — and it fails as an authentication error, not a permission error. The type that keeps a password working and is exempt from MFA policy is LEGACY_SERVICE:

ALTER USER DATADIOS SET TYPE = LEGACY_SERVICE, PASSWORD = '<strong-password>';

Snowflake marks LEGACY_SERVICE as the transitional type and points at SERVICE with key-pair as the destination. Treat it as a way to get running, not as the end state.

Converting an existing TYPE = PERSON user to SERVICE removes password sign-in — set the public key first, or the connection breaks the moment you convert.

9. When something fails​

002003 (42S02): Object '...' does not exist or not authorized

Snowflake returns this identical message for both "you lack the grant" and "this object does not exist". Before hunting for a missing grant, confirm the object is real — run the same query as ACCOUNTADMIN. If it still fails, it is not a permissions problem.

Insufficient privileges to operate on query stats

This is the GET_QUERY_OPERATOR_STATS() fallback from §7. Either the account is Standard edition and needs GRANT MONITOR ON WAREHOUSE, or ACCESS_HISTORY is unreadable because GOVERNANCE_VIEWER is missing. The §4 verify block settles which.

Every metric group fails at once, right after setup

Almost always DEFAULT_ROLE. The connection succeeds — Snowflake lets you connect as PUBLIC — and then every query fails on authorization. DESC USER DATADIOS; settles it.

Nothing appears for the first hour

Expected. Snowflake publishes ACCOUNT_USAGE views with a latency of 45 minutes to 3 hours depending on the view, and DataDios deliberately checks in hourly rather than sooner so that it arrives after publication instead of paying for empty reads.