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.