Firefly Health is a virtual-first healthcare company with a mission of delivering half-priced healthcare that’s twice as good. Having just closed a $40M Series B milestone which will support a multi-state launch of a new health plan for employees and their families, our analytics team is growing incredibly fast and keeping track of permissioning is proving increasingly difficult.
The dbt labs team have written excellent guides like the exact grant statements we use in a dbt project and the exact privileges we grant outside of dbt (thanks Claire 
). In this guide, I will be diving into the maintain step and demonstrating how to automate this process when users get added, change roles or leave.
Current Permissions Setup
Our current permissions set up closely resembles the recommended approach in “the exact privileges we grant outside of dbt” article. The transformer and reporter roles are granted to dbt developers, while the reporter role is granted to analysts that only query the warehouse.
Granting those permissions has historically been a manual process that boils down to us reaching out to our ACCOUNTADMIN to work some magic. Our ACCOUNTADMIN certainly has better things to do with their time, so we set about automating this process as much as possible.
Using dbt to Automate this Process
There are many tools out there that help with permissions management, however, setting one up - Terraform for example - at our organization would have been too big a lift.
Enter dbt. We set up an on-run-start macro that (re)grants permissions to new and existing users. (Although you only need to run these statements once, running them multiple times will have the exact same effect. For more on this see idempotence).
Now any analyst can modify the permissions macro and submit it as a pull request which, once approved, is picked up by the next prod job and voila, permissions granted!
Although the permission granting statements below are snowflake specific, you could swap out the warehouse specific statement and it should yield the same results.
Step 1 : Collect a list of your users and what roles they have access to
-
To see current grants in snowflake, run
SHOW GRANTS TO { ROLE <role_name> | USER <user_name> } -
Determine who is a dbt developer and who is an analyst and insert that into the following dictionary
{% set snowflake_users = {
"FIRSTNAME_LASTNAME": {
"is_active": "yes",
"is_dbt_developer": "yes"
},
"FIRSTNAME2_LASTNAME2": {
"is_active": "yes",
"is_dbt_developer": "no"
}
}
%}
Step 2: Iterate through your users to create or delete users
- If the user doesn’t already exist, create the user and grant it the analyst role
- If the user is no longer active, delete the user and all their associated schemas
We recommend that you follow the principle of least privilege and use sysadmin or equivalent to create/delete these users.
Note: only the transformer role should have the ability to create/delete any schemas
{% for user_type in user_types %}
{% if user_types["is_active"] == "yes" %}
use role SYSADMIN ;
create user IF NOT EXISTS {{ users }} password='temp_password'
default_role = reporter must_change_password = true;
grant role reporter to user {{ users }};
{% elif user_types["is_active"] == "no" %}
use role SYSADMIN ;
drop user IF EXISTS {{ users }};
use role transformer;
drop schema IF EXISTS analytics.dbt_{{ users }} ;
-- you can always drop any additional schemas, for example:
-- drop schema IF EXISTS analytics.dbt_{{ users }}_staging ;
{% endif %}
Step 3: Iterate through your users to grant the transformer role to dbt developers
- If the user is active, check if they’re a dbt developer and if they need the transformer role granted
- If their status was recently changed and they only need the reporter role then revoke access to the dbt role and delete their dev schemas.
{% if (user_types["is_active"] == "yes" and user_types["is_dbt_developer" ] == "yes") %}
use role SYSADMIN ;
grant role transformer to user {{ users }};
use role transformer;
create schema IF NOT EXISTS analytics.dbt_{{ users }};
grant usage on schema analytics.dbt_{{ users }} to role transformer;
grant select on all views in schema analytics.dbt_{{ users }} to role transformer;
grant select on all tables in schema analytics.dbt_{{ users }} to role transformer;
{% elif (user_types["is_active"] == "yes" and user_types["is_dbt_developer" ] == "no" ) %}
use role SYSADMIN ;
revoke role transformer from user {{ users }};
grant role reporter to user {{ users }};
use role transformer;
drop schema IF EXISTS analytics.dbt_{{ users }} ;
{% endif %}
{% endfor %}
Step 4: Include logic to make sure this only runs in prod (leveraging the target variable).
{% if target.name == "prod" %}
...
{% endif %}
Pulling all of this together
-- macros/grant_permissions.sql
{% macro grant_permissions(snowflake_users) %}
{% if target.name == "prod" %}
{% set snowflake_users = {
"FIRSTNAME_LASTNAME": {
"is_active": "yes",
"is_dbt_developer": "yes"
},
"FIRSTNAME2_LASTNAME2": {
"is_active": "yes",
"is_dbt_developer": "no"
}
}
%}
{% for users, user_types in snowflake_users.items() %}
-- check if the user is active, create the user if it doesn't already exist and grant it the reporter role
-- If the user is no longer active, delete the user and all their associated schemas
{% for user_type in user_types %}
{% if user_types["is_active"] == "yes" %}
use role SYSADMIN ;
create user IF NOT EXISTS {{ users }} password='temp_password' default_role = reporter must_change_password = true;
grant role reporter to user {{ users }};
{% elif user_types["is_active"] == "no" %}
use role SYSADMIN ;
drop user IF EXISTS {{ users }};
use role transformer;
drop schema IF EXISTS analytics.dbt_{{ users }} ;
-- you can always drop any additional schemas, for example:
-- drop schema IF EXISTS analytics.dbt_{{ users }}_staging ;
{% endif %}
-- if the user is active, check if they're a transformer user and if they need that role grant it ,
-- if their status was recently changed and they only need the reporter role then revoke access to transformer role and deleted their schemas.
{% if (user_types["is_active"] == "yes" and user_types["is_dbt_developer" ] == "yes") %}
use role SYSADMIN ;
grant role transformer to user {{ users }};
use role transformer;
create schema IF NOT EXISTS analytics.dbt_{{ users }};
grant usage on schema analytics.dbt_{{ users }} to role transformer;
grant select on all views in schema analytics.dbt_{{ users }} to role transformer;
grant select on all tables in schema analytics.dbt_{{ users }} to role transformer;
{% elif (user_types["is_active"] == "yes" and user_types["is_dbt_developer" ] == "no" ) %}
use role SYSADMIN ;
revoke role transformer from user {{ users }};
grant role reporter to user {{ users }};
use role transformer;
drop schema IF EXISTS analytics.dbt_{{ users }} ;
{% endif %}
-- ends internal dict loop
{% endfor %}
-- ends external dict loop
{% endfor %}
-- if statement for 'target.name == prod'
{% endif %}
{% endmacro %}
Step 6 : Add macro as an on run-start hook to dbt_project.yml
- This macro will now run before dbt runs any models.
- This will run for all the users that you set inside the macro.
-- dbt_project.yml
on-run-start:
-- grants permissions to all users to reporter and transformer roles
- "{{ grant_permissions()}}"
Step 7: Maintain
Next time you have a new analyst on the team, ask them to add their name and create a PR. Same if you have someone leaving your team, changing is_active to “no” can be their last PR.
Future improvements
We’re pretty happy with this process so far, and we think this can be useful for a small org. Ultimately, we’ll probably move to something like Terraform as we get bigger to manage permissions across multiple systems.