Course contents

SnowPro Core COF-C03 Study GuidePart 3: Access control and securityPrivileges and GRANT Mechanics

Privileges and GRANT Mechanics

Privileges and GRANT Mechanics

In short

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.

How Snowflake authorizes one statement. Left: Alice's SELECT on sales_db.core.orders and the four privileges it needs. Right: her active roles, ANALYST as primary and EUROPE_ANALYST as secondary, combined with everything they inherit into one privilege set. Snowflake checks whether all four needed privileges are in the set. No: it fails with does not exist or not authorized. Yes: the query runs on BI_WH.WHAT THE STATEMENT NEEDSWHAT ALICE’S SESSION HOLDSSTATEMENTSELECT * FROM sales_db.core.ordersrun by Alice on warehouse BI_WHneeds all four:USAGE on warehouse BI_WHUSAGE on database SALES_DBUSAGE on schema CORESELECT on table ORDERSROLEANALYSTprimaryROLEEUROPE_ANALYSTsecondary (ALL)COMBINEDPrivilege setboth roles + every rolethey inheritTHE CHECKAll four in the set?Fails“does not exist ornot authorized”Runson warehouse BI_WHno: one is missingyes
  • 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

Figure 3.5 How Snowflake authorizes one statement. It combines the privileges of every active role (primary and secondary) and of the roles they inherit, then compares that set with what the statement needs. All four privileges present: the query runs. One missing: it fails.

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.

Where things live, and where Alice's query stops. The account holds account-level objects: the role ANALYST and the warehouse BI_WH, which is compute and contains no data. Data lives in nested containers: database SALES_DB holds schemas CORE and FINANCE, and each schema holds tables. ANALYST holds USAGE on BI_WH, USAGE on SALES_DB and CORE through SALES_DB.READER, and SELECT on FINANCE.INVOICES, but no USAGE on schema FINANCE. Name lookup goes database, then schema, then table, and stops at FINANCE, so the SELECT on INVOICES is never reached.IsmaFrost account1 · DATABASE SALES_DB2 · SCHEMA CORE2 · SCHEMA FINANCEAlice runs: SELECT * FROM sales_db.finance.invoices (warehouse BI_WH)ROLE · HOLDSANALYSTUSAGE on BI_WHSALES_DB.READERSELECT on INVOICES(nothing on FINANCE)WAREHOUSEBI_WHcompute only,beside the data✓✓✓3 · TABLEORDERSSELECT via READER✓path open: USAGE comesfrom SALES_DB.READER✗no USAGE: lookup stops here3 · TABLEINVOICESSELECT granted,never reachedFix: GRANT USAGE ON SCHEMA sales_db.finance TO ROLE analyst

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

Figure 3.6 Where things live. Roles and warehouses are account-level objects, and a warehouse is compute: it contains no database. Data sits in nested containers, database then schema then table, and name resolution needs USAGE at each one. ANALYST reaches CORE through SALES_DB.READER but holds nothing on FINANCE, so the lookup stops before the SELECT on INVOICES.
  1. 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.
  2. 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 runs SHOW GRANTS TO USER alice; to confirm she holds ANALYST.
  3. Fix. GRANT USAGE ON SCHEMA sales_db.finance TO ROLE analyst;
  4. 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.
  5. 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.

One schema over time. At 10:00 Youness runs both grants for ANALYST on schema FINANCE. ON ALL TABLES gives SELECT on the 40 tables that exist at 10:00, then stops. ON FUTURE TABLES stores a rule from 10:00 on, so table 41, created at 14:00, and table 42, created tomorrow, each get SELECT the moment they are created.TABLES IN FINANCEON ALL TABLESone-time snapshotON FUTURE TABLESstored ruleTables 1–40exist at 10:00Table 41created 14:00Table 42created tomorrowSELECT on 1–40, oncerule in force from 10:0009:0010:0014:00TOMORROWapplied at CREATEapplied at CREATE One schema over time. At 10:00 Youness runs both grants for ANALYST on schema FINANCE. ON ALL TABLES gives SELECT on the 40 tables that exist at 10:00, then stops. ON FUTURE TABLES stores a rule from 10:00 on, so table 41, created at 14:00, and table 42, created tomorrow, each get SELECT the moment they are created.TABLES IN FINANCEON FUTURE TABLESstored rulerule in force from 10:0009:0010:0014:00TOMORROWSELECT on 1–40,onceON ALL TABLESone-time snapshotapplied at CREATEapplied at CREATETables 1–40exist at 10:00Table 41created 14:00Table 42created tomorrow
Figure 3.7 One schema over time. At 10:00, ALL grants SELECT on the 40 existing tables and stops. The FUTURE rule stored at the same moment gives SELECT to table 41 and table 42 when each is created.

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

KindExampleEffect
DirectGRANT SELECT ON TABLE core.orders TO ROLE analystOne object, today
BulkGRANT SELECT ON ALL TABLES IN SCHEMA core TO ROLE analystEvery table that exists now
FutureGRANT SELECT ON FUTURE TABLES IN SCHEMA core TO ROLE analystEvery table created later
Role to roleGRANT ROLE data_engineer TO ROLE sysadminHierarchy: the parent inherits
Role to userGRANT ROLE analyst TO USER aliceMembership: Alice can use the role
Ownership transferGRANT OWNERSHIP ON TABLE t TO ROLE data_engineer COPY CURRENT GRANTSControl moves, existing grants are kept

Privileges worth knowing by name

ObjectPrivilegeWhat it allows
WarehouseUSAGERun queries on it
WarehouseOPERATEStart, suspend, resume, and abort its queries
WarehouseMODIFYChange 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
WarehouseMONITORSee its queries and usage
Database, schemaUSAGEEnter the container and resolve names
SchemaCREATE TABLE, CREATE VIEW, …Create that object type inside it
TableSELECT, INSERT, UPDATE, DELETE, TRUNCATEThe matching statement
Any objectOWNERSHIPFull 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 holds
SHOW GRANTS ON TABLE sales_db.core.orders;  -- who holds what on this table
SHOW GRANTS TO USER alice;                  -- roles granted to Alice
SHOW GRANTS OF ROLE analyst;                -- users and roles ANALYST is granted to
SHOW 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.

The same kind of GRANT in two schemas. Regular schema SALES_DB.FINANCE: DATA_ENGINEER owns INVOICES and its GRANT SELECT to ANALYST succeeds. Managed access schema HR_DB.CORE, owned by SYSADMIN: DATA_ENGINEER still owns EMPLOYEES, but its GRANT SELECT to HR_ADMIN is refused. Only the schema owner SYSADMIN or a role with MANAGE GRANTS, here SECURITYADMIN used by Youness, can grant on objects in the schema.REGULAR SCHEMA SALES_DB.FINANCEMANAGED ACCESS SCHEMA HR_DB.COREROLEDATA_ENGINEERowns table INVOICESROLEANALYSTgets SELECT on INVOICES✓SYSTEM ROLESYSADMINowns the schemaSYSTEM ROLESECURITYADMINMANAGE GRANTS(Youness)ROLEDATA_ENGINEERstill owns EMPLOYEESROLEHR_ADMINgets SELECT on EMPLOYEES✗GRANT refused✓runs GRANT SELECTruns GRANTSELECT

REGULAR SCHEMA SALES_DB.FINANCE

ROLEDATA_ENGINEERowns table INVOICES

✓runs GRANT SELECT

ROLEANALYSTgets SELECT on INVOICES

MANAGED ACCESS SCHEMA HR_DB.CORE

SYSTEM ROLESYSADMINowns the schema

ROLEDATA_ENGINEERstill owns EMPLOYEES

✗GRANT refused

SYSTEM ROLESECURITYADMINMANAGE GRANTS (Youness)

✓runs GRANT SELECT

ROLEHR_ADMINgets SELECT on EMPLOYEES

Figure 3.8 The same kind of GRANT in two schemas. In the regular schema the table's owner can grant. In the managed access schema the owner's GRANT is refused, and only the schema owner (SYSADMIN) or a MANAGE GRANTS holder (SECURITYADMIN) can grant.
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 todayON ALL TABLES IN SCHEMA
Access to tables a pipeline will create laterON FUTURE TABLES IN SCHEMA (plus ALL for the existing ones)
Grant decisions made by one team, not by each creatorA managed access schema
A parent role that can manage a child role's objectsGRANT ROLE child TO ROLE parent
Exam takeaway
  • 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.