A Snowflake RBAC Blueprint: Functional Roles, Access Roles, Database Roles, and Grants You Can Audit

Most Snowflake accounts start with three roles and a shared password, and most of them are still running that way two years later — except now there are 40 roles, nobody knows which ones are actually granted to people, and every new schema request turns into a ticket that takes a week. Authentication hardening (MFA, key pairs, service users) gets the attention because Snowflake now enforces it; authorization design is the part teams quietly defer.

This tutorial is the blueprint we deploy on client accounts: a two-layer role model, database roles for packaging, a naming convention that survives growth, and the SQL to stand it up and audit it.

The core idea: separate who you are from what you can touch

Snowflake RBAC is a graph — roles can be granted to other roles. That single feature is what lets you avoid the 40-role swamp, if you use it deliberately.

Split roles into two layers:

Layer	Purpose	Granted to	Example
Access roles (a.k.a. permission roles)	Hold privileges on exactly one object scope, at one level	Only to functional roles	AR_SALES_RAW_RO, AR_SALES_MART_RW
Functional roles (a.k.a. business roles)	Describe a job. Hold no direct privileges	Users, service users, and other functional roles	FR_ANALYST_SALES, FR_ENGINEER_PLATFORM

Rules that make it work:

1. Privileges are never granted to a functional role directly.
2. Users are never granted an access role directly.
3. An access role covers one database/schema scope and one access level (RO, RW, FULL).

Now a new hire is one GRANT ROLE FR_ANALYST_SALES TO USER ..., and a new schema is a set of access roles plugged into existing functional roles. Reviews become answerable: "what can an analyst see?" is one query, not an archaeology project.

Naming convention

Boring and mechanical beats clever:

AR_<DATABASE>_<SCHEMA>_<RO|RW|FULL>
FR_<JOB>[_<DOMAIN>]
SR_<SYSTEM>            -- service/integration roles, e.g. SR_DBT_PROD, SR_FIVETRAN

Every role gets a COMMENT. Every role has an owner role that is not ACCOUNTADMIN.

Step 1: object ownership and a functional-role parent

Create a role that owns the data objects, so ownership never accidentally lands on a person's role:

USE ROLE USERADMIN;
CREATE ROLE IF NOT EXISTS OBJ_OWNER
  COMMENT = 'Owns databases/schemas/tables. Never granted to humans.';

USE ROLE SECURITYADMIN;
GRANT ROLE OBJ_OWNER TO ROLE SYSADMIN;   -- keep the SYSADMIN hierarchy intact

Keeping every custom role reachable from SYSADMIN matters: if you skip it, SYSADMIN cannot manage objects created by those roles and you end up doing routine work as ACCOUNTADMIN.

Step 2: access roles for a schema

USE ROLE USERADMIN;
CREATE ROLE IF NOT EXISTS AR_ANALYTICS_SALES_RO COMMENT = 'Read ANALYTICS.SALES';
CREATE ROLE IF NOT EXISTS AR_ANALYTICS_SALES_RW COMMENT = 'Read/write ANALYTICS.SALES';

USE ROLE OBJ_OWNER;

-- read-only
GRANT USAGE ON DATABASE ANALYTICS TO ROLE AR_ANALYTICS_SALES_RO;
GRANT USAGE ON SCHEMA   ANALYTICS.SALES TO ROLE AR_ANALYTICS_SALES_RO;
GRANT SELECT ON ALL TABLES  IN SCHEMA ANALYTICS.SALES TO ROLE AR_ANALYTICS_SALES_RO;
GRANT SELECT ON FUTURE TABLES IN SCHEMA ANALYTICS.SALES TO ROLE AR_ANALYTICS_SALES_RO;
GRANT SELECT ON ALL VIEWS   IN SCHEMA ANALYTICS.SALES TO ROLE AR_ANALYTICS_SALES_RO;
GRANT SELECT ON FUTURE VIEWS IN SCHEMA ANALYTICS.SALES TO ROLE AR_ANALYTICS_SALES_RO;

-- read/write inherits read
GRANT ROLE AR_ANALYTICS_SALES_RO TO ROLE AR_ANALYTICS_SALES_RW;
GRANT INSERT, UPDATE, DELETE, TRUNCATE ON ALL TABLES    IN SCHEMA ANALYTICS.SALES TO ROLE AR_ANALYTICS_SALES_RW;
GRANT INSERT, UPDATE, DELETE, TRUNCATE ON FUTURE TABLES IN SCHEMA ANALYTICS.SALES TO ROLE AR_ANALYTICS_SALES_RW;
GRANT CREATE TABLE, CREATE VIEW, CREATE DYNAMIC TABLE ON SCHEMA ANALYTICS.SALES TO ROLE AR_ANALYTICS_SALES_RW;

The FUTURE grant trap. Future grants exist at both database and schema level, and the schema-level future grant wins — a database-level GRANT SELECT ON FUTURE TABLES IN DATABASE silently stops applying to any schema that has its own future grant. Pick one level per privilege type and stay there. Also remember future grants do not apply to objects created by cloning or by CREATE ... LIKE in some paths, so pair them with a periodic GRANT ... ON ALL reconciliation job.

Step 3: database roles for anything you share or package

Account-level roles cannot travel. Database roles live inside a database and are the right tool when the database is a share, a Native App, or a unit you want to hand to another team wholesale.

USE ROLE OBJ_OWNER;
CREATE DATABASE ROLE IF NOT EXISTS ANALYTICS.DR_SALES_READER;
GRANT USAGE ON SCHEMA ANALYTICS.SALES TO DATABASE ROLE ANALYTICS.DR_SALES_READER;
GRANT SELECT ON ALL TABLES IN SCHEMA ANALYTICS.SALES TO DATABASE ROLE ANALYTICS.DR_SALES_READER;

-- expose it to the account role graph
GRANT DATABASE ROLE ANALYTICS.DR_SALES_READER TO ROLE AR_ANALYTICS_SALES_RO;

Database roles are also what you grant into a share, which means the same object works for internal consumers and external ones without a second privilege model.

Step 4: functional roles

USE ROLE USERADMIN;
CREATE ROLE IF NOT EXISTS FR_ANALYST_SALES   COMMENT = 'Sales analyst';
CREATE ROLE IF NOT EXISTS FR_ENGINEER_SALES  COMMENT = 'Sales data engineer';

USE ROLE SECURITYADMIN;
GRANT ROLE AR_ANALYTICS_SALES_RO TO ROLE FR_ANALYST_SALES;
GRANT ROLE AR_ANALYTICS_SALES_RW TO ROLE FR_ENGINEER_SALES;
GRANT ROLE FR_ANALYST_SALES      TO ROLE FR_ENGINEER_SALES;  -- engineers see what analysts see

-- warehouses are privileges too
GRANT USAGE, OPERATE ON WAREHOUSE WH_BI_XS   TO ROLE AR_WH_BI_USE;
GRANT ROLE AR_WH_BI_USE TO ROLE FR_ANALYST_SALES;

-- keep everything under SYSADMIN
GRANT ROLE FR_ENGINEER_SALES TO ROLE SYSADMIN;

GRANT ROLE FR_ANALYST_SALES TO USER ASMITH;
ALTER USER ASMITH SET DEFAULT_ROLE = FR_ANALYST_SALES, DEFAULT_WAREHOUSE = WH_BI_XS;

Give warehouses their own access roles. Warehouse usage is the single most common cause of "role sprawl by exception", and it is also where cost accountability lives — one warehouse per functional role family makes chargeback trivial.

Step 5: service identities

Every pipeline gets its own role and its own user; no shared ETL_USER.

USE ROLE USERADMIN;
CREATE USER IF NOT EXISTS SVC_DBT_PROD
  TYPE = SERVICE                      -- service users cannot use passwords or interactive MFA
  RSA_PUBLIC_KEY = 'MIIB...'
  DEFAULT_ROLE = SR_DBT_PROD
  DEFAULT_WAREHOUSE = WH_TRANSFORM;

USE ROLE SECURITYADMIN;
GRANT ROLE AR_ANALYTICS_SALES_RW TO ROLE SR_DBT_PROD;
GRANT ROLE SR_DBT_PROD TO USER SVC_DBT_PROD;

Scope service roles to the environments they actually deploy to. A dbt role that can write to PROD and DEV will eventually write to the wrong one.

Step 6: environments and clones

Use one database per environment (RAW_PROD, RAW_DEV) rather than schema suffixes — it keeps access roles clean and makes zero-copy clones a one-liner. After a clone, re-apply grants, because clones do not inherit grants on child objects unless you use COPY GRANTS:

CREATE DATABASE ANALYTICS_DEV CLONE ANALYTICS;
-- then re-run the access-role grant script against ANALYTICS_DEV

This is the strongest argument for keeping your whole RBAC model in Git as idempotent SQL (or a tool like Terraform, Permifrost, or SnowDDL) and running it in CI. A role model you cannot re-apply from source is a role model you cannot rebuild after an incident.

Auditing what you built

Three queries that should be scheduled, not run once:

-- 1. Humans holding access roles directly (rule violation)
SELECT grantee_name, role
FROM SNOWFLAKE.ACCOUNT_USAGE.GRANTS_TO_USERS
WHERE deleted_on IS NULL AND role LIKE 'AR/_%' ESCAPE '/';

-- 2. Privileges granted straight to functional roles (rule violation)
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 NOT IN ('ROLE','DATABASE_ROLE');

-- 3. Roles nobody has used in 90 days
SELECT r.name
FROM SNOWFLAKE.ACCOUNT_USAGE.ROLES r
LEFT JOIN (
  SELECT DISTINCT role_name
  FROM SNOWFLAKE.ACCOUNT_USAGE.QUERY_HISTORY
  WHERE start_time > DATEADD('day', -90, CURRENT_TIMESTAMP())
) q ON q.role_name = r.name
WHERE r.deleted_on IS NULL AND q.role_name IS NULL;

Add a fourth check on ACCOUNTADMIN and SECURITYADMIN membership. ACCOUNTADMIN should be two or three named humans with MFA, used only for account-level work — never as anyone's default role, and never the owner of data objects.

Snowflake also exposes SNOWFLAKE.ACCOUNT_USAGE.GRANTS_TO_ROLES lineage well enough to answer "who can read this table?" transitively; if you would rather not write the recursive CTE, the Trust Center and access history views cover most audit asks.

Where this meets masking and row access policies

RBAC answers which objects; policies answer which rows and columns. The two compose cleanly when your policies key off functional roles:

CREATE MASKING POLICY mask_email AS (val STRING) RETURNS STRING ->
  CASE WHEN IS_ROLE_IN_SESSION('FR_ENGINEER_SALES') THEN val
       ELSE SHA2(val) END;

Use IS_ROLE_IN_SESSION() rather than CURRENT_ROLE() — with a role hierarchy, the current role is often a parent that inherits the privilege, and CURRENT_ROLE() will not match.

Common failure modes

1. ACCOUNTADMIN owns production objects. Anyone with the role can drop them; nobody else can manage them. Transfer ownership to OBJ_OWNER.
2. Custom roles detached from SYSADMIN. Fix with GRANT ROLE <fr> TO ROLE SYSADMIN.
3. Future grants at two levels. Schema-level silently overrides database-level.
4. Per-person roles. If you have ROLE_JBLOGGS, you have no model.
5. Grants applied by hand in the UI. They are invisible to review and lost on rebuild.
6. Clone drift. Grants are not copied by default; re-run the script.

A pragmatic rollout

You rarely get to start clean. What works on a live account: build the new model alongside the old one, migrate one domain at a time, grant the new functional role to the affected users in addition to their old role, watch QUERY_HISTORY for 30 days to confirm nothing breaks, then revoke the legacy roles. Nobody is blocked, and the audit query in step 3 tells you exactly when the old roles went quiet.

If you want a second pair of eyes on your Snowflake role model — an audit of an account that grew organically, a Git-managed rebuild, or an RBAC design that has to satisfy SOC 2 or HIPAA reviewers — get in touch. Our Snowflake architects do this on production accounts regularly and can usually map an existing account in a couple of days.