Demystifying Snowflake Access Control: Key Concepts and Framework
This topic dives into the core principles of access control in Snowflake, where granularity and flexibility reign supreme.
Snowflake's hybrid approach:
Borrows from Discretionary Access Control (DAC): Object owners grant access rights directly.
Leverages Role-based Access Control (RBAC): Access privileges are assigned to roles, then granted to users.
Essential building blocks:
Securable Object: Any entity requiring access control (default: access denied).
Role: A container for privileges, assigned to users and other roles (forming a hierarchy).
Privilege: A specific level of access granted for an object (granular control).
User: An identity recognized by Snowflake, representing individuals or programs.
The Snowflake Access Control Model:
- Access to securable objects is granted through privileges assigned to roles, which are then assigned to users or other roles (nesting creates a hierarchy).
- Each securable object has an owner who can directly grant access to roles (distinct from granting roles to users).
Key Difference:
- Snowflake prioritizes roles, unlike user-based models where users or groups directly hold rights and privileges. This fosters centralized control while offering users flexibility through assigned roles.
- Outcome:
Snowflake's hybrid access control framework delivers both strict control and adaptable access permissions, empowering users while maintaining security.
Further Exploration:
For a deeper understanding, explore the Role hierarchy and privilege inheritance section within this topic to delve into nested roles and inherited privileges.
Understanding the Hierarchy of SecurableObjects in Snowflake
In Snowflake, securable objects reside within a structured hierarchy of containers, ensuring organized access control. Here's how it unfolds:
Top-Level Container: The customer organization sits at the apex, encompassing all objects within your Snowflake account.
Databases: Each database acts as a major container, housing multiple schemas and their associated objects.
Schemas: Schemas reside within databases, providing a logical grouping for related objects.
Securable Objects: These include tables, views, functions, stages, and more, residing within schemas.
Visualizing the Structure:
Customer Organization
├── Database 1
│ ├── Schema 1
│ │ ├── Table 1
│ │ ├── View 1
│ │ └── Function 1
│ └── Schema 2
│ ├── Table 2
│ └── Stage 1
└── Database 2
├── Schema 3
│ └── ... (Additional securable objects)
└── ... (Additional schemas and their objects)
Key Points:
This clear organization streamlines access control management.
Privileges are granted at appropriate levels within this structure (e.g., database-level, schema-level, or object-level).
Understanding this hierarchy is crucial for effective security administration in Snowflake.
Understanding Roles, Privileges, and Ownership in Snowflake's Access Control
Key Concepts:
Ownership:
- A role "owns" an object by having the OWNERSHIP privilege on it.
- Ownership grants full control over the object, including granting or revoking privileges to other roles.
- Ownership can be transferred using GRANT OWNERSHIP.
Roles:
- Entities that hold privileges on objects.
- Roles are assigned to users, allowing them to perform actions on objects.
Types:
- Account roles (apply to any object in the account).
- Database roles (apply to objects within a specific database).
- Instance roles (for access to class instances).
Role hierarchies:
- Roles can be granted to other roles, creating a hierarchy.
- Privileges are inherited from higher roles in the hierarchy.
System-defined roles:
- ORGADMIN (manages organization-level operations)
- ACCOUNTADMIN (encapsulates SYSADMIN and SECURITYADMIN, highest level)
- SECURITYADMIN (manages grants and users/roles)
- USERADMIN (manages users and roles)
- SYSADMIN (creates warehouses, databases, and other objects)
- PUBLIC (granted to everyone by default)
- Custom roles can be created for specific access control needs.
Privileges:
- Define who can access and perform operations on objects.
- Managed using GRANT and REVOKE commands.
- Granted at the object level or through future grants (for new objects).
- Managed access schemas centralize privilege management with the schema owner or MANAGE GRANTS role.
Key Points:
- Snowflake's access control combines aspects of DAC and RBAC for granular control.
- Understanding roles, privileges, and ownership is crucial for effective security administration.
- Role hierarchies facilitate privilege inheritance and efficient management.
- Consider best practices for custom role creation and hierarchy structures.
Understanding Database Roles and Enforcement Models in Snowflake
Key Points Regarding Database Roles:
Limitations:
- Database roles granted to shares cannot be granted to other database roles (restriction on nesting).
- Database roles granted to other database roles cannot be granted to shares.
- Account roles cannot be granted to database roles in a hierarchy.
Activation:
- Database roles cannot be directly activated in a session.
- To leverage database role privileges, grant them to account roles, which can then be activated.
Enforcement Model with Primary and Secondary Roles:
Primary Role:
- Every session has a single "current role" or primary role.
- Determines authorization for CREATE statements (object ownership).
Determined at session initiation based on connection settings or user defaults.
Secondary Roles:
- Multiple secondary roles can be activated within a session.
- Aggregate privileges from primary and secondary roles determine SQL action authorization (excluding object creation).
- Authorization for actions other than object creation can come from primary or secondary roles.
Key Takeaways:
- Database roles offer granular access control within databases.
- Understand their limitations and activation methods for effective use.
Snowflake's enforcement model with primary and secondary roles provides flexibility in privilege management and session-level authorization.
Simplifying Role Management and Authorizing Actions with Secondary Roles
Key Benefits of Secondary Roles:
Streamlined Role Management: In organizations with numerous roles and granular authorization, secondary roles allow users to activate multiple roles within a session, simplifying access control without creating complex role hierarchies.
Cross-Database Operations: Empower users to perform SQL actions spanning multiple databases, such as cross-database joins, without requiring a single parent role with access to all involved databases.
Activation and Management:
Enabling Secondary Roles: Activate secondary roles using the USE SECONDARY ROLES command.
Viewing Active Roles: Use the CURRENT_SECONDARY_ROLES function to list active secondary roles in a session.
Changing Roles: The USE ROLE command allows switching the primary role during a session.
Authorization Considerations:
Object Creation: Only the primary role and its inherited roles are considered for authorization when creating objects.
Other Actions: For actions like querying tables, privileges from both primary and secondary roles, as well as their inherited roles, are considered.
No Super-User Privileges:
Snowflake emphasizes security by design. There's no "super-user" or "super-role" concept that bypasses authorization checks. All actions require explicit access privileges.
