Custom Roles and Secondary Roles

Snowflake Management, Governance & Collaboration

Emily Melhuish

Technical Curriculum Developer, Snowflake

The Problem with Flat Role Scenarios

List of analysts pointing to different privileges.

Access Roles vs Functional Roles

Split showing different between access and functional roles in terms of inherited roles

Access roles hold specific privileges on specific objects
Functional roles sit above and inherit from access roles

¹ [Snowflake: Aligning Object Access with Business Functions](https://docs.snowflake.com/en/user-guide/security-access-control-considerations#aligning-object-access-with-business-functions)

How It Works

How it works in practice

Access roles hold specific privileges
Functional roles inherit from one or more access roles
Users are assigned functional roles

Building the Hierarchy in SQL

Step 1: Create an access role and grant it object privileges

CREATE ROLE credit_read;
GRANT USAGE ON DATABASE claro_db 
TO ROLE credit_read;
GRANT USAGE ON SCHEMA claro_db.core 
TO ROLE credit_read;
GRANT SELECT ON TABLE core.credit_scores
  TO ROLE credit_read;

Step 2: Create a functional role and inherit the access role

CREATE ROLE analyst;
GRANT ROLE credit_read TO ROLE analyst;

Step 3: Assign the functional role to a user

GRANT ROLE analyst TO USER ana;

Account-roles vs Database-roles

	Account Roles	Database Roles
Scope	Span any database or schema	Scoped to a single database
Assigned to users?	Yes	No - granted to an account role instead
SQL	CREATE ROLE	CREATE DATABASE ROLE
Use Case	Represent job functions across the account	Bundle object access within a single database

Secondary Roles

Each session has a primary role set with USE ROLE <Role Name>
By default, secondary roles are set to ALL: all your assigned roles are active
Use USE SECONDARY ROLES <role_name> to activate a specific subset
USE SECONDARY ROLES NONE to restrict to the primary role only

Control secondary roles in a session

USE SECONDARY ROLES ALL;

USE SECONDARY ROLES <role_name>;

USE SECONDARY ROLES NONE;

¹ [Snowflake BCR-1692: Secondary Roles Default Change](https://docs.snowflake.com/en/release-notes/bcr-bundles/2024_08/bcr-1692)

Future Grants

Automatically grant SELECT on any new tables added to this schema

GRANT SELECT ON FUTURE TABLES
  IN SCHEMA core
  TO ROLE credit_read;

Let's practice!

Snowflake Management, Governance & Collaboration