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.
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.
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.
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.
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.
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.
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.
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.
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.
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
| Problem | Likely cause | Fix |
|---|---|---|
| A GRANT statement fails with an insufficient privileges error | The 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 schema | USAGE 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 available | The 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 analyst | The 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 role | DEFAULT_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








