2.1 Role-Based Access Control Architecture & Hierarchy

2.1 Role-Based Access Control Architecture & Hierarchy

In Snowflake, security governance is founded upon Role-Based Access Control (RBAC): privileges are granted to roles, and roles are granted to other roles or to users. Snowflake's access control model also includes Discretionary Access Control (DAC) — each object has an owner that can grant access — and User-Based Access Control (UBAC), where privileges can be granted directly to a user; privileges granted directly to users are considered only when the session uses USE SECONDARY ROLES ALL. Enterprise designs still route almost all access through roles. To design an enterprise architecture that satisfies strict regulatory compliance, scales across thousands of data consumers, and avoids administrative paralysis, a SnowPro Advanced Architect must understand both Snowflake's built-in system role hierarchy and the architectural patterns required for custom role modeling.

System-Defined Roles Architecture & Segregation of Duties

Every Snowflake account is provisioned with a set of immutable, system-defined roles. These roles establish a baseline division of responsibilities between security administration, user provisioning, and operational workload management.

-- Viewing the built-in system role hierarchy and grants
SHOW GRANTS TO ROLE SECURITYADMIN;
SHOW GRANTS TO ROLE USERADMIN;
SHOW GRANTS TO ROLE SYSADMIN;
SHOW GRANTS TO ROLE ACCOUNTADMIN;

The Critical USERADMIN vs SECURITYADMIN Separation

A foundational security design tenet tested on the ARA-C01 exam is the architectural separation between USERADMIN and SECURITYADMIN:

• USERADMIN can create entities: CREATE USER jdoe; and CREATE ROLE analyst_fr;.
• Because USERADMIN owns the roles it creates, it can grant analyst_fr to jdoe — the owner of a role may grant that role.
• However, USERADMIN cannot grant object privileges on objects it does not own (such as SELECT on a finance table) to analyst_fr, because it lacks the global MANAGE GRANTS privilege.
• SECURITYADMIN (which holds MANAGE GRANTS) can grant or revoke any privilege on any object, which is why it is the governance role for wiring access roles to objects. This separation means an identity administrator can create users and roles but cannot, on its own, give those roles access to data it does not own.

Custom Role Modeling: The Access Role vs Functional Role Pattern

Enterprise architectures must never grant object privileges directly to business users or even directly to business-department roles. Doing so creates an unmanageable web of thousands of discrete grants, resulting in security drift, privilege escalation, and audit failures. The industry standard pattern for Snowflake RBAC is the two-tier Access Role (AR) / Functional Role (FR) model.

+-------------------------------------------------------------+
|                      Business Users                         |
+-------------------------------------------------------------+
                              │ (Role Assignment)
                              ▼
+-------------------------------------------------------------+
|           Functional Roles (FR) - Business Personas         |
|      (e.g., FR_FINANCE_ANALYST, FR_DATA_ENGINEER)           |
+-------------------------------------------------------------+
                              │ (Role Inheritance)
                              ▼
+-------------------------------------------------------------+
|            Access Roles (AR) - Object Privileges            |
|  (e.g., AR_EDW_FINANCE_READ, AR_EDW_FINANCE_READWRITE)     |
+-------------------------------------------------------------+
                              │ (Direct Object Grants)
                              ▼
+-------------------------------------------------------------+
|                     Snowflake Objects                       |
|               (Databases, Schemas, Tables)                  |
+-------------------------------------------------------------+

1. Access Roles (Object Access Tier)

• Purpose: Access roles represent specific, granular permissions on specific database objects. They are defined purely by what they can do to an object, not who does it.
• Naming Convention: AR_<DATABASE>_<SCHEMA>_<PERMISSION> (e.g., AR_EDW_SALES_R for read-only, AR_EDW_SALES_RW for read-write).
• Rules: Access roles are never assigned directly to users. They contain only object grants (USAGE, SELECT, INSERT, TRUNCATE).

-- Step 1: Create Access Roles via SECURITYADMIN
USE ROLE SECURITYADMIN;

CREATE ROLE AR_FINANCE_DB_READ;
CREATE ROLE AR_FINANCE_DB_WRITE;

-- Step 2: Grant object-level privileges to Access Roles
GRANT USAGE ON DATABASE finance_db TO ROLE AR_FINANCE_DB_READ;
GRANT USAGE ON SCHEMA finance_db.ap TO ROLE AR_FINANCE_DB_READ;
GRANT SELECT ON ALL TABLES IN SCHEMA finance_db.ap TO ROLE AR_FINANCE_DB_READ;
GRANT SELECT ON ALL VIEWS IN SCHEMA finance_db.ap TO ROLE AR_FINANCE_DB_READ;

-- Write access role inherits read permissions and adds DML
GRANT ROLE AR_FINANCE_DB_READ TO ROLE AR_FINANCE_DB_WRITE;
GRANT INSERT, UPDATE, DELETE ON ALL TABLES IN SCHEMA finance_db.ap TO ROLE AR_FINANCE_DB_WRITE;

2. Functional Roles (Business Persona Tier)

• Purpose: Functional roles represent business job titles, organizational departments, or service identities (e.g., FR_FINANCIAL_ANALYST, FR_DATA_ENGINEER, FR_DBT_INGESTION).
• Rules: Functional roles are granted one or more Access Roles, plus usage on a dedicated virtual warehouse. Functional roles are the only custom roles granted directly to end users or synchronized via SCIM directory groups.

Enforcing Role Hierarchy up to SYSADMIN & Orphaned Roles

A critical failure mode in Snowflake access modeling occurs when a custom role is created and assigned to users without linking it into the higher-level management hierarchy. This creates an orphaned role.

The Orphaned Role Dilemma

If USERADMIN creates FR_MARKETING_ANALYST and assigns it to users, but fails to grant FR_MARKETING_ANALYST to SYSADMIN:

1. Users operating under FR_MARKETING_ANALYST can create objects (tables, views, temporary stages) within their schemas.
2. Because the objects are owned by FR_MARKETING_ANALYST, SYSADMIN cannot see, alter, drop, or manage those objects without explicit grants.
3. The platform engineering team operating under SYSADMIN loses administrative oversight, backup validation, and cleanup capability.
4. The only way to remediate without restructuring the role is for ACCOUNTADMIN to intervene, violating the principle of least privilege.

-- The Golden Rule of Snowflake Custom Role Architecture:
-- Always roll every Functional Role up to SYSADMIN
USE ROLE SECURITYADMIN;

CREATE ROLE FR_FINANCIAL_ANALYST;
GRANT ROLE AR_FINANCE_DB_READ TO ROLE FR_FINANCIAL_ANALYST;
GRANT USAGE ON WAREHOUSE wh_finance TO ROLE FR_FINANCIAL_ANALYST;

-- Crucial: Enforce administrative hierarchy
GRANT ROLE FR_FINANCIAL_ANALYST TO ROLE SYSADMIN;

-- Grant functional role to business users
GRANT ROLE FR_FINANCIAL_ANALYST TO USER jane_doe;

                        ACCOUNTADMIN
                         ▲        ▲
                         │        │
            ┌────────────┘        └────────────┐
            │                                  │
      SECURITYADMIN                         SYSADMIN
            ▲                                  ▲
            │                                  │ (Roll up)
        USERADMIN                     FR_FINANCIAL_ANALYST
                                               ▲
                                               │ (Inheritance)
                                      AR_FINANCE_DB_READ

By rolling FR_FINANCIAL_ANALYST up to SYSADMIN, any user activating SYSADMIN inherits all privileges possessed by FR_FINANCIAL_ANALYST, enabling platform engineers to troubleshoot and manage downstream objects seamlessly.

Principle of Least Privilege & Securing ACCOUNTADMIN

The ACCOUNTADMIN role is the most privileged entity in a Snowflake account, possessing combined capabilities of infrastructure modification, grant control, security policy bypass, and billing management. On the ARA-C01 exam, scenarios testing the containment and protection of ACCOUNTADMIN are pervasive.

Architectural Rules for Securing ACCOUNTADMIN

1. Mandatory Multi-Factor Authentication (MFA): Every human user granted ACCOUNTADMIN should be enrolled in MFA (passkey, authenticator app/TOTP, or Duo). Snowflake is rolling out mandatory MFA for all password sign-ins, and accounts created after the 2024_08 behavior change bundle already require human password users to enroll.
2. At Least Two, but Few, Administrators: Snowflake recommends assigning ACCOUNTADMIN to at least two users (password resets for this role follow a strict procedure that can take up to two business days) while keeping the group small to limit exposure.
3. Prohibition on Automated Workloads: Automated ETL pipelines, orchestration engines (e.g., Airflow, dbt), BI reporting platforms (e.g., Tableau, PowerBI), and cloud connectors must never be configured with ACCOUNTADMIN. Service accounts must operate under tailored functional roles.
4. Default Role Restrictions: Avoid setting a user's DEFAULT_ROLE to ACCOUNTADMIN, and avoid using it to create objects. When an administrator logs into Snowsight or executes SnowSQL, their session should initialize in a lower-privilege role (such as SYSADMIN or a custom persona role). Elevating to ACCOUNTADMIN must be an explicit, conscious action (USE ROLE ACCOUNTADMIN;).

-- Hardening user configuration against default administrative escalation
ALTER USER admin_ranchen SET 
  DEFAULT_ROLE = SYSADMIN,
  DEFAULT_WAREHOUSE = WH_ADMIN,
  MINS_TO_BYPASS_MFA = 0;

Common Architectural Scenarios & Exam Traps

• Exam Trap: Granting MANAGE GRANTS to Functional Roles: When a team asks for the ability to manage grants in their own sandbox database, never grant the global MANAGE GRANTS privilege to their functional role. Global MANAGE GRANTS allows that role to grant ANY privilege on ANY object account-wide, effectively turning that role into a SECURITYADMIN. Instead, make the role the owner of the sandbox schema or utilize a Managed Access Schema.
• Exam Trap: Misunderstanding Role Inheritance Direction: Remember that GRANT ROLE role_a TO ROLE role_b; means role_b inherits all privileges of role_a. A common mistake is reversing the syntax, which grants administrative privileges to lower-tier roles.