Privileges and GRANT Mechanics
A query runs only if the session's roles, taken together, hold USAGE on the warehouse, the database and the schema, plus the right privilege on the object itself.
The problem
Every GRANT has three parts: an action, an object and a recipient. GRANT SELECT ON TABLE orders TO ROLE analyst reads as "allow SELECT, on ORDERS, for ANALYST." That part is easy. What trips people up is that one query usually needs several grants, at several levels, before it can run.
The mental model
Objects are nested. A table sits in a schema, and the schema sits in a database. To reach the table, a role must be allowed into each container, and the privilege for that is USAGE. USAGE on a container allows no action on its contents. It only lets the role find names inside it. That lookup is called name resolution: for sales_db.finance.invoices Snowflake finds the database, then the schema, then the table, and needs USAGE at each container on the way. A query also needs compute, and running a query on a warehouse needs USAGE on that warehouse. Figure 3.5 shows the whole check for one statement.
WHAT THE STATEMENT NEEDS
STATEMENTSELECT * FROM sales_db.core.ordersrun by Alice on warehouse BI_WH
needs all four:
USAGE on warehouse BI_WH
USAGE on database SALES_DB
USAGE on schema CORE
SELECT on table ORDERS
WHAT ALICE’S SESSION HOLDS
ROLEANALYSTprimary
ROLEEUROPE_ANALYSTsecondary (ALL)
COMBINEDPrivilege setboth roles + every role they inherit
THE CHECKAll four in the set?
no: one is missing
Fails“does not exist or not authorized”
yes
Runson warehouse BI_WH
Worked example: Alice can't query INVOICES
Alice already reads everything in schema CORE through SALES_DB.READER. Thomas now creates a new schema, SALES_DB.FINANCE, loads an INVOICES table into it, and, working as DATA_ENGINEER (the table's owner), grants SELECT on the table to ANALYST. Alice opens her dashboard the next morning. Figure 3.6 shows where each object lives and which grants ANALYST holds.
Alice runs: SELECT * FROM sales_db.finance.invoices (warehouse BI_WH)
IsmaFrost account
ROLE · HOLDSANALYSTUSAGE on BI_WHSALES_DB.READERSELECT on INVOICES(nothing on FINANCE)
✓WAREHOUSEBI_WHcompute only, beside the data
✓1 · DATABASE SALES_DB
✓2 · SCHEMA CORE
✓3 · TABLEORDERSSELECT via READER
path open: USAGE comes from SALES_DB.READER
✗2 · SCHEMA FINANCE
no USAGE: lookup stops here
3 · TABLEINVOICESSELECT granted, never reached
Fix: GRANT USAGE ON SCHEMA sales_db.finance TO ROLE analyst
- Symptom.
SELECT * FROM sales_db.finance.invoices;fails with "Object 'SALES_DB.FINANCE.INVOICES' does not exist or not authorized." Snowflake uses one message for both cases on purpose, so an unauthorized user can't probe which objects exist. - Diagnose. Youness runs
SHOW GRANTS TO ROLE analyst;and finds USAGE on BI_WH, the database role SALES_DB.READER (which brings USAGE on SALES_DB and on CORE, nothing on FINANCE), SELECT on SALES_DB.FINANCE.INVOICES, and nothing on the schema FINANCE. He also runsSHOW GRANTS TO USER alice;to confirm she holds ANALYST. - Fix.
GRANT USAGE ON SCHEMA sales_db.finance TO ROLE analyst; - Result. Alice reruns the query and gets rows. Snowflake now resolves the database (USAGE), the schema (USAGE, newly granted) and the table (SELECT), and runs the query on BI_WH (USAGE). The table grant didn't change. The path to it opened.
- Prevent the next one. Thomas's pipeline will create more tables in FINANCE, so Youness adds a future grant:
GRANT SELECT ON FUTURE TABLES IN SCHEMA sales_db.finance TO ROLE analyst;
How it works: existing objects versus future objects
A GRANT normally applies to objects that exist when it runs. ON ALL TABLES IN SCHEMA is a shortcut that grants on every table present at that moment. It is still a one-time snapshot. ON FUTURE TABLES IN SCHEMA stores a rule, and Snowflake applies it each time a new table is created in that schema. You usually need both: ALL for the past, FUTURE for what comes next.
Two precise rules. Schema-level future grants override database-level ones for the same object type; the two never combine. And future grants can't go to users through UBAC.
Go deeper · a worked example of the override
A database-level rule gives SELECT on future tables in SALES_DB to ANALYST. A schema-level rule in FINANCE gives INSERT on future tables to DATA_ENGINEER. A new table in FINANCE gets only the INSERT grant. ANALYST's database-level rule is ignored in that schema.
The grants you'll write
| Kind | Example | Effect |
|---|---|---|
| Direct | GRANT SELECT ON TABLE core. | One object, today |
| Bulk | GRANT SELECT ON ALL TABLES IN SCHEMA core TO ROLE analyst | Every table that exists now |
| Future | GRANT SELECT ON FUTURE TABLES IN SCHEMA core TO ROLE analyst | Every table created later |
| Role to role | GRANT ROLE data_ | Hierarchy: the parent inherits |
| Role to user | GRANT ROLE analyst TO USER alice | Membership: Alice can use the role |
| Ownership transfer | GRANT OWNERSHIP ON TABLE t TO ROLE data_ | Control moves, existing grants are kept |
Privileges worth knowing by name
| Object | Privilege | What it allows |
|---|---|---|
| Warehouse | USAGE | Run queries on it |
| Warehouse | OPERATE | Start, suspend, resume, and abort its queries |
| Warehouse | MODIFY | Change properties such as size. Assigning the warehouse to a resource monitor (a credit limit) also needs it. Creating the monitor itself is ACCOUNTADMIN's job |
| Warehouse | MONITOR | See its queries and usage |
| Database, schema | USAGE | Enter the container and resolve names |
| Schema | CREATE TABLE, CREATE VIEW, … | Create that object type inside it |
| Table | SELECT, INSERT, UPDATE, DELETE, TRUNCATE | The matching statement |
| Any object | OWNERSHIP | Full control, including grants on it |
| Account (global) | MANAGE GRANTS, CREATE DATABASE, CREATE WAREHOUSE, CREATE INTEGRATION, … | Account-level powers. Account roles only, never database roles |
GRANT ALL PRIVILEGES grants every privilege that applies to the object type except OWNERSHIP, which only moves through GRANT OWNERSHIP.
Reading the grant graph
SHOW GRANTS TO ROLE analyst; -- privileges and roles ANALYST holdsSHOW GRANTS ON TABLE sales_db. core. orders; -- who holds what on this table SHOW GRANTS TO USER alice; -- roles granted to AliceSHOW GRANTS OF ROLE analyst; -- users and roles ANALYST is granted toSHOW FUTURE GRANTS IN SCHEMA sales_db. core;
The small words are tested exactly. TO a role lists what it holds. ON an object lists who holds privileges on it. OF a role lists who has been given that role. "SHOW GRANTS FOR ROLE" doesn't exist.
Managed access schemas: who may grant
In a regular schema, each object's owner grants access to it, so the power to grant is spread across whoever happened to create things. In a managed access schema, creators still own their objects but can't grant on them. Only the schema owner, or a role with MANAGE GRANTS, can grant privileges on objects in the schema. That includes future grants. Owners keep everything else: they can still alter and drop what they own. IsmaFrost uses this for HR_DB.CORE. SYSADMIN owns the schema, Thomas's pipeline (DATA_ENGINEER) creates the tables, and Youness, working as SECURITYADMIN, decides who reads them.
REGULAR SCHEMA SALES_DB.FINANCE
ROLEDATA_
✓runs GRANT SELECT
ROLEANALYSTgets SELECT on INVOICES
MANAGED ACCESS SCHEMA HR_DB.CORE
SYSTEM ROLESYSADMINowns the schema
ROLEDATA_
✗GRANT refused
SYSTEM ROLESECURITY
✓runs GRANT SELECT
ROLEHR_ADMINgets SELECT on EMPLOYEES
CREATE SCHEMA hr_db.core WITH MANAGED ACCESS;ALTER SCHEMA sales_db. core ENABLE MANAGED ACCESS; -- convert an existing schema
Limits and traps: delegation and revocation
WITH GRANT OPTION lets the recipient pass a privilege on to other roles. Revoking it later uses RESTRICT by default, which fails while dependent grants exist; CASCADE removes them too. Most teams never use GRANT OPTION, because it spreads the power to grant in exactly the way managed access schemas try to prevent.
Go deeper · RESTRICT and CASCADE in SQL
GRANT SELECT ON TABLE sales_db. finance. invoices TO ROLE analyst WITH GRANT OPTION; -- ANALYST passes it on to FINANCE_VIEWER, a small role for the finance team (not on the grant sheet): -- GRANT SELECT ON TABLE sales_db. finance. invoices TO ROLE finance_ viewer; REVOKE SELECT ON TABLE sales_db. finance. invoices FROM ROLE analyst; -- RESTRICT (default): fails REVOKE SELECT ON TABLE sales_db. finance. invoices FROM ROLE analyst CASCADE; -- removes both grants
Which one to use
| You need… | Use |
|---|---|
| Access to the tables that exist today | ON ALL TABLES IN SCHEMA |
| Access to tables a pipeline will create later | ON FUTURE TABLES IN SCHEMA (plus ALL for the existing ones) |
| Grant decisions made by one team, not by each creator | A managed access schema |
| A parent role that can manage a child role's objects | GRANT ROLE child TO ROLE parent |
- One SELECT = USAGE on warehouse + USAGE on database + USAGE on schema + SELECT on the object.
- "Does not exist or not authorized" usually means a missing container USAGE.
- ALL = objects that exist now. FUTURE = objects created later. Schema-level future grants override database-level ones.
- Managed access: only the schema owner or MANAGE GRANTS can grant on contained objects.

