Skip to main content

πŸ›‘οΈ 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 NameDescription
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 reports
  • etl_runner – Execute data pipelines
  • analyst_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:​

  • ANALYST can only SELECT data.
  • TEAM_LEAD inherits all of ANALYST's permissions but can also INSERT/UPDATE.
  • SYSADMIN can manage all objects including the analysts' schemas.

πŸ“Œ Key Differences​

FeatureDefault RolesCustom Roles
Who creates them?Snowflake (predefined)You (user-defined)
Use CaseAdmin & global functionsFunctional/team-specific access
Editable?❌ Noβœ… Yes
Best ForAdmin, securityBusiness 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. β„οΈπŸ”