In Snowflake, custom roles and role hierarchy are fundamental components of the access control framework that enable organizations to implement fine-grained security policies. Snowflake uses Role-Based Access Control (RBAC) where privileges are assigned to roles, and roles are granted to users or o…In Snowflake, custom roles and role hierarchy are fundamental components of the access control framework that enable organizations to implement fine-grained security policies. Snowflake uses Role-Based Access Control (RBAC) where privileges are assigned to roles, and roles are granted to users or other roles.
Custom roles are user-defined roles created to meet specific organizational security requirements. Unlike system-defined roles (ACCOUNTADMIN, SECURITYADMIN, SYSADMIN, USERADMIN, and PUBLIC), custom roles can be tailored to match your business needs. You create custom roles using the CREATE ROLE statement, and they can be granted specific privileges on database objects like warehouses, databases, schemas, tables, and views.
Role hierarchy is the structure where roles can be granted to other roles, creating a parent-child relationship. When Role A is granted to Role B, Role B inherits all privileges of Role A. This inheritance flows upward through the hierarchy. The ACCOUNTADMIN role sits at the top of the default hierarchy, inheriting privileges from SECURITYADMIN and SYSADMIN.
Best practices for role hierarchy include: creating custom roles for specific job functions, granting custom roles to system roles like SYSADMIN to maintain a connected hierarchy, and following the principle of least privilege. Roles that are not connected to the hierarchy cannot be used by ACCOUNTADMIN, which can create security blind spots.
The SECURITYADMIN role can manage grants globally and is typically responsible for creating and managing custom roles. Users can have multiple roles assigned but operate under one active role at a time, though they can use secondary roles to combine privileges.
Understanding role hierarchy is essential for the SnowPro Core exam, as it forms the foundation of Snowflakes security model and determines how users interact with objects and perform operations within the platform.
Custom Roles and Role Hierarchy in Snowflake
Why Custom Roles and Role Hierarchy Matter
Understanding custom roles and role hierarchy is fundamental to Snowflake security administration. This knowledge is essential for the SnowPro Core exam because it forms the backbone of Snowflake's access control system, determining who can access what data and perform which operations.
What Are Custom Roles?
Custom roles are user-defined roles created to meet specific organizational security requirements. Unlike system-defined roles (ACCOUNTADMIN, SECURITYADMIN, SYSADMIN, USERADMIN, and PUBLIC), custom roles allow administrators to implement granular, tailored access control policies.
Custom roles can be created using: CREATE ROLE role_name;
Understanding Role Hierarchy
Role hierarchy in Snowflake is a parent-child relationship structure where higher-level roles inherit all privileges from lower-level roles granted to them. This creates a chain of privilege inheritance.
Key concepts: - GRANT ROLE child_role TO ROLE parent_role - establishes hierarchy - Parent roles automatically inherit all privileges of child roles - The default hierarchy flows: PUBLIC → Custom Roles → SYSADMIN → ACCOUNTADMIN - SECURITYADMIN manages users and roles - USERADMIN can create users and roles but should grant custom roles to SYSADMIN
How Role Hierarchy Works
1. Privilege Inheritance: When Role A is granted to Role B, Role B inherits all of Role A's privileges
2. Best Practice Structure: - Create custom roles for specific functions - Grant custom roles to SYSADMIN (not ACCOUNTADMIN) - Users are assigned roles based on job functions
3. Active Role vs Granted Roles: Users can have multiple roles granted but operate under one active role at a time (can use USE ROLE to switch)
System-Defined Role Hierarchy
- ACCOUNTADMIN: Top-level role, inherits from SECURITYADMIN and SYSADMIN - SECURITYADMIN: Manages grants and can create/modify/drop roles - SYSADMIN: Creates warehouses and databases, should be parent of custom roles - USERADMIN: Creates and manages users and roles - PUBLIC: Automatically granted to all users and roles
Creating Effective Role Hierarchies
-- Create custom role CREATE ROLE data_analyst;
-- Grant privileges to custom role GRANT USAGE ON WAREHOUSE my_wh TO ROLE data_analyst; GRANT USAGE ON DATABASE my_db TO ROLE data_analyst;
-- Add to hierarchy under SYSADMIN GRANT ROLE data_analyst TO ROLE sysadmin;
-- Assign to user GRANT ROLE data_analyst TO USER john;
Exam Tips: Answering Questions on Custom Roles and Role Hierarchy
1. Remember the default hierarchy: PUBLIC is at the bottom, ACCOUNTADMIN at the top. Custom roles should sit between PUBLIC and SYSADMIN.
2. Know which system role does what: - SECURITYADMIN = security and grant management - SYSADMIN = object management (warehouses, databases) - USERADMIN = user and role creation - ACCOUNTADMIN = full account control
3. Inheritance flows upward: Child role privileges flow to parent roles, not downward.
4. Best practice questions: Custom roles should be granted to SYSADMIN, not ACCOUNTADMIN, to maintain proper separation of duties.
5. PUBLIC role: Every user and role is automatically a member of PUBLIC. Privileges granted to PUBLIC apply to everyone.
6. Watch for tricky wording: Questions may ask about who can perform an action versus who should perform it based on best practices.
7. OWNERSHIP privilege: Remember that the role creating an object owns it by default and has full control.
8. Secondary roles: Understand that users can activate secondary roles using USE SECONDARY ROLES ALL to combine privileges from multiple roles.