In order to create a role in Snowflake, you can run the following command:
CREATE_ROLE <role_name>;
In order to grant permissions to a role in Snowflake, you can run the command:
GRANT <privledge> to ROLE <role_name>;
Lastly, make sure you assign your roles to the appropriate user. You can do so using this:
GRANT ROLE <role_name> to USER <user_name>;
Snowflake’s permission hierarchy
You need to grant certain permissions to the database, schema, tables/views, and future tables/views. Snowflake’s permissions are unique in that you can’t assign a permission to the database and expect it to also apply for the schemas and tables/views within that database.
Grant permissions to databases
First, let’s review the most important permission you can grant a role on the database level.
MONITOR allows a role to see details on an object; you will want to grant this to all roles who you want to see the database.
USAGE allows a role to use a database; you will need to grant this to any roles that wish to query the database.
CREATE grants a role the ability to create an object within the database. This is important to assign to your TRANSFORMER role on databases “BASE”, “DATA_MART_DEV”, and “DATA_MART_PROD”. Your LOADER role should be granted this permission on “RAW” and your ANALYZER role on “RDA”.
Grant permissions to schemas
Now, let’s review important permissions on the schema level.
Once again, you need to grant the MONITOR privilege on schemas within your databases to the roles.
USAGE allows a role to use a schema; you will need to grant this to any roles that wish to query the schema.
CREATE also exists on the schema level. You will need to assign this to all the roles for the schemas within the databases I just mentioned above.
Grant permissions to tables and views
Lastly, here is where things get different. Tables and views have unique privileges from databases and schemas.
SELECT allows a role to select from a table or view. You will want to assign this to every role who is allowed to Snowflake query a certain object.
INSERT and DELETE are all permissions that should be given to your TRANSFORMER role on “DATA_MART_DEV” and “DATA_MART_PROD”. Since this is the role assigned to the dbt user, it needs to be able to make changes to tables. dbt requires these permissions to run incremental models. Keep in mind that these permissions only work for tables, not views.
With tables, views and schemas, you also need to make sure you grant permission to future tables and views within a schema or database. This will make it so you don’t have to keep granting access on new objects that get created.
grant select on future tables in schema FACEBOOK to role ANALYZER;
grant select on future schemas in database BASE to role TRANSFORMER;
Conclusion
It’s a best practice to document your Snowflake architecture, especially the permissions of each role, before running any Snowflake command. Granting permissions can get confusing if you don’t have everything clearly documented. I know it can get frustrating when you think you’ve granted all the needed permissions and then a role stillcan’t access what you need.
Take your time. Don’t just grant a role all the permissions available because you are frustrated. Well-documented Snowflake users, roles, and permissions are key to a secure data warehouse. The initial work goes a long way as your organization grows and begins to rely more and more on the data ecosystem you’ve put into place.
The data movement infrastructure for the modern data teams.
