Skip to content

Grokem · https://www.grokem.com/data/data-platforms/data-warehouses/how-to-set-up-snowflake-rbac-with-least-privilege-roles-step-by-step

Data WarehousesTutorialIntermediate level

How to Set Up Snowflake RBAC With Least-Privilege Roles, Step by Step

Build a small Snowflake role hierarchy with read and write access roles, functional roles and one test user. You finish with grants you can list and a failed write that proves least privilege works.

Krishna Sharma

Technical Writer

Published Updated
  • 30 min to complete
  • 0 sec

Tested with
  • Snowflake
  • SQL
Your progress
  1. How to Set Up Snowflake RBAC With Least-Privilege Roles, Step by Step5 min(finished)
    0%

Before you start

4
  • A Snowflake account where you can use the SYSADMIN and SECURITYADMIN roles (or ACCOUNTADMIN)
  • An existing virtual warehouse; you replace YOUR_WAREHOUSE in the code with its name
  • A SQL worksheet or SQL client connected to the account
  • Basic knowledge of SQL and of what a database, schema and table are

Snowflake access control is built on roles. You grant privileges to roles, and you grant roles to users or to other roles. A user never gets a table privilege directly.

In this tutorial you build a small, reusable pattern:

  • Access roles hold privileges on objects (SALES_READ, SALES_WRITE).
  • Functional roles map to jobs and receive access roles (ANALYST, ETL_LOADER).
  • Users receive only a functional role.

The schema uses managed access, so privilege grants stay in the hands of a central administrator role instead of every object owner. The exact privilege list for your own objects will differ. Check Snowflake's official access control documentation for the full list of privileges and grant syntax.

The code uses the names ANALYTICS_DB, SALES and ORDERS. Rename them freely, but keep them consistent across steps.

ANALYST_JANEuserANALYSTfunctional roleSALES_READaccess roleANALYTICS_DB.SALESmanaged access schemaETL_LOADERfunctional roleSALES_WRITEaccess roleUSAGE, SELECTINSERT, UPDATE,DELETE
Users receive functional roles, which receive access roles that hold the privileges.

Step 1 of 9: Create a managed access schema with a test table

Switch to SYSADMIN and create the database, a schema with managed access, and one table. Managed access means object owners cannot grant privileges on their objects. Only the schema owner or a role with the MANAGE GRANTS privilege can, so grants stay central and auditable.

If you use ACCOUNTADMIN here instead, the objects work the same, but you break least privilege for no gain.

sql
USE ROLE SYSADMIN;

CREATE DATABASE IF NOT EXISTS ANALYTICS_DB;

CREATE SCHEMA IF NOT EXISTS ANALYTICS_DB.SALES WITH MANAGED ACCESS;

CREATE TABLE IF NOT EXISTS ANALYTICS_DB.SALES.ORDERS (
  ORDER_ID NUMBER,
  AMOUNT   NUMBER(10,2)
);

Step 2 of 9: Create the access roles as SECURITYADMIN

Switch to SECURITYADMIN and create one role for reading and one for writing. Role administration belongs to SECURITYADMIN (or ACCOUNTADMIN), so you keep object creation and permission management in separate hands.

sql
USE ROLE SECURITYADMIN;

CREATE ROLE IF NOT EXISTS SALES_READ;
CREATE ROLE IF NOT EXISTS SALES_WRITE;

Step 3 of 9: Grant read privileges to SALES_READ

Grant USAGE on the database and schema, then SELECT on the tables. A role needs USAGE on every container above a table before the table privilege has any effect. Use ALL TABLES for existing tables and FUTURE TABLES so new tables are covered without another grant.

The warehouse grant lets the role run queries. It is the only compute privilege this role gets.

sql
GRANT USAGE ON DATABASE ANALYTICS_DB TO ROLE SALES_READ;
GRANT USAGE ON SCHEMA ANALYTICS_DB.SALES TO ROLE SALES_READ;
GRANT SELECT ON ALL TABLES IN SCHEMA ANALYTICS_DB.SALES TO ROLE SALES_READ;
GRANT SELECT ON FUTURE TABLES IN SCHEMA ANALYTICS_DB.SALES TO ROLE SALES_READ;
GRANT USAGE ON WAREHOUSE YOUR_WAREHOUSE TO ROLE SALES_READ;

Step 4 of 9: Grant write privileges to SALES_WRITE

Grant only the data-change privileges to SALES_WRITE, then grant SALES_READ to it. The write role inherits USAGE and SELECT through the role grant, so you do not repeat them and the two roles cannot drift apart.

Note what is missing: no CREATE TABLE, no DROP. A loader that only inserts and updates rows does not need them.

sql
GRANT INSERT, UPDATE, DELETE ON ALL TABLES IN SCHEMA ANALYTICS_DB.SALES TO ROLE SALES_WRITE;
GRANT INSERT, UPDATE, DELETE ON FUTURE TABLES IN SCHEMA ANALYTICS_DB.SALES TO ROLE SALES_WRITE;

GRANT ROLE SALES_READ TO ROLE SALES_WRITE;

Step 5 of 9: Create functional roles and attach the access roles

Create one role per job and grant each the access role it needs. Functional roles let you onboard a person with one grant and change what a job can do in one place.

The analyst gets read access only. The loader gets write access, which already includes read.

sql
CREATE ROLE IF NOT EXISTS ANALYST;
CREATE ROLE IF NOT EXISTS ETL_LOADER;

GRANT ROLE SALES_READ  TO ROLE ANALYST;
GRANT ROLE SALES_WRITE TO ROLE ETL_LOADER;

Step 6 of 9: Create a test user and assign the analyst role

Create a user with a temporary password, set the default role, and grant the functional role. Setting DEFAULT_ROLE means the user starts a session with the lowest-privilege role they hold. MUST_CHANGE_PASSWORD forces a new password at first login. Replace the password placeholder with your own temporary value.

sql
CREATE USER IF NOT EXISTS ANALYST_JANE
  PASSWORD = 'YOUR_TEMPORARY_PASSWORD'
  MUST_CHANGE_PASSWORD = TRUE
  DEFAULT_ROLE = ANALYST
  DEFAULT_WAREHOUSE = YOUR_WAREHOUSE;

GRANT ROLE ANALYST TO USER ANALYST_JANE;

Step 7 of 9: List the grants to check the hierarchy

Run the SHOW GRANTS statements as SECURITYADMIN. Reading the grants back confirms that each role holds exactly what you intended and nothing more.

sql
SHOW GRANTS TO ROLE SALES_READ;
SHOW GRANTS TO ROLE SALES_WRITE;
SHOW GRANTS TO ROLE ANALYST;
SHOW GRANTS TO USER ANALYST_JANE;

Step 8 of 9: Test the user: allow a read and block a write

Log in as ANALYST_JANE in a separate session, set a new password when asked, and run the statements below. The SELECT must succeed and the INSERT must fail. A denied write is the proof that least privilege is active.

sql
SELECT CURRENT_ROLE();

SELECT * FROM ANALYTICS_DB.SALES.ORDERS;

INSERT INTO ANALYTICS_DB.SALES.ORDERS (ORDER_ID, AMOUNT) VALUES (1, 9.99);

Step 9 of 9: Repeat the test with the loader role

Create a second user the same way, with DEFAULT_ROLE = ETL_LOADER and GRANT ROLE ETL_LOADER TO USER ..., or ask your administrator to grant ETL_LOADER to a test user. Run the same INSERT. It must now succeed, which shows the write role works and that the difference comes from the role alone.


Expected result

The SHOW GRANTS output for SALES_READ lists USAGE on the database, schema and warehouse, plus SELECT on the tables. SALES_WRITE lists INSERT, UPDATE and DELETE and shows SALES_READ granted to it. ANALYST_JANE shows only the ANALYST role. In the user session, CURRENT_ROLE() returns ANALYST, the SELECT returns rows (or an empty result), and the INSERT fails with an insufficient privileges error. The same INSERT succeeds under ETL_LOADER.

Troubleshooting

ProblemLikely causeFix
A GRANT statement fails with an insufficient privileges errorThe active role neither owns the object nor holds the MANAGE GRANTS privilege. In a managed access schema, even the object owner cannot grant privileges on it.Run USE ROLE SECURITYADMIN (or ACCOUNTADMIN) before the grant, then rerun the statement.
The test user cannot see the database or schemaUSAGE is missing on the database or the schema, or the functional role was not granted to the user.Rerun the USAGE grants in step 3 and check SHOW GRANTS TO USER ANALYST_JANE.
Queries fail because no warehouse is availableThe role has no USAGE on a warehouse, or the user has no default warehouse.Grant USAGE on the warehouse to SALES_READ and set DEFAULT_WAREHOUSE, or run USE WAREHOUSE in the session.
A table created later cannot be read by the analystThe ALL TABLES grant only covers tables that exist when you run it.Add the FUTURE TABLES grant from step 3, and run the ALL TABLES grant again for tables created before it.
The analyst session starts in the wrong roleDEFAULT_ROLE is unset or points to a role the user does not hold.Grant the role to the user, then set DEFAULT_ROLE on the user, or run USE ROLE ANALYST in the session.

Frequently asked questions

Which role should I use to create roles and grant privileges?

Use SECURITYADMIN or ACCOUNTADMIN. Use SYSADMIN to create databases, schemas and tables, so object creation and permission management stay separate.

Can I grant table privileges directly to a user?

No. Snowflake grants privileges to roles, and you grant roles to users or to other roles. That is why this pattern uses access roles and functional roles.

Why use a managed access schema?

It stops object owners from granting privileges on their own objects. Only the schema owner or a role with MANAGE GRANTS can, which keeps access decisions central.

Do database roles replace this pattern?

Database roles are another way to group privileges inside a database. This tutorial does not use them. Check Snowflake's official documentation for their syntax and limits.

Next steps

  • Add a second schema and repeat the access role pattern for it.
  • Read about database roles in Snowflake's official documentation and decide whether they suit your sharing needs.
  • Review your existing grants with SHOW GRANTS and remove any direct privileges you find on users' roles.
  • Plan which administrator roles should keep the right to grant privileges.

From Grokem: https://www.grokem.com/data/data-platforms/data-warehouses/how-to-set-up-snowflake-rbac-with-least-privilege-roles-step-by-step

Finished this article?

How did this strike you?

Be the first to react – it is anonymous.

Community

Questions about this?

Ask in Questions and help and get help from other readers.

Ask a questionBrowse Questions and help

Comments

No comments yet. Share what worked for you, or ask a question.

Keep learning

Picked for this article

Related reading, discussions and learning paths on the same subject.

All in Data Warehouses

Learning paths

Learning pathIntermediate

SQL for data work

From removing duplicates and window functions to modelling for reporting, cohort analysis, indexes and query plans: the SQL a working analyst or engineer uses every week, each lesson with queries that were run.

8 lessonsabout 90 minStart

Choose what we may use. You can change this at any time with “Privacy choices” at the foot of every page. Details are in the Cookie policy and the Privacy policy.

Strictly necessary

Remembers your theme, the lessons you marked as done and this choice. It stays on your device, tracks nothing and cannot be switched off.

Always on
What exactly is collected, and why

Google Analytics

Collects
The pages you view, your device and browser type, your language, how you reached the site and your approximate location (country and city, worked out from your IP address).
Why
To understand which pages and lessons are used, so we can improve them.
Stored
Two cookies, _ga and _ga_<id>, for up to 2 years. Google acts for us; the data is processed by Google, including outside your country.

Grokem’s own counts

Collects
The pages you open and how long you stay, how far you scroll, where you came from, your device type, browser, screen size, language and time zone. Your IP address is turned into a code that changes every day; it is never stored as it is.
Why
To see how long lessons are read and where readers leave. Not shared with anyone.
Stored
A random visitor ID, a visit ID and the time of your last page view in your browser’s storage (gr_vid, gr_sid, gr_last). No name or email is attached.