Course contents

SnowPro Core COF-C03 Study GuidePart 3: Access control and securityThe Access Model

The Access Model

Part 3

Access Control & Security

This part explains who can do what in a Snowflake account, and how Snowflake protects data even after someone is allowed in. It is about a fifth of COF-C03, and most questions have one exact answer. You need four skills: follow the direction of a grant, list every privilege one query needs, tell authentication apart from authorization, and pick the right protection for a threat (network, rows, columns, or a view's definition).

The IsmaFrost cast and grant sheet

Every example in Part 3 follows this sheet. When an example needs an extra grant, it says so first.

AliceBI analyst · ANALYST, EUROPE_ANALYST
ThomasData engineer · DATA_ENGINEER, ANALYST
AmineContractor from an outside agency · ANALYST
YounessSecurity admin · SECURITYADMIN, SYSADMIN, ACCOUNTADMIN
SafiaHR · HR_ADMIN
RoleWhat it holdsGranted to
ANALYSTSALES_DB.READER (USAGE on SALES_DB and CORE, SELECT on CORE's tables). USAGE on BI_WH. USAGE on HR_DB and HR_DB.CORE, for one HR viewAlice, Thomas, Amine · SYSADMIN
DATA_ENGINEERUSAGE on SALES_DB and HR_DB. Owns the schemas it builds in SALES_DB (RAW, CORE, FINANCE) and their tables. Creates and owns the tables in HR_DB.CORE. USAGE on ETL_WHThomas, the service user svc_etl · SYSADMIN
EUROPE_ANALYSTNothing. The row access policy on SALES looks for itAlice · SYSADMIN
HR_ADMINUSAGE on HR_DB, HR_DB.CORE and BI_WH, SELECT on EMPLOYEES. The HR masking policy shows real values only to itSafia only, not SYSADMIN

SYSADMIN owns SALES_DB, HR_DB and both warehouses. Each example query below holds all four privileges a SELECT needs, unless the example says what is missing.

Go deeper · the grant sheet as SQL
USE ROLE securityadmin;   -- MANAGE GRANTS: it can grant on objects SYSADMIN owns
-- ANALYST (its bundle SALES_DB.READER is built in the Custom & Database Roles section)
GRANT USAGE ON WAREHOUSE bi_wh TO ROLE analyst;
GRANT USAGE ON DATABASE hr_db TO ROLE analyst;
GRANT USAGE ON SCHEMA hr_db.core TO ROLE analyst;    -- for the view TEAM_DIRECTORY
-- DATA_ENGINEER
GRANT USAGE, CREATE SCHEMA ON DATABASE sales_db TO ROLE data_engineer;
GRANT USAGE ON DATABASE hr_db TO ROLE data_engineer;
GRANT USAGE, CREATE TABLE ON SCHEMA hr_db.core TO ROLE data_engineer;
GRANT USAGE ON WAREHOUSE etl_wh TO ROLE data_engineer;
-- HR_ADMIN
GRANT USAGE ON DATABASE hr_db TO ROLE hr_admin;
GRANT USAGE ON SCHEMA hr_db.core TO ROLE hr_admin;
GRANT USAGE ON WAREHOUSE bi_wh TO ROLE hr_admin;
GRANT SELECT ON TABLE hr_db.core.employees TO ROLE hr_admin;

Eleven words this part relies on

Read them once now. Every later page uses them with exactly these meanings.

UserA login identity: a person such as Alice, or a program such as the ETL pipeline.
RoleA named container of privileges. Users act through roles.
PrivilegeOne allowed action on one object, for example SELECT on table ORDERS.
Securable objectAnything access can be controlled on: warehouse, database, schema, table, view, stage, function.
GrantThe SQL action that gives a privilege to a role, or a role to a user or to another role.
OwnershipThe OWNERSHIP privilege. Every object has exactly one owning role, which controls it fully.
InheritanceWhen role A is granted to role B, A is the child and B the parent. B can also do everything A can do.
AuthenticationProving who you are when you connect. Done once per session.
AuthorizationChecking whether the session's roles may run a statement. Done for every statement.
PolicyA rule object Snowflake applies automatically once it's attached: network, authentication, password, session, masking, row access. Most live in a schema; network policies are account-level objects.
Security integrationAn account-level object that connects Snowflake to an outside identity service: Okta or Entra ID for SSO, an OAuth link for a BI tool such as Tableau, or SCIM provisioning. Created by ACCOUNTADMIN.

Role-Based Access Control

The Access Model

In short

Privileges go to roles, roles go to users, and a role granted to another role passes all its privileges upward.

The problem

IsmaFrost has about forty people and four hundred tables. Granting each person each table would mean thousands of decisions that nobody could audit. Snowflake groups them so that access follows jobs, not individuals.

The mental model

Snowflake combines two models. With role-based access control (RBAC), privileges go to roles and roles go to users. With discretionary access control (DAC), each object's owner decides who gets privileges on it, through the OWNERSHIP privilege. A small exception, user-based access control (UBAC), lets an owner grant some privileges straight to a user (end of this page).

Learn one idea first. Grants are written in one direction and privileges are inherited in the other. GRANT ROLE analyst TO ROLE sysadmin makes ANALYST a child of SYSADMIN. SYSADMIN can then do everything ANALYST can do. ANALYST gains nothing from SYSADMIN.

Grant direction and inheritance at IsmaFrost. Every arrow points from what is given to who receives it. SELECT on ORDERS is granted to ANALYST. OWNERSHIP of ORDERS is held by DATA_ENGINEER, the role that created the table. ANALYST and DATA_ENGINEER are both granted to SYSADMIN, so SYSADMIN inherits both privileges. ANALYST is granted to Alice and to Thomas. DATA_ENGINEER is granted to Thomas.PARENT ROLEROLESUSERSSYSTEM ROLESYSADMINparent roleRESULTWhat SYSADMIN inheritsSELECT on ORDERSOWNERSHIP of ORDERSPRIVILEGESELECTon table ORDERSROLEANALYSTaccount roleROLEDATA_ENGINEERaccount rolePRIVILEGEOWNERSHIPof table ORDERSUSERAlicecan use ANALYSTUSERThomasDATA_ENGINEER + ANALYSTGRANT ROLE analystTO ROLE sysadminGRANT ROLE data_engineerTO ROLE sysadminRule for every Part 3 figure: a grant arrow points from what is given to who receives it.granted toownerTO USER aliceTO USER thomasTO USER thomas Grant direction and inheritance at IsmaFrost. Every arrow points from what is given to who receives it. SELECT on ORDERS is granted to ANALYST. OWNERSHIP of ORDERS is held by DATA_ENGINEER, the role that created the table. ANALYST and DATA_ENGINEER are both granted to SYSADMIN, so SYSADMIN inherits both privileges. ANALYST is granted to Alice and to Thomas. DATA_ENGINEER is granted to Thomas.PARENT ROLERESULTWhat SYSADMIN inheritsSELECT on ORDERSOWNERSHIP of ORDERSSYSTEM ROLESYSADMINparent roleROLESPRIVILEGESELECTon table ORDERSPRIVILEGEOWNERSHIPof table ORDERSROLEANALYSTaccount roleROLEDATA_ENGINEERaccount rolegrantedtoownerGRANT ROLEanalystTO ROLEsysadminGRANT ROLEdata_engineerTO ROLEsysadminUSERSUSERAlicecan use ANALYSTUSERThomasDATA_ENGINEER +ANALYSTTO USERaliceTO USERthomasTO USERthomasRule for every Part 3 figure: a grantarrow points from what is given towho receives it.
Figure 3.1 Every arrow is a grant, pointing from what is given to who receives it. ANALYST and DATA_ENGINEER are granted to SYSADMIN, so SYSADMIN inherits SELECT on ORDERS and OWNERSHIP of ORDERS. Alice can use only ANALYST; Thomas can use both of his roles.

How it works in one session

Read Figure 3.1 as Alice's day. Her session activates ANALYST, which holds SELECT on ORDERS, so she can read the table. She can't drop it: dropping needs OWNERSHIP, which belongs to DATA_ENGINEER. SYSADMIN can drop it, because DATA_ENGINEER is granted to SYSADMIN. Without that one grant, SYSADMIN can't even see Thomas's tables. It is the most common support ticket in this domain (System-Defined Roles section).

A session has one primary role, set with USE ROLE and returned by CURRENT_ROLE(). It can also have secondary roles: other roles granted to the same user, active at the same time. Statements on existing objects draw on every active role. That covers queries and DML (INSERT, UPDATE, DELETE). CREATE uses the primary role only, and that role owns the new object. Every object needs exactly one owner, so Snowflake takes the role you chose explicitly. Figure 3.2 shows the rule in Thomas's session.

Thomas's session: DATA_ENGINEER is his primary role, ANALYST is active as a secondary role. Three statements. The session warehouse is BI_WH. ALTER TABLE on raw.staging runs on DATA_ENGINEER alone and needs no warehouse. SELECT on core.orders runs because DATA_ENGINEER holds USAGE on SALES_DB and owns CORE and ORDERS, and ANALYST holds USAGE on BI_WH: two roles authorize one statement together. CREATE TABLE is checked against the primary role only, so DATA_ENGINEER owns the new table and ANALYST is not consulted.STATEMENT THOMAS RUNSsession warehouse: BI_WHDATA_ENGINEERprimary roleANALYSTsecondary roleOUTCOMESTATEMENTALTER TABLEraw.staging …owns RAWand STAGING✓nothingneeded hereRunsmetadata only,no warehouseSTATEMENTSELECT * FROMcore.ordersUSAGE on SALES_DB,owns CORE, ORDERS✓USAGE on BI_WH,the session warehouse✓Runstwo roles,one statementSTATEMENTCREATE TABLEcore.t (…)owns schemaCORE✓not consultedfor CREATECreatedowned byDATA_ENGINEERStatements on existing objects: every active role contributes.CREATE: the primary role only, and that role becomes the owner.
STATEMENT THOMAS RUNS
session warehouse: BI_WH
DATA_ENGINEER
primary role
ANALYST
secondary role
OUTCOME
ALTER TABLE raw.staging …✓owns RAW and STAGINGnothing needed hereRuns metadata only, no warehouse
SELECT * FROM core.orders✓USAGE on SALES_DB, owns CORE, ORDERS✓USAGE on BI_WH, the session warehouseRuns two roles, one statement
CREATE TABLE core.t (…)✓owns schema COREnot consulted for CREATECreated owned by DATA_ENGINEER

Statements on existing objects: every active role contributes. CREATE: the primary role only, and that role becomes the owner.

Figure 3.2 Thomas's session on warehouse BI_WH, with DATA_ENGINEER as primary role and ANALYST as secondary. On existing objects every active role contributes, even within one statement. CREATE is checked against the primary role only, and that role owns the result.
USE ROLE data_engineer;                   -- primary role
USE SECONDARY ROLES ALL;                  -- every other role granted to this user
USE SECONDARY ROLES analyst;              -- or a specific list of roles
USE SECONDARY ROLES NONE;                 -- primary role only
SELECT CURRENT_ROLE(), CURRENT_SECONDARY_ROLES();

USE SECONDARY ROLES accepts ALL, NONE or a list of role names. New users start with ALL.

Go deeper · older material and the user default

USE SECONDARY ROLES accepts ALL, NONE or a list of role names. Older material lists only ALL and NONE.

The starting value comes from the user property DEFAULT_SECONDARY_ROLES (User Management section). Snowflake ships changes to existing behavior in numbered bundles. Since bundle 2024_08, new users get DEFAULT_SECONDARY_ROLES = ('ALL').

Worked example: who owns what

The role that creates an object owns it. The owner can grant on it, alter it, drop it, and hand it over with GRANT OWNERSHIP. Altering or dropping a table needs OWNERSHIP of it, and GRANT ALL PRIVILEGES never includes OWNERSHIP.

Situation at IsmaFrostOwner afterwardsWhat happens to grants
DATA_ENGINEER runs CREATE TABLE ordersDATA_ENGINEERNone yet. The owner decides them.
SYSADMIN runs DROP ROLE etl_role, an old pipeline roleSYSADMIN, the role that ran the DROP, even if ETL_ROLE was never granted to itEvery grant that names ETL_ROLE as grantor or grantee is revoked. ETL_ROLE had granted SELECT on its table to ANALYST, so ANALYST loses it.
SYSADMIN runs CREATE TABLE orders_test CLONE ordersSYSADMIN, no matter who owned the sourceNone of the source's grants, unless the statement adds COPY GRANTS (all except OWNERSHIP)
Someone clones a whole database or schemaThe cloning roleChild objects keep their grants. The new container starts with none.

To keep ANALYST's access through the drop, move ETL_ROLE's objects first with GRANT OWNERSHIP … TO ROLE data_engineer COPY CURRENT GRANTS. The new owner becomes the grantor of the copied grants, so they survive.

Go deeper · COPY GRANTS, COPY CURRENT GRANTS and empty containers

COPY CURRENT GRANTS is an option of GRANT OWNERSHIP: the object changes owner and keeps the grants other roles hold. REVOKE CURRENT GRANTS removes them during the move instead. COPY GRANTS is an option of CREATE … CLONE: it copies the source's grants onto the clone. Same purpose, different statements.

A cloned container starts without grants because it is a new object, and new objects start with only their owner. Its children are copies of objects that already had grants, so Snowflake carries those over.

Limits and traps: UBAC

Thomas built sales_db.core.feedback_scratch last week. Amine reaches CORE through ANALYST, but the table is newer than READER's ALL grant, so he can't read it. Thomas, as the owner, runs GRANT SELECT ON TABLE sales_db.core.feedback_scratch TO USER amine and revokes it next Friday. Snowflake keeps UBAC small on purpose:

  • A grant to a user works only when the session has all secondary roles active. With NONE or a partial list, it does nothing. (The 2026_08 behavior change, not yet enabled by default, makes user grants apply in every session.)
  • Future, CREATE and OWNERSHIP privileges can't go to users.
  • Standing access for a team still belongs on a role.
Exam trap

"Privileges can only be granted to roles" is false, because UBAC exists. "The recommended model for governed production access is RBAC" is true. Check whether the question asks what is possible or what is recommended.

Exam takeaway
  • Grants are written child → parent. The parent inherits. Nothing flows down.
  • One primary role per session. Secondary roles add privileges on existing objects, never for CREATE: one object, one owner.
  • DROP ROLE: the dropping role owns the objects, and every grant naming the dropped role, as grantor or grantee, is revoked.
  • A clone belongs to the cloner and carries no grants without COPY GRANTS.
  • UBAC exists, needs all secondary roles active (until the 2026_08 behavior change is enabled), and allows no future, CREATE or OWNERSHIP grants.
Go deeper · why UBAC needs all secondary roles

Snowflake states the rule without a reason. One way to hold it: a user grant belongs to no role, so Snowflake counts it only when the session runs with everything the user holds. Snowflake is changing this: with the 2026_08 behavior change enabled, user grants apply in every session.