Skip to content
Tutorials Center
My learning

Topics

Lesson 26 of 38 ยท Security and Governance

Roles, Privileges and Access Control

Learn Snowflake role-based access control: system roles, the role hierarchy, GRANT and REVOKE, future grants, database roles and a least-privilege design.

Snowflake combines two access models. Discretionary access control (DAC) means every object has an owner who can grant access to it. Role-based access control (RBAC) means privileges are granted to roles, and roles are granted to users. You never grant a table privilege to a person directly: you grant it to a role, and give the person that role. Understanding this chain is the foundation of Snowflake security.

Snowflake itself is not available in the browser here. Its statements are shown as read-only code, and the runnable examples model roles and grants with portable SQL in SQLite.

Objects, privileges, roles, users

  • A securable object is anything you can protect: account, warehouse, database, schema, table, view, stage, task and so on, arranged in a hierarchy (account, database, schema, object).
  • A privilege is a permitted action on an object, for example SELECT, INSERT, USAGE, CREATE TABLE, OPERATE.
  • A role is a named set of privileges.
  • A user is an identity that can be granted roles. A session has one primary role at a time (USE ROLE), and can also use secondary roles.

To read sales.public.orders a role needs three grants at different levels: USAGE on the database, USAGE on the schema and SELECT on the table. Forgetting the first two is the most common cause of "object does not exist or not authorized".

SQL
CREATE ROLE analyst;

GRANT USAGE ON WAREHOUSE reporting_wh TO ROLE analyst;
GRANT USAGE ON DATABASE sales TO ROLE analyst;
GRANT USAGE ON SCHEMA sales.public TO ROLE analyst;
GRANT SELECT ON TABLE sales.public.orders TO ROLE analyst;

GRANT ROLE analyst TO USER maria;

System-defined roles

Every account has these roles, arranged so that the more powerful ones inherit from the others:

Role Purpose
ACCOUNTADMIN Top role: account-level settings, billing, everything below it. Use rarely.
SECURITYADMIN Manages grants globally; owns USERADMIN.
USERADMIN Creates users and roles.
SYSADMIN Creates and owns warehouses, databases and other objects.
ORGADMIN Manages accounts in an organization.
PUBLIC Automatically granted to everyone; any privileges here apply to all users.

Best practice: do not do daily work as ACCOUNTADMIN, require MFA for it, and have at least two people who can use it. Build your own roles and attach them under SYSADMIN so that system admins can see all objects.

The role hierarchy

Roles can be granted to other roles. The parent role inherits all privileges of its children.

SQL
CREATE ROLE reader;
CREATE ROLE engineer;

GRANT ROLE reader   TO ROLE engineer;   -- engineer inherits reader
GRANT ROLE engineer TO ROLE sysadmin;    -- sysadmin inherits both

A typical layout uses functional roles (what a person does: analyst, engineer) built from access roles (what data they can touch: sales_read, sales_write). Privileges go to access roles; access roles are granted to functional roles; functional roles go to users.

flowchart TB
  U["User maria"] --> F["Functional role: analyst"]
  F --> A1["Access role: sales_read"]
  F --> A2["Access role: marketing_read"]
  A1 --> P1["SELECT on sales tables"]
  A2 --> P2["SELECT on marketing tables"]

Grant privileges to access roles, access roles to functional roles, functional roles to users.

Modelling inheritance

Here is a runnable model of a role hierarchy with a recursive query: given a user's role, find every role it effectively holds, and so every privilege.

SQL Role hierarchy with a recursive query (stand-in)

Press Run (or Ctrl + Enter) to see the result here.

The engineer role holds its own INSERT plus the SELECT privileges of everything below it. Replace 'engineer' with 'analyst' and the INSERT disappears, because inheritance flows only downward.

Ownership, GRANT and REVOKE

The role that creates an object owns it (and the OWNERSHIP privilege lets it grant access to others). Ownership is usually moved to a stable role rather than a person's role:

SQL
GRANT OWNERSHIP ON TABLE sales.public.orders TO ROLE sysadmin COPY CURRENT GRANTS;
REVOKE SELECT ON TABLE sales.public.orders FROM ROLE analyst;
SHOW GRANTS TO ROLE analyst;
SHOW GRANTS ON TABLE sales.public.orders;
SHOW GRANTS OF ROLE analyst;      -- who holds the role

Future and bulk grants

Granting SELECT on today's tables misses tomorrow's. Future grants apply automatically to objects created later:

SQL
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;

Use ALL for what exists now and FUTURE for what will exist. Schemas created as managed access schemas (CREATE SCHEMA ... WITH MANAGED ACCESS) go one step further: only the schema owner (or a role with MANAGE GRANTS) can grant on objects inside, so object owners cannot hand out access on their own.

Database roles and secondary roles

A database role lives inside one database, so it travels with that database when it is cloned or shared:

SQL
CREATE DATABASE ROLE sales.reader;
GRANT SELECT ON ALL TABLES IN SCHEMA sales.public TO DATABASE ROLE sales.reader;
GRANT DATABASE ROLE sales.reader TO ROLE analyst;

Secondary roles let one session combine privileges of all granted roles with USE SECONDARY ROLES ALL, so users do not switch roles for each task.

A least-privilege checklist

  1. Daily work happens in custom roles, never ACCOUNTADMIN.
  2. Privileges are granted to roles, never to users.
  3. Separate read, write and admin access roles per data domain.
  4. Use future grants so new tables are covered, managed access schemas to limit who can grant.
  5. Review SNOWFLAKE.ACCOUNT_USAGE.GRANTS_TO_ROLES and GRANTS_TO_USERS regularly.
  6. Remove unused roles and users.

Check your understanding

Quick check ยท 5 questions

of 5 answered You scored / 5 ยท perfect!

  1. 1. To query a table, which grants does a role need?

    Access flows down the hierarchy: database USAGE, schema USAGE, then SELECT. You also need USAGE on a warehouse to run the query.
  2. 2. Role A is granted to role B. Which statement is true?

    The role that receives the grant (B) inherits the privileges of the granted role (A).
  3. 3. Which role is meant for creating users and roles?

    USERADMIN creates and manages users and roles; SYSADMIN manages objects like warehouses and databases.
  4. 4. What problem do FUTURE grants solve?

    A future grant applies automatically to objects created later in the schema or database.
  5. 5. Why attach custom roles under SYSADMIN?

    Placing roles in the SYSADMIN hierarchy keeps objects visible to the administrators who manage them.

Summary

  • Privileges go to roles, roles go to users; roles can inherit other roles.
  • Table access needs database and schema USAGE plus table privileges.
  • System roles: ACCOUNTADMIN, SECURITYADMIN, USERADMIN, SYSADMIN, ORGADMIN, PUBLIC.
  • Use future grants, access and functional roles, database roles and managed access schemas.
  • Avoid ACCOUNTADMIN for daily work.

Next: Authentication and network policies