Hello everyone! My company has been using Snowflake for about a year. The first few months we didn’t pay a lot of attention to privileges. As more people started using Snowflake and as we started having more databases, schemas, and objects, it became difficult to manage b/c we didn’t understand really how privileges in Snowflake are meant to work.
So after a little trial and error, we’ve come up with some hard-and-fast rules that help us keep Snowflake privileges simple and organized:
Don’t grant more than one role the ability to create objects in a single database.
Every custom role has to eventually roll up to sysadmin.
Never grant the database_owner roles to any other role except sysadmin.
There are lots of other things we do, but those are the most important. Some more details of how we’ve set things up can be found here.
Our system is simple, but it works very well for us. I imagine there may come a time when it doesn’t work. I’d welcome perspectives of when that might be. Perhaps others have use cases that require more sophisticated setups. What do those look like? I’m eager to learn and collect for the community some good strategies for making and keeping Snowflake privileges manageable.
Thank you for starting this convo. We had a similar experience where we learned a lot about Snowflake along the way (that’s just being nice to ourselves )
Given this is a big topic and I’m no expert, I will just comment on your first bullet point. We think about this a bit differently. Albeit, most other things you say definitely rings true for us too.
We think about the creation/ownership of objects by schema across databsaes as well. So we have a role for general purpose loading into the RAW schemas and another role for transformation which is what dbt uses to create the objects in the STG and MART schemas.
We did this because we wanted dbt to have as little privileges as possible given we wanted as many people from the business to use dbt as possible.
This is actually similar to the design you linked. But I guess the main point is, we found it useful to have a higher privileged role “Loader” to be able to perform operations across databases. Then have a lower privilege role, “Transformer” that could own and create objects within that database.
This allows the Loader role to be much more powerful which gives a lot of flexibility upstream in the pipeline given all the various options of how data can be loaded into Snowflake.
Hi @robli, thanks for the response! If I understand correctly, you have a role LOADER that has the CREATE TABLE privilege in your raw schemas across many databases. Then your transformer probably has SELECT ON FUTURE TABLES in that schema. Do you have another role in your raw schemas that can create objects as well? If you do, then your transformer would have access to them and you wouldn’t care who the owner was. Am I following?
I guess the only issue there would be if a human wanted to manually manage those tables - it would have to be a human that either switches between two roles, or has a composite role that encompasses the two. Which, if you follow rule 2, could just be sysadmin. I think that makes sense, especially since in the raw schemas or databases, you don’t really care who created it and you don’t change the schema - schema change is left up to whatever tool created it - i.e. fivetran, snowpipe loader, etc.
That makes sense. Thanks for the feedback. I’ll have to think a bit about that and let it percolate a bit. Good comment.
To summarise, that’s right the TRANSFORMER role has read-only to future resources. Having a separate READONLY role that is granted to TRANSFORMER might be a good way to achieve it because a human can have the READONLY role as well to troubleshoot. So it’s just another hop for some more flexibility.
You are right, we have a composite role. We have multiple layers of ADMIN because we don’t want the lower tier ADMIN to have the powerful “manage grants” privilege which would allow it to transfer ownership or resources because we want a human to have this role but we don’t want them transferring things around in the whole Account. Instead the lower tier ADMINs just have grants from the LOADER and TRANSFORMER which own all the tables/views/schemas they create + the READONLY. This should be plenty for a human admin (think developer).
A good outcome of this is that the LOADER role can’t see the TRANSFORMER assets (i.e. STG and MARTs) which is nice because it could be a 3rd party tool/SaaS thing that has it like you mention. But if a human wants to do things, they can have the ADMIN role (and simply have the same access as owning the LOADER and TRANSFORMER’s assets all in one).
Then you can have even higher tier ADMIN roles for tools like Terraform to automate all this for you.
I’ll add a reply that focuses on management tooling, rather than policy. Here is a tool I do not yet have experience with but I’m planning to try very soon!
Permifrost is a python tool for managing permissions on a Snowflake data warehouse in a declarative fashion.
Given the parameters to connect to a Snowflake account and a YAML file (a “spec”) representing the desired database configuration, this command makes sure that the configuration of that database matches the spec.
Thanks for dropping the link to Permifrost! We’re actively developing it (after a brief pause because I was on parental leave) and would love feedback, input, and contributions from the community.
It’s fairly opinionated about how to manage permissions, and it doesn’t currently manage object creation/deletion, but it’s working really well for our core use case now: clear understanding of permissions on existing objects.
Good conversation. A management pattern I use for Snowflake was inspired by @randy. It introduces the concept of “Object Access” (OA) roles and “Business Function” roles. OA roles are used for different types of access to objects. Given a database called “App” you may have OA roles like this:
OA_APP_CREATE_SCHEMA
OA_APP_READ
OA_APP_WRITE
These OA roles are only granted to the “Business Function” (BF) roles. Note: no users are granted to OA Roles. Rather, a BF role represents a user group defined by a function in the business. For example, you may have BF roles like:
BF_APP_DATA_ENGINEER (member of the three OA roles above)
BF_APP_DATA_ANALYST (only a member of the OA_APP_READ role granting read, but not write or create).
Users are only granted BF roles and BF roles are only granted OA roles. Enforcing these rules is essential for a cohesive and predictable environment. Adopting a consistent naming convention really helps to keep it all straight as well.
These concepts logically separate object access security from user access security by isolating the privileges into an OA or BF role. The outcome is easier management of roles and privileges for the admin and fewer (or no) holes in the security architecture.
)