π‘οΈ Core Components: Default vs Custom Roles in Snowflake
In Snowflake, roles control what users can access and do within the account. They are central to the Role-Based Access Control (RBAC) security model. Let's explore the two main types of roles and how to use them effectively.
πΉ Default Roles (System-Defined)β
Snowflake provides a set of predefined roles with specific purposes:
| Role Name | Description |
|---|---|
| ACCOUNTADMIN | π Superuser role β full control over all Snowflake features and settings. |
| SYSADMIN | π οΈ Can create and manage all objects (warehouses, databases, schemas, tables, etc.). |
| SECURITYADMIN | π Manages users, roles, and privileges (but not data objects). |
| USERADMIN | π₯ Creates and manages users and roles. |
| PUBLIC | π Default role assigned to all users, can be used for common access. |
| ORGADMIN | π’ Manages multi-account Snowflake organizations (Enterprise tier+). |
π§ Tip: Always limit the use of
ACCOUNTADMIN. Use least privilege principles by assigning more specific roles to users.
π§© Custom Roles (User-Defined)β
Custom roles allow you to assign business-specific access like:
marketing_viewerβ Read-only access to marketing reportsetl_runnerβ Execute data pipelinesanalyst_poweruserβ Query and create objects in a specific schema
β Why Use Custom Roles?β
- Improve security with least-privilege access
- Match roles to real-world team responsibilities
- Simplify auditing and user onboarding
π Role Hierarchy:β
Roles can inherit privileges from other roles, allowing for role trees:
- Example:
data_viewer β team_lead β SYSADMIN - Enables scalable access control for larger teams.
π§ Example Hierarchy:β
- ACCOUNTADMIN
- βββ SYSADMIN
- βββ TEAM_LEAD
- βββ ANALYST
ποΈ Use Case:β
ANALYSTcan only SELECT data.TEAM_LEADinherits all ofANALYST's permissions but can also INSERT/UPDATE.SYSADMINcan manage all objects including the analysts' schemas.
π Key Differencesβ
| Feature | Default Roles | Custom Roles |
|---|---|---|
| Who creates them? | Snowflake (predefined) | You (user-defined) |
| Use Case | Admin & global functions | Functional/team-specific access |
| Editable? | β No | β Yes |
| Best For | Admin, security | Business roles, automation |
π§Ύ SQL Statements (Collated at the End)β
-- Create a custom role
CREATE ROLE finance_read_only;
-- Grant usage on database to role
GRANT USAGE ON DATABASE finance_db TO ROLE finance_read_only;
-- Grant read-only access to all tables in schema
GRANT SELECT ON ALL TABLES IN SCHEMA finance_db.reports TO ROLE finance_read_only;
-- Grant custom role to a user
GRANT ROLE finance_read_only TO USER alice;
-- Optional: build a role hierarchy
GRANT ROLE finance_read_only TO ROLE team_finance;
GRANT ROLE team_finance TO ROLE SYSADMIN;
---
π Best Practicesβ
- β Create custom roles for actual users β donβt assign SYSADMIN or ACCOUNTADMIN directly.
- β Use role hierarchy to reduce complexity in privilege management.
- β Follow least privilege principles.
- β Use default roles only for administrative operations.
A strong role strategy builds a secure, scalable Snowflake environment that's easy to manage. βοΈπ