A standard model for Snowflake roles and privileges

by trevorscode | posted in: Snowflake
from: https://trevorscode.com/toward-a-standard-model-for-snowflake-roles-and-privileges/

Here is what a standard model could look like for Snowflake roles and privileges. Let me explain each piece. I welcome feedback.

The code for implementing this type of model can be found on my github.

/*
Reasoning for this design is described here:
https://trevorscode.com/toward-a-standard-model-for-snowflake-roles-and-privileges/
For an overview of Snowflake Privileges: 
https://trevorscode.com/comprehensive-tutorial-of-snowflake-privileges-and-access-control/
For a description of what privileges are needed to complete certain tasks:
https://trevorscode.com/what-snowflake-privileges-do-i-need-to-do-insert-command-here/
*/



/*
WARNING: Make sure if you run these drops that you aren't nuking something you want to keep
use role accountadmin;
drop database if exists db_1;
drop role if exists owner_db_1;
drop role if exists reader_db_1;
drop role if exists monitor_all;
drop role if exists reader_all;
*/



--Set this up once per account----------------------------------------------------------
use role accountadmin;

create role reader_all;

create role monitor_all;
grant monitor execution, monitor usage on account to role monitor_all;
grant role reader_all to role monitor_all;
grant role monitor_all to role sysadmin;




--For a New Database--------------------------------------------------------------------------

create role owner_db_1;
grant execute task on account to role owner_db_1;
grant role owner_db_1 to role sysadmin;

create role reader_db_1;
grant usage, monitor on warehouse xs_wh to role reader_db_1;
grant role reader_db_1 to role owner_db_1;
grant role reader_db_1 to role reader_all;


create database db_1;
grant ownership on database db_1 to role owner_db_1;

grant monitor, usage on database db_1 to role reader_db_1;

grant monitor on future tasks in database db_1 to role reader_db_1;
grant usage on future schemas in database db_1 to role reader_db_1;
grant USAGE on future functions in database db_1 to role reader_db_1;
grant USAGE on future PROCEDURES in database db_1 to role reader_db_1;
grant select on future TABLES in database db_1 to role reader_db_1;
grant monitor on future TASKS in database db_1 to role reader_db_1;
grant select on future VIEWS in database db_1 to role reader_db_1;
grant usage, read on future stages in database db_1 to role reader_db_1;
grant usage on future file formats in database db_1 to role reader_db_1;
grant select on future streams in database db_1 to role reader_db_1;

--The _temp schema is useful for readers so they can create temporary tables
use role owner_db_1;
create schema db_1._temp;
grant create table on schema db_1._temp to role reader_db_1;



--Create a new schema in the new database -----------------------------------------------------
use role owner_db_1;
create schema db_1.db_1_schema;
use schema db_1.db_1_schema;

If you want to learn more about Snowflake privileges, see my Comprehensive Tutorial of Privileges and Access Control in Snowflake.

The numbered points in the diagram below correspond w/ my notes under the diagram.

standard snowflake model

1. Every database should have a single owner role that owns all objects in that database. Nobody else (besides sysadmin and accountadmin) should have the ability to create objects in that database. So if you have a database called db_1, then you should have a single roll called owner_db_1 that has created and owns all the objects in that database. The only exception is in the _temp schema (see point 4 below)
   • If you allow more than one role to create and own objects in a database, you can easily end up in a scenario where the owner role no longer has control over the database it owns. See here for more info on that: Snowflake Permission Problems #3: Database owner can’t view all objects in the database
2. The Execute Task privilege is an account-level privilege that allows the owner role to alter task resume any task it owns. The ownership privilege allows the owner role to create and own everything in the database. If you want to know more about which privileges allow which actions, see my post on that.

3. There should be at least one reader role for each database – possibly more. Unlike the owner role, there can be many reader roles per database, and you can create reader roles that span multiple databases. I try to make the main reader role as powerful as possible without the ability to create or make changes to objects. This means I want them to be able to use, show, monitor, and describe all object in the database.

4. Most often, the reader role is an analyst. I want analysts to be able to create temporary tables. But there is no privilege to create temp tables separate from the privilege to create tables. My way around this is to have the owner role create a schema called _temp (one _temp schema per db), which the reader roles have the privilege of writing to. I also create a procedure and a task that cleans this area up periodically (i.e. after 7 days since object creation). See also: Grant the creation of temporary tables in Snowflake

5. I have a role called reader_all that makes it easy for people to generally have read-access to all databases at one time. You could optionally create other custom reader roles that are a composition of each database’s reader roles. Remember, never compose multiple owner roles into a single role, because if you do, you’ll have created another role that can be the owner of an object in your database. And that does not end well for reasons linked to above.

6. Sometimes as an administrator you want to give people rights to see account information, but not change it. For example, you want an account that can see or query credits or storage used. I use a role like this for some reporting I do to chart usage stats.

7. The only reason I grant the reader role to the owner role is because I want the owner to have access to the tables that the reader role might create in _temp. It’s also convenient to manage warehouse usage at the reader level, though you might want to split that out.

8. Remember that all roles need to either directly or indirectly roll up into sysadmin (and therefore accountadmin).

   • See also:
     Snowflake Permission Problems #6: Sysadmin doesn’t have access to a database or schema

In summary, all privilege schemas are subjective to a certain extent. I’ve shown you how I like to roll mine. But I do think there are three cardinal rules you always need to follow when designing your privilege scheme:

• 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 some good objections people could make about how I run privileges. Some of those could include:

• Objection 1: Owner role owning the database. The alternative is for the SysAdmin to own the database, and for the owner to own the schemas. I think that would make sense. It’s just not my preference. I’d rather each database have one or two schemas and be restricted in its domain than have a single database with a bunch of different schemas, each of which we have to control access to.

• Objection 2: Giving owners the ability to resume/start tasks. Some people don’t like this b/c they want their sysadmin to know and approve the scheduled use of costly resources. I understand that, but I’d rather have my sysadmin monitor job schedules after-the-fact and accept the risk that someone might make a mistake in scheduling that costs us a little extra money for a few days.

• Objection 3: Giving the READER role the USAGE privilege on procedures. This is the only way to give them the ability to get_ddl() on the object, which is very important to me. To compensate for this, we make sure all procedures run under the caller’s privileges. Unfortunately, I don’t know of any way to make sure that always happens so for now, it’s an on-your-honor manual check. Not for everybody, I know, but it’s my preference.

• Objection 4: I don’t use the securityadmin role. I really don’t see a compelling reason to use this role since the people who have accountadmin where I work will also have securityadmin. For us, it’s the same people, and we started out setting up roles and users through accountadmin, and I can’t see a downside to that except that we’re violating the least privileges principle. I don’t see this as an egregious violation that matters in any material way for my organization.

• Objection 5: Your reader role is too powerful! This may be true for some organizations, but I like having a reader role that can be used by developers and advanced analysts who want to know things are working. Also, in an overarching design document like this one, it’s nice to see what the “full monte” looks like and then you can decide which ones to remove.