2.2 Secondary Roles, Object Ownership & Delegated Administration

2.2 Secondary Roles, Object Ownership & Delegated Administration

While hierarchical RBAC governs structural permissions, real-world analytical workflows frequently involve users who require privileges spread across multiple functional domains. Snowflake addresses this through Secondary Roles, sophisticated Object Ownership semantics, and Managed Access Schemas. For the SnowPro Advanced Architect, mastering how privileges are evaluated during concurrent role execution, how object ownership transitions affect dependent grants, and how administrative delegation models are implemented is essential for passing the ARA-C01 exam.

Secondary Roles: Architecture & Session Authorization Evaluation

In standard Snowflake sessions, authorization evaluates strictly against the user's active primary role (retrieved via CURRENT_ROLE()). If an analyst belongs to both FR_SALES_ANALYST and FR_FINANCE_ANALYST, querying a joined view across both domains historically required repeatedly toggling USE ROLE <name>.

Secondary Roles solve this concurrency challenge by allowing a user to activate their entire granted role graph within a single session.

-- Activating secondary roles in a session
USE ROLE FR_SALES_ANALYST;       -- Sets the Primary Role
USE SECONDARY ROLES ALL;         -- Activates all roles granted to the current user

-- Inspecting session state
SELECT CURRENT_ROLE();            -- Returns: 'FR_SALES_ANALYST'
SELECT CURRENT_SECONDARY_ROLES(); -- Returns: {"roles": "FR_FINANCE_ANALYST,FR_MARKETING_ANALYST"}

Authorization Evaluation Mechanics

When a SQL statement executes in a session with secondary roles active, Snowflake evaluates the user's effective permissions as the union of privileges across the primary role and all active secondary roles:

• If FR_SALES_ANALYST has SELECT on sales_db.orders and FR_FINANCE_ANALYST has SELECT on finance_db.general_ledger, a single JOIN query succeeds.
• Warehouse usage is authorized if USAGE on the specified warehouse exists in either the primary role or any active secondary role.

Critical Architectural Restrictions on Secondary Roles

The ARA-C01 exam strictly tests the boundary conditions of secondary roles:

1. Object Creation (DDL) Constraint: When any CREATE <object> statement executes, the newly created object is owned exclusively by the Primary Role (CURRENT_ROLE()). Secondary roles can never own newly created objects.
2. Privilege Requirement for DDL: The privilege required to create an object (e.g., CREATE TABLE ON SCHEMA analytics.public) must reside in the Primary Role. If the CREATE TABLE privilege exists only in an active secondary role, the DDL statement fails with an authorization error.
3. Disabling Secondary Roles: Executing USE SECONDARY ROLES NONE; immediately deactivates secondary roles, returning the session to primary-role-only authorization.

Object Ownership Semantics & Ownership Transfers

In Snowflake, OWNERSHIP is a distinct, non-delegable privilege. Every securable object (database, schema, table, stage, pipe, view, warehouse) must have exactly one owning role at any given moment. The role possessing OWNERSHIP holds full discretionary control over the object, including ALTER, DROP, SELECT, INSERT, and the power to grant access to other roles.

The Mechanics of Ownership Transfer: COPY CURRENT GRANTS

When an architect transitions ownership of an object (for instance, moving ownership of production tables from an engineering development role to an automated service role), the syntax chosen has critical operational ramifications.

-- Option 1: REVOKE CURRENT GRANTS — transfer ownership and remove every outbound privilege
GRANT OWNERSHIP ON TABLE raw_data.events TO ROLE PROD_SERVICE_ROLE REVOKE CURRENT GRANTS;
-- RESULT: SELECT/INSERT previously granted to analyst roles are removed and must be re-granted.

-- Neither clause: fails if the object has outbound privileges
GRANT OWNERSHIP ON TABLE raw_data.events TO ROLE PROD_SERVICE_ROLE;
-- RESULT: error — Snowflake requires you to copy or revoke the existing grants explicitly.

Snowflake deliberately forces the decision:

• REVOKE CURRENT GRANTS enforces RESTRICT semantics: the new owner starts with no inherited outbound grants, so every dependent role loses access until privileges are re-granted.
• COPY CURRENT GRANTS keeps the existing outbound privileges, now recorded with the new owner as grantor.
• Neither clause — the statement is blocked if any outbound privileges exist on the object (role objects are an exception).
• Pipes must be paused and scheduled tasks suspended before their ownership can be transferred.

-- Architecturally Safe Ownership Transfer: Retaining Downstream Grants
GRANT OWNERSHIP ON TABLE raw_data.events 
  TO ROLE PROD_SERVICE_ROLE 
  COPY CURRENT GRANTS;
-- RESULT: PROD_SERVICE_ROLE becomes the new owner, but existing SELECT/INSERT grants on raw_data.events remain active.

Transferring Ownership with COPY CURRENT GRANTS:

[ Old Owner: DEV_ROLE ] ─── Transfers OWNERSHIP ───► [ New Owner: PROD_SERVICE_ROLE ]
          │                                                        │
          ├─── Granted SELECT to ANALYST_ROLE ─────────────────────┤ (Preserved!)
          └─── Granted INSERT to INGESTION_ROLE ───────────────────┘ (Preserved!)

Managed Access Schemas (WITH MANAGED ACCESS)

In a standard (unmanaged) Snowflake schema, access control follows Discretionary Access Control (DAC). Under DAC, whichever role creates an object automatically owns that object and possesses the unilateral authority to grant access to any other role in the account. In enterprise organizations, this creates substantial governance vulnerabilities: individual analysts or data engineers can grant ad-hoc access to sensitive data without security review.

To enforce Centralized Access Control, Snowflake provides Managed Access Schemas.

-- Creating a Managed Access Schema
CREATE SCHEMA enterprise_dw.finance WITH MANAGED ACCESS;

-- Converting an existing schema to Managed Access
ALTER SCHEMA enterprise_dw.sales ENABLE MANAGED ACCESS;

-- Reverting a schema to standard Discretionary Access Control
ALTER SCHEMA enterprise_dw.sales DISABLE MANAGED ACCESS;

Architectural Rules of Managed Access Schemas

-- Scenario in a Managed Access Schema:
-- Role DATA_LOADER owns table enterprise_dw.finance.gl_transactions
USE ROLE DATA_LOADER;

-- The following statement FAILS in a Managed Access Schema because
-- DATA_LOADER owns the table but not the schema (and lacks MANAGE GRANTS):
GRANT SELECT ON TABLE enterprise_dw.finance.gl_transactions TO ROLE FR_INTERN;

-- Remediation: Only the schema owner can grant access
USE ROLE FINANCE_SCHEMA_OWNER;
GRANT SELECT ON TABLE enterprise_dw.finance.gl_transactions TO ROLE FR_FINANCIAL_ANALYST;

Future Grants Architecture & Nuances

To prevent manual intervention whenever new tables, views, or schemas are materialized, architects use Future Grants. Future grants define a declarative authorization template applied automatically upon object creation.

-- Granting future SELECT privileges on all future tables in a schema
GRANT SELECT ON FUTURE TABLES IN SCHEMA enterprise_dw.finance 
  TO ROLE AR_FINANCE_READ;

-- Granting future privileges across all future schemas in a database
GRANT USAGE ON FUTURE SCHEMAS IN DATABASE enterprise_dw 
  TO ROLE AR_ENTERPRISE_DISCOVERY;

Critical Future Grant Semantics for Architects

1. Forward-Looking Only: Future grants never apply retroactively. If a schema already contains 50 tables, executing GRANT SELECT ON FUTURE TABLES grants privileges to the 51st table created tomorrow, while the existing 50 tables remain untouched. Existing tables must be addressed using GRANT SELECT ON ALL TABLES IN SCHEMA.
2. Schema vs Database Precedence: If conflicting future grants exist at both the database level and schema level, schema-level future grants take precedence, completely overriding database-level future grants for objects created in that schema.
3. Object Replacement Behavior: If a data pipeline executes CREATE OR REPLACE TABLE, the original table is dropped and a new table is instantiated. Explicit grants granted to the old table are destroyed, but future grants automatically re-apply to the replacement table.
4. Cloning Behavior: When a database or schema is cloned, its future grants are copied to the clone (grants on the container itself are not). When an individual table is cloned without COPY GRANTS, the new table receives the future grants defined for tables in the target schema.

Delegated Administration Patterns

Enterprise architects must design administrative delegation models that prevent bottlenecking the centralized SECURITYADMIN or ACCOUNTADMIN teams while upholding least-privilege security.

1. Delegated Warehouse Administration

Rather than granting SYSADMIN or ACCOUNTADMIN to team leads who only need to size or monitor their departmental compute resources, create a dedicated warehouse administration role:

USE ROLE SECURITYADMIN;
CREATE ROLE WH_FINANCE_ADMIN;

-- Grant specific management privileges on the departmental warehouse
GRANT USAGE, OPERATE, MODIFY, MONITOR ON WAREHOUSE wh_finance TO ROLE WH_FINANCE_ADMIN;
GRANT ROLE WH_FINANCE_ADMIN TO USER lead_finance_engineer;

This enables lead_finance_engineer to scale the warehouse up/down, alter auto-suspend limits, or manually resume/suspend compute without granting rights over other corporate warehouses.

2. Delegated User Provisioning

To allow departmental security coordinators to manage users within their domain without giving them global SECURITYADMIN privileges, grant them the ability to manage specific roles via ownership:

USE ROLE USERADMIN;
CREATE ROLE FR_MARKETING_ANALYST;
CREATE ROLE MARKETING_SECURITY_COORDINATOR;

-- Grant ownership of the role to the coordinator
GRANT OWNERSHIP ON ROLE FR_MARKETING_ANALYST TO ROLE MARKETING_SECURITY_COORDINATOR;

-- MARKETING_SECURITY_COORDINATOR can now grant FR_MARKETING_ANALYST to departmental users
-- without possessing global MANAGE GRANTS or ACCOUNTADMIN rights