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).
Your route through Part 3
Each card opens a section. The running example is the same everywhere: IsmaFrost, a retailer with five people and the roles below.
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.
| Role | What it holds | Granted to |
|---|---|---|
| ANALYST | SALES_ | Alice, Thomas, Amine · SYSADMIN |
| DATA_ | USAGE on SALES_ | Thomas, the service user svc_ |
| EUROPE_ | Nothing. The row access policy on SALES looks for it | Alice · SYSADMIN |
| HR_ | USAGE on HR_ | Safia 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_ADMINGRANT 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.
Role-Based Access Control
The Access Model
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.
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.
| STATEMENT THOMAS RUNS session warehouse: BI_WH | DATA_ENGINEER primary role | ANALYST secondary role | OUTCOME |
|---|---|---|---|
| ALTER TABLE raw.staging … | ✓owns RAW and STAGING | nothing needed here | Runs metadata only, no warehouse |
| SELECT * FROM core.orders | ✓USAGE on SALES_DB, owns CORE, ORDERS | ✓USAGE on BI_WH, the session warehouse | Runs two roles, one statement |
| CREATE TABLE core.t (…) | ✓owns schema CORE | not consulted for CREATE | Created owned by DATA_ENGINEER |
Statements on existing objects: every active role contributes. CREATE: the primary role only, and that role becomes the owner.
USE ROLE data_engineer; -- primary role USE SECONDARY ROLES ALL; -- every other role granted to this userUSE SECONDARY ROLES analyst; -- or a specific list of rolesUSE SECONDARY ROLES NONE; -- primary role onlySELECT 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 IsmaFrost | Owner afterwards | What happens to grants |
|---|---|---|
| DATA_ | DATA_ | None yet. The owner decides them. |
| SYSADMIN runs DROP ROLE etl_ | SYSADMIN, the role that ran the DROP, even if ETL_ | Every grant that names ETL_ |
| SYSADMIN runs CREATE TABLE orders_ | SYSADMIN, no matter who owned the source | None of the source's grants, unless the statement adds COPY GRANTS (all except OWNERSHIP) |
| Someone clones a whole database or schema | The cloning role | Child 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.
"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.
- 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.

