Snowflake RBAC Design: A Role Hierarchy That Survives an Audit and a Reorg

Every Snowflake account I have inherited had the same permission model: a role named after each team, a pile of grants added whenever someone was blocked, and one person who knew why ANALYST_ROLE could write to a production schema.

That model works fine right up to the two events it cannot survive. The first is an audit, where someone asks you to produce a list of everyone who can read the customer PII table and you discover the answer requires three hours and a recursive query. The second is a reorg, where the analytics team splits in two and you find that “analyst” was never a set of privileges, it was a set of accidents.

The fix is not more roles. It is a discipline about what a role is for. Snowflake’s model gives you role inheritance, which means you can separate the roles that describe what someone does from the roles that describe what access exists. Get that separation right and both the audit and the reorg become boring.

I have rebuilt this three times. What follows is the version I would build again.

The two-layer model

There are exactly two kinds of role in my accounts.

An access role describes a specific privilege set on a specific set of objects. AR_ANALYTICS_GOLD_R means read on the gold schema in the analytics database. AR_ANALYTICS_SILVER_RW means read and write on silver. Access roles are granted object privileges. They are never granted to users.

A functional role describes a job. FR_ANALYST, FR_DATA_ENGINEER, FR_DBT_PROD. Functional roles are granted to users and to service accounts. They hold no object privileges at all — they hold access roles.

flowchart TD
  user["User or service account"] --> fr["Functional role: FR_ANALYST"]
  fr --> ar1["Access role: AR_ANALYTICS_GOLD_R"]
  fr --> ar2["Access role: AR_RAW_MARKETING_R"]
  ar1 --> obj1["Grants on ANALYTICS.GOLD"]
  ar2 --> obj2["Grants on RAW.MARKETING"]
  fr --> sysadmin["Granted up to SYSADMIN for visibility"]

People attach at the top, privileges at the bottom. The join in the middle is the only thing a reorg changes.

The payoff is that the two layers change for different reasons and on different schedules. A reorg changes which access roles a functional role holds — a handful of GRANT ROLE statements. A new data domain adds access roles without touching anyone’s job definition. Nothing has to be re-derived from scratch, and the diff in your repository reads like the business change that caused it.

The naming convention matters more than it sounds. AR_ and FR_ prefixes let you write assertions: no FR_ role should ever appear in GRANTS_TO_ROLES with a table-level privilege. That is a one-line CI check, and it is the check that keeps the model from eroding.

Building the access roles

Start with the database and schema layout, because access roles mirror it. For each schema, I create at most three access roles: read, read-write, and (rarely) full. More granularity than that is usually a sign the schema is doing two jobs.

use role securityadmin;

create role if not exists ar_analytics_gold_r;
create role if not exists ar_analytics_gold_rw;

-- Usage on the container objects is required before anything else works.
grant usage on database analytics to role ar_analytics_gold_r;
grant usage on schema analytics.gold to role ar_analytics_gold_r;

-- Current objects.
grant select on all tables in schema analytics.gold to role ar_analytics_gold_r;
grant select on all views in schema analytics.gold to role ar_analytics_gold_r;

-- Objects that do not exist yet. This is the line people forget.
grant select on future tables in schema analytics.gold to role ar_analytics_gold_r;
grant select on future views in schema analytics.gold to role ar_analytics_gold_r;

-- Read-write inherits read, then adds the write privileges.
grant role ar_analytics_gold_r to role ar_analytics_gold_rw;
grant insert, update, delete, truncate on all tables in schema analytics.gold
  to role ar_analytics_gold_rw;
grant insert, update, delete, truncate on future tables in schema analytics.gold
  to role ar_analytics_gold_rw;
grant create table, create view on schema analytics.gold to role ar_analytics_gold_rw;

Future grants are the single most valuable feature in this whole post. Without them, every table your dbt project creates tomorrow is invisible to analysts until someone runs a grant script, and the gap between “model shipped” and “grants applied” is where production incidents live.

Two things to know about them. Database-level future grants and schema-level future grants on the same object type conflict, with the schema-level one winning — so pick one level and stay there. I use schema level, always, because it is explicit. And future grants do not apply to objects created before the grant existed, which is why you run both ON ALL and ON FUTURE every time.

Functional roles and the SYSADMIN spine

Functional roles are thin. They exist to be granted to humans and to be granted a set of access roles.

use role securityadmin;

create role if not exists fr_analyst;
create role if not exists fr_data_engineer;
create role if not exists fr_dbt_prod;

-- Analysts read gold and the curated marketing raw feed. Nothing else.
grant role ar_analytics_gold_r   to role fr_analyst;
grant role ar_raw_marketing_r    to role fr_analyst;

-- Engineers read everything and write to silver and gold.
grant role ar_analytics_gold_rw  to role fr_data_engineer;
grant role ar_analytics_silver_rw to role fr_data_engineer;
grant role ar_raw_all_r          to role fr_data_engineer;

-- The dbt service account writes, and nothing else does in prod.
grant role ar_analytics_silver_rw to role fr_dbt_prod;
grant role ar_analytics_gold_rw   to role fr_dbt_prod;

-- Warehouse access is a functional concern, not an object one.
grant usage, operate on warehouse transforming_wh to role fr_data_engineer;
grant usage on warehouse bi_wh to role fr_analyst;

-- Keep the hierarchy connected so SYSADMIN can see and manage everything.
grant role fr_analyst       to role sysadmin;
grant role fr_data_engineer to role sysadmin;
grant role fr_dbt_prod      to role sysadmin;

-- People attach here and only here.
grant role fr_analyst to user jsmith;

That last grant to SYSADMIN is not optional in practice. Snowflake’s system roles form a tree, and if your custom roles hang off nothing, SYSADMIN cannot administer the objects they own and you end up using ACCOUNTADMIN for routine work. That is the habit that gets flagged in every security review I have sat in.

Speaking of which: ACCOUNTADMIN should belong to two or three named humans with MFA, should never be a default role, and should never own an object. SECURITYADMIN manages roles and grants. SYSADMIN owns databases, schemas, and warehouses. Keeping those three separate is most of what a reviewer is looking for.

Managed access schemas and ownership

By default in Snowflake, whoever owns an object can grant privileges on it. That is a reasonable default for a small team and a disaster for governance, because it means your carefully designed model can be bypassed by any engineer with CREATE TABLE.

Managed access schemas move that authority to the schema owner:

use role sysadmin;

create schema if not exists analytics.gold with managed access;

-- Convert an existing schema.
alter schema analytics.gold enable managed access;

In a managed access schema, only the schema owner (and roles with MANAGE GRANTS) can grant privileges on objects inside it, even if someone else owns the table. Every production schema in my accounts is managed access. It costs nothing and it turns “who could have granted this?” from an investigation into a one-line answer.

The related discipline is ownership. Objects should be owned by roles, never by users, and the owning role should be an access role or a dedicated ownership role rather than a functional one.

-- Make the service role create objects with a stable owner.
alter user svc_dbt_prod set default_role = fr_dbt_prod;

-- Reassign anything a departing human owns.
grant ownership on all tables in schema analytics.gold
  to role ar_analytics_gold_rw
  copy current grants;

COPY CURRENT GRANTS on that statement is important — the default is REVOKE CURRENT GRANTS, which will drop existing privileges and give you a very interesting morning.

Auditing: the queries you will be asked for

Two questions come up in every review. Who can read this table? What can this person do? Both are answerable from ACCOUNT_USAGE, and both should be saved views rather than ad hoc SQL you rewrite under pressure.

-- Everything a given role can reach, one level deep plus inherited roles.
with recursive role_tree as (
  select grantee_name as role_name, name as granted_role
  from snowflake.account_usage.grants_to_roles
  where granted_on = 'ROLE' and privilege = 'USAGE' and deleted_on is null
    and grantee_name = 'FR_ANALYST'
  union all
  select rt.granted_role, g.name
  from role_tree rt
  join snowflake.account_usage.grants_to_roles g
    on g.grantee_name = rt.granted_role
   and g.granted_on = 'ROLE' and g.privilege = 'USAGE' and g.deleted_on is null
)
select distinct g.privilege, g.granted_on, g.table_catalog, g.table_schema, g.name
from snowflake.account_usage.grants_to_roles g
where g.deleted_on is null
  and g.grantee_name in (select granted_role from role_tree)
  and g.granted_on in ('TABLE', 'VIEW', 'MATERIALIZED_VIEW')
order by 3, 4, 5;

And the reverse direction, which is the one auditors ask first:

-- Who currently holds a role, directly or by inheritance.
select
  grantee_name as user_name,
  role,
  created_on
from snowflake.account_usage.grants_to_users
where role = 'FR_DATA_ENGINEER'
  and deleted_on is null
order by created_on;

-- Anything owned by a user rather than a role is an audit finding.
select table_catalog, table_schema, table_name, table_owner
from snowflake.account_usage.tables
where deleted is null
  and table_owner in (select name from snowflake.account_usage.users where deleted_on is null)
order by 1, 2, 3;

I run that last one weekly. It should always return zero rows, and the week it does not, someone created a table with the wrong active role and you want to know before it becomes load-bearing.

Put it in version control

An RBAC model maintained by hand in worksheets is not a model. It is a set of decisions nobody can reconstruct. The grants above are declarative statements, which means they belong in a repository and they should be applied by CI.

You have three reasonable options. Terraform with the Snowflake provider is the most complete and the most work. Permifrost or a similar YAML-driven tool gives you a readable spec and drift detection. A plain SQL directory applied idempotently by a pipeline is the simplest and, for most teams, entirely sufficient — every statement above is safe to re-run.

Whichever you pick, add the assertions that make the model self-enforcing:

-- CI check: functional roles must never hold object privileges directly.
select grantee_name, privilege, granted_on, name
from snowflake.account_usage.grants_to_roles
where deleted_on is null
  and grantee_name like 'FR\\_%' escape '\\'
  and granted_on in ('TABLE', 'VIEW', 'STAGE', 'FUNCTION');
-- Expected: zero rows. Fail the build otherwise.

That single query is what keeps the two-layer model from decaying into the pile of accidents you started with. The same instinct applies to schema shape and column contracts, which is why this pairs naturally with data contracts and with dbt testing in production.

Where teams get this wrong

Granting object privileges to person-facing roles. It works today, and it makes every future change a search-and-replace across the account. This is the one rule that, if you keep only one, keeps the model salvageable.

Forgetting future grants. Every table created after your grant script ran is invisible to the people who need it, and someone will fix it with a broad grant at 6 p.m. on a Friday.

Using ACCOUNTADMIN for routine work. It creates objects owned by the wrong role, it shows up in every audit, and it means one compromised session owns the account. Set default roles explicitly for every user.

Cloning a database and forgetting the grants. A zero-copy clone copies table-level grants only if you ask, and never copies future grants. Cloned environments are a classic source of “why can dev read prod PII?”

Roles named after people or tools. JSMITH_ROLE and LOOKER_ROLE both encode something that will change. Name roles after functions and grant them to whoever or whatever performs the function.

No revocation process. Access accretes. If nobody ever removes a role grant, your model is only ever growing, and the audit will find the intern from 2023 who still reads finance data.

FAQ

How many roles is too many?

The count matters less than the shape. Two access roles per schema plus one functional role per job function scales to hundreds of schemas without confusion, because the structure is predictable. A hundred roles with no naming convention is unmanageable at any size.

Should service accounts use the same functional roles as humans?

No. Give each service its own functional role, even when the privileges initially match a human role. They diverge quickly, and having FR_DBT_PROD separate from FR_DATA_ENGINEER means you can revoke write access from humans without breaking the pipeline.

Do I need SCIM or can I grant roles manually?

Manual grants are fine below roughly fifty users if you have a documented offboarding step. Above that, use SCIM with your identity provider so that group membership drives role membership and offboarding is automatic. The failure mode of manual is always the same: people leave and their grants do not.

How does this interact with masking policies?

Cleanly, and they complement each other. RBAC decides who reaches the table; a masking policy decides what they see once they are there. Write masking policies against functional role names, since those are the stable identity in the model.

What about database roles?

Database roles are scoped inside a single database and are the right tool when you are sharing a database, since account roles cannot cross a share boundary. For internal access I still use account-level access roles, and I reach for database roles specifically when building shares.

What this means for your pipelines

RBAC feels like a security topic, but the day-to-day pain it causes is a pipeline reliability problem. A new model lands and the dashboard breaks because the grant was not applied. An engineer runs dbt with the wrong active role and creates a table owned by their personal user, which then fails when they leave. A clone of prod into dev quietly carries production grants. All of that is caused by a permission model that lives in someone’s head instead of in the repository.

The two-layer split is what makes it mechanical. Access roles are generated from the schema layout, so they can be templated. Functional roles are a short, readable list that maps to your org chart. The join between them is the only file that a reorg touches, and reviewing that diff is a five-minute conversation rather than an archaeology project.

Put the grants in version control, apply them from CI, run the three audit queries above on a schedule, and add the functional-role assertion as a failing test. After that, the audit is a matter of running a saved view, and the reorg is a pull request. That is the whole goal — not perfect least privilege on day one, but a model where the correct change is obvious and the incorrect one fails a test.