Snowflake minimal role permissions (in Python)

Hello!

This is a cheat sheet of commands for creating roles and role based permissions within Snowflake. Note, things for you to change are usually in all caps.

Additionally, this sheet also includes making certain database objects, such as databases, schemas and tables. This is largely due to my companies architecture where a role usually comes with additional database objects. You can skip these steps and use pre-existing database objects if you don't need to create new ones.

Optional : connect in Python

If you are doing this in python: make sure to to connect first:

# Connecting to Snowflake
conn = snowflake.connector.connect(
    user=USER,
    password=PASSWORD,
    account=ACCOUNT,
)

Optional: How to run these in python:

I am going to put the commands directly for the rest of the sheet. They can all be run in python by wrapping it into this format

query = 'COMMAND TO PUT IN SUCH AS THOSE BELOW' 

cur = conn.cursor().execute(query)

Create a role:

create role ROLE_NAME

Grant that role to a specific user

grant role ROLE_NAME to user USER_NAME

Grant warehouse usage to a role

grant usage on warehouse WAREHOUSE_NAME to role ROLE_NAME

Optional: Create database and schema to use:

Sometimes, especially for multi-tenant style builds, a new role may mean a new database. So here is how to create one. This command only builds if one of that name does not already exist:

create database if not exists DATABASE_NAME

create schema if not exists SCHEMA_NAME

Grant database and schema usage:

grant usage on database DATABASE_NAME to role ROLE_NAME

grant usage on schema SCHEMA_NAME to role ROLE_NAME

Optional: Select a database and schema to use

For specific commands that you are doing within a database, you can select to work in a specific database. This will prevent you from having to prefix a lot of commands with the database.

use database DATABASE_NAME

use schema SCHEMA_NAME

Then from now on, your connection will assume you want to be doing things within this database and schema. Alternatively, you can prefix commands and not use a specific database if you are running commands across multiple.

Create stage permissions

Our workflow uses stages to upload and put data into tables. This is not rare, but not possibly the most common need to roles. If you don't use stages, you can skip this permission.

grant create stage on schema SCHEMA_NAME to role ROLE_NAME

Select permissions on all tables

Our current workflow is permission driven by schema. This means users should have the same permissions for all tables within a given schema. We have different levels of permissions for schemas, most notably:

Read / select only access on protected
All permissions
No permissions

Snowflake allows you much more fine grained permissions between 1 and 2, we have just found it is easier and much more efficient to have users either fully owning a schema or only able to read / select.

Read / Select permissions

For current tables:

grant select on all tables in database DATABASE_NAME to role ROLE_NAME

For all future tables:

grant select on future tables in database DATABASE_NAME to role ROLE_NAME

All permissions:

Here is an example on a particular schema

For existing

grant all privileges on schema SCHEMA_NAME to role ROLE_NAME

For future:

grant all on future tables in schema SCHEMA_NAME to role ROLE_NAME

File format permissions:

Create a file format if necessary. Parquet example here:

create file format if not exists FILE_FORMAT_NAME type = parquet

Then grant privileges to it:

grant all privileges on file format FILE_FORMAT_NAME to role ROLE_NAME