Understanding Access Control in Snowflake
Snowflake is a pretty awesome data warehouse. I recently implemented the access control policy for my organization. Access control often can be an afterthought when trying to get things up and running quickly. But for a large organization, it’s crucial to start on a solid foundation otherwise things can become very difficult to manage.
In this post, I’ll share what I learned through this process.
Snowflake Basics
Let’s understand some basic Snowflake concepts before getting into our access control policies here.
Securable Object – an entity to which access can be granted (schemas, tables, etc.), here is a full list of Snowflake securable objects.
Privilege – specific permission to a securable object, here is a full list of Snowflake privileges.
Role – an entity which privileges can be granted. Roles are then assigned to users or other roles.
User – an identity to Snowflake, either a person or program.
These 4 concepts tie together like this:
Users get roles granted to them, roles have privileges granted to them, privileges allow access to securable objects.
Ownership
Every securable object is owned by a single role. The default owner is the role that created the object. The owner of an object will have all privileges on the object. Ownership can be transferred.
Certain actions on an object can only be done by the object owner. We try to ensure tables are created by the role that will operate on them as ALTER TABLE can only be executed by the object owner.
Roles
A key concept for role-based access control and our role hierarchy detailed below is the distinction between access roles and functional roles. This is strictly a logical distinction. To Snowflake a role is a role, but we use this logical separation of roles to make it easier to manage our roles.
Access Roles – only have privileges on objects and are not assigned to users directly
Functional Roles – are composed only of access roles and are granted to users
System Roles – these are managed by Snowflake, the SYSADMIN role is granted access to all of our custom roles
Think of the distinction between roles like this:
Access roles have privileges on securable objects. These access roles are then granted to functional roles. Functional roles are granted to users.
A functional role inherits all the privileges of all the access roles granted to it. A user is then granted access to a functional role or multiple functional roles. When logged in as a user, you can only assume one role at a time. A user’s default role should be their functional role, so this way you have access to all the inherited privileges at once.
Role Hierarchy
As mentioned above roles can inherit privileges from all the roles granted to them. This allows us to form role hierarchies. Snowflake provides a great example here of how to do this in practice. I largely worked off of this documentation when implementing it for my organization. But here are the basics.
Create your access roles where only privileges are granted to them. These are the lowest tier of your role hierarchy. For example, for each schema in your database, you can create a MY_SCHEMA_READ_ONLY and MY_SCHEMA_READ_WRITE role.
The privileges would be assigned as follows:
MY_SCHEMA_READ_ONLY
USAGEon the databaseUSAGEon the schemaSELECTon future tables
MY_SCHEMA_READ_WRITE
USAGEon the databaseUSAGEon the schemaSELECT,INSERT,UPDATE,DELETE,TRUNCATEon future tables
This access role could then be granted to a functional role. The functional role would inherit all privileges of the access role. So a DEVELOPER role could be granted MY_SCHEMA_READ_WRITE. The DEVELOPER role would then be granted to any developer user.
Snowflake provides a full list of privileges you can grant on each securable object.
Next Steps
Think about the needs of your organization. Sketch out what the role hierarchy would look like. Determine what privileges and granularity your access roles need. List out the functional roles that apply to how your teams and services are organized. With this plan in mind codify the permissions scheme, ideally using something like Terraform.
Resources