Skip to content
LakeBench
ProblemsCommunityPricing
Sign inStart practicing
Back
  1. Home
  2. Interview prep
  3. Snowflake RBAC and system roles

Snowflake, BigQuery & Databricks · Snowflake

Snowflake RBAC and system roles

Mediumwarehouses-12
snowflakerbacrolessecuritygrants

Question

How does access control work in Snowflake?

Solution

In Snowflake you never give privileges directly to users. You grant privileges on objects to roles, and then grant roles to users or to other roles. A user acts through the role they currently use.

The pieces

CREATE ROLE analyst;
GRANT USAGE ON DATABASE analytics TO ROLE analyst;
GRANT USAGE ON SCHEMA analytics.marts TO ROLE analyst;
GRANT SELECT ON ALL TABLES IN SCHEMA analytics.marts TO ROLE analyst;
GRANT SELECT ON FUTURE TABLES IN SCHEMA analytics.marts TO ROLE analyst;
GRANT ROLE analyst TO USER asha;

The FUTURE grant is the useful one: without it, every new table needs a fresh grant, and people forget.

Role hierarchy

A role can be granted to another role, and the parent inherits every privilege of the child. So you can build a data_team_lead role that includes analyst and engineer.

System roles

  • ACCOUNTADMIN: top level, can do everything, including billing. Keep for very few people, with MFA.
  • SECURITYADMIN: manages grants and users and roles.
  • USERADMIN: creates users and roles.
  • SYSADMIN: creates and manages warehouses, databases and other objects.
  • PUBLIC: given to every user automatically.

A common design pattern

Separate two kinds of roles. Access roles hold privileges on data, for example marts_read and raw_write. Functional roles describe a job, for example analyst or etl_service, and they are granted the access roles they need. People get functional roles. This keeps grants easy to review.

What to avoid

Do not use ACCOUNTADMIN for daily work or for pipelines. A mistake with that role can drop anything. Create custom roles owned by SYSADMIN (or a dedicated admin role), so objects have sensible owners. Use service users with key-pair authentication for pipelines, and give them narrow roles. Review grants regularly, and remember that ownership of an object lets the owner role grant it onwards.

PreviousNext