Snowflake Access Control Best Practices: Securing Your Account and Data
This guide outlines key best practices and considerations for managing access control in your Snowflake account, ensuring secure access to data. It focuses on role-based access control (RBAC) for granular object access based on user roles.
Understanding the ACCOUNTADMIN Role:
Most powerful role: Grants access to account-level configuration, billing, and running SQL statements.
Not a super-user: Requires object-specific privileges for viewing/managing objects.
Hierarchy and precautions:
Other administrator roles (USERADMIN, SECURITYADMIN, SYSADMIN) are children.
Assign cautiously – limited users, multi-factor authentication (MFA), redundancy.
Avoid using for object creation – delegate to business-aligned roles.
Avoiding ACCOUNTADMIN Misuse:
Focus on account management: Use for initial setup and ongoing oversight.
Create object access roles: Align with business functions and grant to SYSADMIN.
Use alternate roles for automated scripts: Leverage SYSADMIN hierarchy for object operations.
Accessing Database Objects:
Securable objects: Tables, functions, stages, etc., within schemas and databases.
Required privileges:
USAGE on container database and schema.
Specific object privileges (e.g., SELECT for tables).
Example: Accessing 'mytable' in 'mydb.myschema' requires USAGE on both and SELECT on mytable.
Managing Custom Roles:
Initial state: Isolated, requires assignment to users and managing roles.
- ACCOUNTADMIN limitations: Cannot modify/drop objects created by custom roles.
Grant to SYSADMIN or hierarchy: Enables full control and inheritance by lower roles.
Aligning Object Access with Business Functions:
Role hierarchies: Grant roles to other roles for inheritance and flexibility.
Object access roles vs. functional roles:
- Object access roles: Grant permissions on specific objects.
- Functional roles: Group object access roles for related business functions.
Assign lower-level functions to higher-level functions as needed.
Grant top-level functional roles to SYSADMIN for comprehensive control.
Example: Managing access to fin and hr databases with different data sensitivities:
Access roles:
db_hr_r: Read-only access to hr tables.
db_fin_r: Read-only access to fin tables.
db_fin_rw: Read-write access to fin tables.
Functional roles:
accountant: Requires db_fin_rw and may need db_hr_r.
analyst: Requires both db_hr_r and db_fin_r.
Remember:
The technical difference between object access and functional roles lies in their logical use and organization.
Customize these best practices to fit your specific security needs and organizational structure.
By implementing these best practices, you can effectively manage access control in your Snowflake account, ensuring secure access to sensitive data and maintaining a robust security posture.
1. Create Roles:
Use a user administrator (USERADMIN role) or role with CREATE ROLE privilege to establish access and functional roles:
db_hr_r: Read-only access to hr database tables.
db_fin_r: Read-only access to fin database tables.
db_fin_rw: Read-write access to fin database tables.
accountant: For financial tasks, requiring db_fin_rw and potentially db_hr_r.
analyst: For data analysis, requiring both db_hr_r and db_fin_r.
2. Grant Object Privileges:
Use a security administrator (SECURITYADMIN role) or role with MANAGE GRANTS privilege to grant minimum necessary permissions to each access role (e.g., USAGE on database and schemas, SELECT on tables).
3. Construct Role Hierarchy:
Grant access roles to functional roles (e.g., db_fin_rw to accountant).
Grant functional roles to the system administrator (SYSADMIN) role for comprehensive control.
4. Assign Roles to Users:
Grant functional roles to users based on their job duties (e.g., accountant to user1, analyst to user2).
Additional Considerations:
Database Roles: Enable database owners to manage access within their databases, align with data sharing needs, and can't be directly activated in sessions.
Managed Access Schemas: Restrict grant decisions to schema owners or roles with MANAGE GRANTS privilege, enhancing security.
Future Grants: Simplify management by automatically granting privileges on newly created objects of specified types in a schema.
Query Results: Accessible only to the executing user for security reasons.
Cloned Objects: Retain privileges granted on contained objects, but not those granted on the source object itself.
Remember:
- Regularly review and update access controls as needed.
- Align role assignments with current business needs and security requirements.
