Snowflake – Roles, Grants and Access Control

Snowflake's access control is straightforward once two ideas land: privileges are granted to roles, never to users, and roles are granted to other roles, forming a hierarchy. Get those two right and the rest is detail. Get them wrong and you end up with an account where everything runs as ACCOUNTADMIN, which is where most Snowflake accounts start and too many stay.

The system roles

Role	For
`ORGADMIN`	Managing accounts across an organisation.
`ACCOUNTADMIN`	Everything. Billing, account parameters, the lot.
`SECURITYADMIN`	Managing grants account-wide; inherits `USERADMIN`.
`USERADMIN`	Creating users and roles.
`SYSADMIN`	Creating warehouses, databases and objects.
`PUBLIC`	Automatically held by everyone. Grant almost nothing to it.

Stop working as ACCOUNTADMIN. It is not a matter of taste. A stray CREATE TABLE as ACCOUNTADMIN produces an object that ACCOUNTADMIN owns, which means SYSADMIN — the role your team actually uses — cannot manage it, and the fix is a grant somebody has to remember to make.

Use it for exactly three things: initial setup, billing and resource monitors, and account-level parameters. Give it to two people, both with MFA, and have those people work as something else.

Ownership versus privileges

Every object has exactly one owning role. Ownership is not a privilege — it is a separate thing, and it confers full control including the right to grant to others.

-- Whoever runs this owns the table. Check before you create.
SELECT CURRENT_ROLE();
CREATE TABLE analytics.marts.fact_orders (...);

-- Hand it over. COPY CURRENT GRANTS keeps existing access working;
-- without it, every grant on the object is revoked.
GRANT OWNERSHIP ON TABLE analytics.marts.fact_orders
  TO ROLE data_engineer COPY CURRENT GRANTS;

SHOW GRANTS ON TABLE analytics.marts.fact_orders;

The rule that avoids most ownership problems: create objects as the role that should own them, and make SYSADMIN the parent of every functional role so it retains visibility over everything.

A role layout that works

The standard structure separates access roles, which hold privileges on objects, from functional roles, which are what people are granted. It looks like extra work for a small account and pays for itself the first time a fourth team appears.

USE ROLE USERADMIN;

-- Functional roles: what a person or service does.
CREATE ROLE data_engineer;
CREATE ROLE analyst;
CREATE ROLE bi_service;

-- Access roles: read or write on one schema.
CREATE ROLE marts_reader;
CREATE ROLE marts_writer;

USE ROLE SECURITYADMIN;

-- Compose. A writer is also a reader; SYSADMIN sits above everything.
GRANT ROLE marts_reader TO ROLE marts_writer;
GRANT ROLE marts_reader TO ROLE analyst;
GRANT ROLE marts_reader TO ROLE bi_service;
GRANT ROLE marts_writer TO ROLE data_engineer;
GRANT ROLE data_engineer TO ROLE SYSADMIN;
GRANT ROLE analyst       TO ROLE SYSADMIN;

Now the object privileges hang off the access roles only, and nothing has to change when a person joins a team:

GRANT USAGE ON DATABASE analytics                TO ROLE marts_reader;
GRANT USAGE ON SCHEMA   analytics.marts          TO ROLE marts_reader;
GRANT SELECT ON ALL TABLES IN SCHEMA analytics.marts TO ROLE marts_reader;
GRANT SELECT ON ALL VIEWS  IN SCHEMA analytics.marts TO ROLE marts_reader;

GRANT INSERT, UPDATE, DELETE ON ALL TABLES IN SCHEMA analytics.marts
  TO ROLE marts_writer;

GRANT USAGE ON WAREHOUSE bi_wh TO ROLE analyst;
GRANT USAGE ON WAREHOUSE bi_wh TO ROLE bi_service;

GRANT ROLE analyst TO USER sam;

USAGE on the database and schema is the step people miss. Without it, SELECT on the table is unreachable and the error says the object "does not exist or not authorized" — which sends everyone hunting for a typo.

Future grants: the feature that stops permissions rotting

GRANT SELECT ON ALL TABLES applies to the tables that exist right now. The table created tomorrow is not covered, and that is why every account without future grants eventually has an analyst who cannot see the newest table and nobody who knows why.

-- Everything created in this schema from now on.
GRANT SELECT ON FUTURE TABLES IN SCHEMA analytics.marts TO ROLE marts_reader;
GRANT SELECT ON FUTURE VIEWS  IN SCHEMA analytics.marts TO ROLE marts_reader;

-- Or at database level, covering schemas that do not exist yet.
GRANT USAGE  ON FUTURE SCHEMAS IN DATABASE analytics    TO ROLE marts_reader;
GRANT SELECT ON FUTURE TABLES  IN DATABASE analytics    TO ROLE marts_reader;

SHOW FUTURE GRANTS IN SCHEMA analytics.marts;

Set future grants once, when the schema is created, alongside the ALL grants that catch what is already there. Doing both is the complete answer; doing only one is the bug.

One subtlety: a database-level future grant and a schema-level one on the same object type do not combine — the more specific one wins. If a schema behaves differently from its siblings, look for a schema-level future grant overriding the database-level one.

Secondary roles

A session has one primary role, and privileges do not combine across roles unless you enable secondary roles:

USE SECONDARY ROLES ALL;    -- union of every role the user holds
SELECT CURRENT_SECONDARY_ROLES();
USE SECONDARY ROLES NONE;   -- back to just the primary role

Convenient for an analyst joining across two teams' data. Avoid it for service accounts, where the point of the role is to be a precise, auditable boundary.

Masking and row access policies

Two features handle the cases a grant cannot express — column-level and row-level rules that follow the data everywhere, including through views.

CREATE OR REPLACE MASKING POLICY mask_email AS (val STRING) RETURNS STRING ->
  CASE WHEN CURRENT_ROLE() IN ('DATA_ENGINEER', 'ACCOUNTADMIN') THEN val
       ELSE REGEXP_REPLACE(val, '.+@', '*****@')
  END;

ALTER TABLE analytics.marts.dim_customer
  MODIFY COLUMN email SET MASKING POLICY mask_email;

CREATE OR REPLACE ROW ACCESS POLICY region_rows AS (region STRING) RETURNS BOOLEAN ->
  CURRENT_ROLE() = 'DATA_ENGINEER'
  OR EXISTS (SELECT 1 FROM security.role_regions
             WHERE role_name = CURRENT_ROLE() AND allowed_region = region);

ALTER TABLE analytics.marts.fact_sales
  ADD ROW ACCESS POLICY region_rows ON (region);

Both require Enterprise edition. The advantage over building the same logic into a view is that the policy cannot be bypassed by querying the base table — it travels with the column.

Service accounts

The account your pipeline logs in as deserves different treatment from a person's, and the difference is worth being deliberate about. A human gets MFA and browses; a service account gets a key, a fixed role, and nothing it does not need.

One service account per service. Sharing one between the loader and the BI tool means the audit log cannot tell you which of them ran the expensive query, and revoking access for one revokes it for both.
Key-pair authentication, not a password — lesson 3 covers the setup. Snowflake is progressively retiring password-only sign-in for programmatic users, so this is where things are heading regardless.
A default role and warehouse on the user. A service that forgets to set context then still runs with the intended privileges rather than falling back to PUBLIC.
A network policy, so a leaked key is only usable from your infrastructure.

USE ROLE USERADMIN;

CREATE USER etl_service
  TYPE              = SERVICE          -- no password, no MFA, no UI login
  DEFAULT_ROLE      = data_engineer
  DEFAULT_WAREHOUSE = loading_wh
  RSA_PUBLIC_KEY    = 'MIIBIjANBgkqh...'
  COMMENT           = 'Nightly load pipeline. Owner: data platform team.';

USE ROLE SECURITYADMIN;
GRANT ROLE data_engineer TO USER etl_service;

CREATE NETWORK POLICY etl_only ALLOWED_IP_LIST = ('203.0.113.0/24');
ALTER USER etl_service SET NETWORK_POLICY = etl_only;

The COMMENT is not decoration. Eighteen months from now somebody will find this account in an audit and need to know whether it can be disabled; a sentence naming the owner is the difference between a five-minute answer and a week of asking around.

Auditing what you have

SHOW GRANTS TO ROLE analyst;
SHOW GRANTS TO USER sam;
SHOW GRANTS ON TABLE analytics.marts.fact_orders;

-- Who can read this table, transitively through role inheritance?
SELECT grantee_name, privilege, granted_on, name
FROM   snowflake.account_usage.grants_to_roles
WHERE  granted_on = 'TABLE'
  AND  name = 'FACT_ORDERS'
  AND  deleted_on IS NULL;

-- Accounts nobody is using. Review quarterly.
SELECT name, last_success_login, disabled, has_rsa_public_key
FROM   snowflake.account_usage.users
WHERE  deleted_on IS NULL
  AND  (last_success_login < DATEADD('day', -90, CURRENT_TIMESTAMP())
        OR last_success_login IS NULL);

Next: controlling cost.