Roles, Grants, and Not Everyone an ACCOUNTADMIN

The trial hands you ACCOUNTADMIN and it's tempting to never leave. Here's Snowflake's role model, the system roles, the functional/access-role pattern, and the grants that keep access sane once it's not just you.

When you sign up, Snowflake makes you an ACCOUNTADMIN — the role that can do everything, including delete everything and read every byte in the account. For the first week it’s just you, so you do all your work as ACCOUNTADMIN and never think about it. That habit is a time bomb. The day a second person joins, or the day a query you didn’t write drops a table you cared about, you’ll wish access had been shaped from the start. It’s far easier to build the shape early than to retrofit it onto a warehouse full of objects that all belong to the wrong owner.

The good news is that Snowflake’s access model is small. Once the pieces click, you can lay down a sane structure in an afternoon and never fight it again.

The model: privileges, roles, users

Snowflake’s rule is simple and rigid: privileges are never granted to users. A privilege — SELECT on a table, CREATE TABLE in a schema, USAGE on a warehouse — is granted to a role. Roles are then granted to users, and a user activates a role to act with its privileges. A user can hold several roles but works as one primary role at a time, chosen with USE ROLE. (We’ll qualify that “one at a time” claim shortly — secondary roles bend it — but start by internalizing the strict version, because that’s the mental model that keeps you out of trouble.)

The move that makes this scale is that roles can be granted to other roles. That builds a hierarchy: if you grant role analyst to role sysadmin, then anyone acting as sysadmin inherits everything analyst can do. Privileges roll upward. Design the hierarchy well and a senior role automatically has everything its juniors have, without anyone re-granting a thing.

One wrinkle worth naming before it bites you: Snowflake case-folds unquoted identifiers to uppercase. CREATE ROLE analyst creates a role named ANALYST; USE ROLE Analyst finds it fine because that folds to ANALYST too. The only way to get a lowercase or mixed-case role name is to double-quote it everywhere (CREATE ROLE "analyst"), which then forces you to quote it in every grant forever. Don’t. Let identifiers fold, write them however reads best, and know that the catalog stores them upper-cased.

The system roles you’re handed

Every account starts with a fixed set of built-in roles, arranged in a hierarchy:

  • ORGADMIN — the organization level, above the account. It creates and manages accounts within your Snowflake organization and sees org-wide billing. Most people never touch it; it isn’t part of the day-to-day account tree. We come back to it when we discuss multi-account setups (chapter 20).
  • ACCOUNTADMIN — the top of the account. Billing, account-wide settings, and everything below it. Dangerous enough that it should be held by very few people and used only when a task genuinely requires it.
  • SECURITYADMIN — manages users, roles, and grants account-wide. Crucially it holds the MANAGE GRANTS privilege globally, which lets it grant or revoke any privilege on any object regardless of who owns it. This is the role that hands out access.
  • USERADMIN — creates and manages users and roles via the CREATE USER and CREATE ROLE privileges. It’s a deliberately contained slice of SECURITYADMIN’s job: USERADMIN can make people and roles, and can grant a role it owns, but it does not hold MANAGE GRANTS, so it can’t hand out privileges on objects it doesn’t own. USERADMIN is a child of SECURITYADMIN, so SECURITYADMIN inherits it.
  • SYSADMIN — creates and owns warehouses, databases, schemas, and the objects inside them. This is where your data infrastructure should live.
  • PUBLIC — granted to everyone automatically. Anything granted to PUBLIC is visible to the whole account, so grant to it sparingly and deliberately.

The distinction between SECURITYADMIN and USERADMIN trips up newcomers, so make it concrete: USERADMIN is the HR department (it creates the people and the job titles); SECURITYADMIN is the badge office (it decides which doors each title opens). In a small shop one person wears both hats, but keeping the privileges separate means you can later delegate user creation to a helpdesk without also handing them the power to grant themselves SELECT on payroll.

The relationship that matters: SYSADMIN and SECURITYADMIN both roll up into ACCOUNTADMIN, so an account admin inherits both. Your job is to slot your own custom roles into this tree so the right privileges roll up to the right place.

Create the users first

Before we grant anything to Dana and Sam, Dana and Sam have to exist. This is the step every “here’s the grant” tutorial skips, and then you paste GRANT ROLE analyst TO USER dana and Snowflake tells you the user doesn’t exist. Users are created by USERADMIN (or anything above it):

USE ROLE USERADMIN;

CREATE USER dana
  PASSWORD = 'Ch4nge-me-now!'
  MUST_CHANGE_PASSWORD = TRUE
  DEFAULT_ROLE = analyst
  DEFAULT_WAREHOUSE = compute_wh
  DISPLAY_NAME = 'Dana Ruiz'
  EMAIL = '[email protected]';

CREATE USER sam
  PASSWORD = 'Ch4nge-me-too!'
  MUST_CHANGE_PASSWORD = TRUE
  DEFAULT_ROLE = loader
  DEFAULT_WAREHOUSE = compute_wh
  DISPLAY_NAME = 'Sam Okafor'
  EMAIL = '[email protected]';

MUST_CHANGE_PASSWORD = TRUE forces a reset on first login, so the throwaway password you typed here never becomes a real one. DEFAULT_ROLE and DEFAULT_WAREHOUSE set what the user lands in when they log in without an explicit USE ROLE/USE WAREHOUSE — a convenience, not a security boundary (the default role has to have actually been granted to them, which we do below, and a user can still USE ROLE any other role they hold).

A word on that password. In a real account you should not be handing out passwords at all for human users — turn on multi-factor authentication. Snowflake enforces MFA for human logins going forward, and the modern path is to enroll each user in an authenticator (or a passkey) rather than lean on a password. For programmatic access, use key-pair authentication or OAuth instead of a password entirely. Treat the PASSWORD above as a first-login bootstrap that MFA sits on top of, not the account’s actual front door.

Now the users exist and have a sensible landing role. On with the grants.

The shape that scales: functional roles and access roles

The naive version of “custom roles” is one role per job function, each granted its privileges directly. That works and it’s where most accounts start. But Snowflake’s own reference pattern goes one layer further, and it’s worth learning early because it’s much cheaper to adopt on day one than to refactor into later. The pattern splits roles into two kinds:

  • Access roles hold privileges on objects, and nothing else. They’re named for what they can do to what — sales_read, sales_readwrite — and you grant object privileges only to these. An access role never gets granted to a user.
  • Functional roles are named for who someone is — analyst, data_engineer. They hold no object privileges directly. Instead you grant them the access roles they need, and you grant the functional roles to users.

Access roles roll up into functional roles; functional roles roll up into SYSADMIN. When a new table’s permissions change, you edit one access role and every functional role that includes it inherits the change. When a person changes jobs, you swap which functional role they hold. Objects and people are decoupled by the access-role layer in between.

First, something to grant on

Every grant below refers to sales.public.orders, so let’s actually build it — this is the table the rest of the book keeps coming back to (sharing in chapter 15, streams and tasks in chapter 17, the metadata queries in chapter 20), and it is worth having one real thing rather than a hypothetical one.

Build it as SYSADMIN, not ACCOUNTADMIN. That’s the whole lesson of the ownership section later in this chapter, applied before you can get it wrong:

USE ROLE SYSADMIN;

CREATE DATABASE IF NOT EXISTS sales;
CREATE SCHEMA   IF NOT EXISTS sales.public;

CREATE OR REPLACE TABLE sales.public.orders (
    o_orderkey    NUMBER,
    o_custkey     NUMBER,
    o_orderstatus VARCHAR(1),
    o_totalprice  NUMBER(12,2),
    o_orderdate   DATE,
    region        VARCHAR        -- which sales region booked the order
);

Rather than invent rows, fill it from the TPCH sample data you’ve been querying since chapter 04 — the region column comes from walking customer → nation → region, which is the same five-way join from the capstone:

INSERT INTO sales.public.orders
SELECT o.o_orderkey, o.o_custkey, o.o_orderstatus,
       o.o_totalprice, o.o_orderdate, r.r_name
FROM snowflake_sample_data.tpch_sf1.orders   o
JOIN snowflake_sample_data.tpch_sf1.customer c ON c.c_custkey   = o.o_custkey
JOIN snowflake_sample_data.tpch_sf1.nation   n ON n.n_nationkey = c.c_nationkey
JOIN snowflake_sample_data.tpch_sf1.region   r ON r.r_regionkey = n.n_regionkey
WHERE o.o_orderdate >= '1998-01-01';

Add the two lookup tables the later chapters join against — the share in chapter 15, the Python connector in chapter 18 — so that sales is a small but complete schema rather than a single table:

CREATE OR REPLACE TABLE sales.public.customer AS
SELECT c_custkey, c_name, c_nationkey, c_mktsegment, c_acctbal
FROM snowflake_sample_data.tpch_sf1.customer;

CREATE OR REPLACE TABLE sales.public.nation AS
SELECT n_nationkey, n_name, n_regionkey
FROM snowflake_sample_data.tpch_sf1.nation;

Now there is a real database, three real tables, and real rows — and the grants below have something to be about.

The roles

Here’s the sales schema wired up the two-layer way. First the access roles and their object grants, done by SECURITYADMIN because these are grants on objects:

USE ROLE USERADMIN;
CREATE ROLE sales_read;
CREATE ROLE sales_readwrite;

USE ROLE SECURITYADMIN;
-- read access role: can see the schema and select from it
GRANT USAGE ON DATABASE sales                     TO ROLE sales_read;
GRANT USAGE ON SCHEMA   sales.public              TO ROLE sales_read;
GRANT SELECT ON ALL TABLES    IN SCHEMA sales.public TO ROLE sales_read;
GRANT SELECT ON FUTURE TABLES IN SCHEMA sales.public TO ROLE sales_read;

-- readwrite access role: inherits read, adds create + write
GRANT ROLE sales_read TO ROLE sales_readwrite;
GRANT CREATE TABLE ON SCHEMA sales.public              TO ROLE sales_readwrite;
GRANT INSERT, UPDATE, DELETE ON ALL TABLES    IN SCHEMA sales.public TO ROLE sales_readwrite;
GRANT INSERT, UPDATE, DELETE ON FUTURE TABLES IN SCHEMA sales.public TO ROLE sales_readwrite;

ON ALL versus ON FUTURE — the grant that silently rots

Look closely at the pairs of grants above, because this is the single most common way access quietly breaks. GRANT SELECT ON ALL TABLES IN SCHEMA sales.public grants SELECT on every table that exists right now. It is a one-time snapshot. The table you create tomorrow is not covered — sales_read will get a bald “does not exist or not authorized” on it, and you’ll waste an afternoon convinced the grant “didn’t take.”

GRANT SELECT ON FUTURE TABLES IN SCHEMA sales.public is the other half: it’s a standing rule that says any table created here from now on is automatically granted SELECT to this role. Neither one covers the other’s case, which is why you almost always want both — ON ALL catches what’s already there, ON FUTURE catches everything to come. Grant only ON ALL and your access decays every time someone runs CREATE TABLE. Grant only ON FUTURE and your existing tables stay invisible. Together they mean “this role can read this schema, permanently.”

(Future grants can also be set at the database level — ON FUTURE TABLES IN DATABASE sales — to blanket every schema, and they exist for other object types too: FUTURE VIEWS, FUTURE SCHEMAS, and so on. If both a database-level and a schema-level future grant apply, the more specific schema-level one wins.)

Now the functional roles. These hold no object privileges — only access roles — and they’re what people actually get:

USE ROLE USERADMIN;
CREATE ROLE analyst;
CREATE ROLE loader;

USE ROLE SECURITYADMIN;
-- functional roles get access roles, plus a warehouse to run on
GRANT ROLE sales_read      TO ROLE analyst;
GRANT USAGE ON WAREHOUSE compute_wh TO ROLE analyst;

GRANT ROLE sales_readwrite TO ROLE loader;
GRANT USAGE ON WAREHOUSE compute_wh TO ROLE loader;

Finally, wire the functional roles into the system hierarchy and hand them to people:

GRANT ROLE analyst TO ROLE SYSADMIN;
GRANT ROLE loader  TO ROLE SYSADMIN;

GRANT ROLE analyst TO USER dana;
GRANT ROLE loader  TO USER sam;

Granting both functional roles up to SYSADMIN means a SYSADMIN — and therefore an ACCOUNTADMIN above it — inherits everything without you re-granting anything. Dana logs in and lands in analyst (her default role); Sam lands in loader. Dana runs:

USE ROLE analyst;
SELECT n.n_name, SUM(o.o_totalprice)
FROM sales.public.orders o
JOIN sales.public.customer c ON c.c_custkey = o.o_custkey
JOIN sales.public.nation   n ON n.n_nationkey = c.c_nationkey
GROUP BY n.n_name;

That query works because analyst inherits sales_read, which holds SELECT on those tables. Run it while a role that lacks the grant is active and Snowflake refuses — access is exactly as wide as the active role, and no wider.

If the two-layer split feels like a lot for two roles, it is — for two roles. The payoff shows up at ten roles and fifty tables, when “give the new BI hire read access to sales and marketing” is two GRANT ROLE statements instead of a scavenger hunt through per-table grants. Start with the layers even while it’s just you; future-you inherits a clean account.

One role at a time — unless you use secondary roles

Earlier I said a user works as one role at a time. That’s the default and it’s a good default: your active privileges are exactly those of your current primary role, which makes “what can I do right now?” answerable without a whiteboard. But Snowflake has an escape hatch called secondary roles. Run:

USE SECONDARY ROLES ALL;

and your session’s active privileges become the union of every role granted to you, on top of your primary role. Dana, holding both analyst and — say — a marketing_read role, would with secondary roles active be able to query across both without switching. This is enormously convenient for interactive analysis and it’s why a BI tool connection often turns it on.

The catch, and the reason it’s not the default: some operations still care about your primary role specifically. Object creation uses the primary role for ownership (more on ownership next), and a few privilege checks look only at the primary role. So secondary roles widen what you can read and use, but “which role owns the table I just made” is still your one primary role. Keep that seam in mind — the “one role at a time” rule is really “one role at a time for the things that assign ownership,” with secondary roles unioning the rest.

Ownership, and the real fix for stranded objects

Here’s the trap that catches everyone who lives in ACCOUNTADMIN too long: the role that creates an object owns it. If you CREATE TABLE while acting as ACCOUNTADMIN, that table is owned by ACCOUNTADMIN, and your analyst and loader roles can’t touch it until someone explicitly grants access — which means every object you made during the “just me” phase is stranded at the top of the tree.

The first-line fix is a habit: create objects under the role that should own them. Switch to SYSADMIN (or a custom role beneath it) before you build a database or a table — which is exactly why the setup at the top of this chapter opened with USE ROLE SYSADMIN; rather than leaving you in ACCOUNTADMIN:

USE ROLE SYSADMIN;      -- not ACCOUNTADMIN
CREATE DATABASE sales;

Because sales was created that way, it is owned by SYSADMIN, sits in the hierarchy where you can reason about it, and your access roles can be granted against it cleanly. Had you built it as ACCOUNTADMIN — which is the role a fresh trial drops you into, and the reason this trap is so easy to fall into — you would already be in the mess the next paragraph digs out of.

But what about the objects you already stranded? Re-creating them isn’t the answer — ownership transfer is. OWNERSHIP is itself a privilege you can grant, and doing so hands the object to another role wholesale:

USE ROLE ACCOUNTADMIN;   -- or a role holding MANAGE GRANTS
GRANT OWNERSHIP ON TABLE sales.public.orders
  TO ROLE sales_readwrite
  COPY CURRENT GRANTS;

The COPY CURRENT GRANTS clause is the important part. Transferring ownership revokes all existing privilege grants on the object by default — which would break every role currently reading it. COPY CURRENT GRANTS preserves them across the handover. Its counterpart, REVOKE CURRENT GRANTS, does the opposite: it strips every other grant as part of the transfer, which is the sledgehammer you reach for when you’re deliberately locking an object down to its new owner. You have to name one or the other; there’s no silent default worth relying on. To sweep a whole schema of stranded objects, GRANT OWNERSHIP ON ALL TABLES IN SCHEMA sales.public TO ROLE ... COPY CURRENT GRANTS does them in bulk.

Delegating grants without handing over the keys

Two more mechanisms come up the moment more than one person is administering access.

WITH GRANT OPTION lets a role not only hold a privilege but re-grant it onward. GRANT SELECT ON ... TO ROLE analyst WITH GRANT OPTION means anyone in analyst can turn around and grant that same SELECT to another role. Use it sparingly — it’s how privileges spread in ways you didn’t personally authorize — but it’s the clean way to let a team lead manage access within their own domain without giving them SECURITYADMIN.

Managed access schemas invert the ownership-grants-everything default. Normally the owner of a table can grant privileges on it. In a schema created WITH MANAGED ACCESS, object owners cannot grant on their own objects — only the schema owner (or a role with MANAGE GRANTS) can. That centralizes all grant decisions for a sensitive schema in one place:

CREATE SCHEMA sales.restricted WITH MANAGED ACCESS;

It’s a foundations-level thing to name; you’ll reach for it the first time you have a schema where “whoever made the table gets to decide who reads it” is exactly the wrong policy.

The fine-grained tools, named so you know they exist

Roles and grants are the coarse control — who can touch which objects. Snowflake also has finer instruments for which rows and which column values a role sees. You won’t wire these up on day one, but you should recognize the names when they come up:

  • Row access policies filter rows by role. You write a policy and attach it to a table; the policy decides, per query, which rows the current role may see. One-liner: ALTER TABLE sales.public.orders ADD ROW ACCESS POLICY region_policy ON (region); — now analyst might see only EUROPE rows while loader sees all.
  • Dynamic data masking (column masking) rewrites a column’s value at query time based on role. ALTER TABLE ... ALTER COLUMN email SET MASKING POLICY mask_email; — and analyst gets ***@*** while a privileged role sees the real address. The underlying data never changes; the mask is applied on read.
  • Object tagging attaches key-value labels to objects (ALTER TABLE ... SET TAG cost_center = 'sales';), which then drive governance and cost reporting, and pair with the policies above to apply masking by tag rather than column-by-column.

Each of these gets its own treatment in chapter 16, where the policies are written out in full; for now, know that “roles decide objects, policies decide rows and cells” is the division of labor.

Auditing what you’ve granted

Grants accumulate, and six months in you’ll need to answer “who can read this?” and “what can this role do?” without guessing. Three tools cover it:

-- everything a role can do, and everything a user holds
SHOW GRANTS TO ROLE analyst;
SHOW GRANTS TO USER dana;

-- who has any privilege on a specific object
SHOW GRANTS ON TABLE sales.public.orders;

SHOW GRANTS is the live, point-in-time answer. For historical and account-wide analysis — “every grant to every role, queryable as a table” — reach for the Account Usage views:

SELECT grantee_name, privilege, name AS object_name
FROM snowflake.account_usage.grants_to_roles
WHERE deleted_on IS NULL
  AND granted_on = 'TABLE'
ORDER BY grantee_name;

SNOWFLAKE.ACCOUNT_USAGE.GRANTS_TO_ROLES (and its sibling GRANTS_TO_USERS) is the auditor’s friend: it’s SQL, so you can join it, filter it, and diff it over time. Note the usual Account Usage trade-off — these views have latency (up to a couple of hours) and retain deleted grants with a deleted_on timestamp, so filter deleted_on IS NULL when you want the current picture.

Tidying up defaults after the fact

Finally, the two settings you’ll adjust most often as people’s needs shift. Neither requires re-creating the user:

USE ROLE USERADMIN;
ALTER USER dana SET DEFAULT_ROLE = analyst;
ALTER USER dana SET DEFAULT_WAREHOUSE = compute_wh;

Changing DEFAULT_ROLE just changes where Dana lands; it doesn’t grant her anything (the role must already be granted to her) and it doesn’t stop her using other roles she holds. Changing DEFAULT_WAREHOUSE points her sessions at a different compute pool by default — handy when you spin up a dedicated warehouse for a team, which is exactly where the next chapter goes.

Final thoughts

Access control feels like ceremony when it’s just you, and that’s exactly the wrong time to judge it — the point of roles isn’t to slow you down today, it’s to make the account legible the day it stops being just you. The discipline reduces to a handful of habits: grant privileges to roles and roles to people, never privileges to people; split object-privilege access roles from person-shaped functional roles so you can change one without churning the other; always pair ON ALL with ON FUTURE so new tables don’t quietly fall out of coverage; and create objects as the role that should own them — reaching for GRANT OWNERSHIP ... COPY CURRENT GRANTS when something’s already stranded. Get those right from post one and you’ll never spend a weekend untangling who can see what.

Next: Warehouse Sizes, Auto-Suspend, and the Credit Meter

Comments