#064: Secure Your Data Warehouse w/ 4 Simple Roles

The simplest way to secure a data warehouse is to start with who is involved rather than with permissions. Group everyone touching your data into four buckets, analysts, developers, loaders and automation, then name a role after each group and grant it only what that group needs. Analysts select from the marts. Developers select from raw and own their dev schema. Automation owns production and CI. Loaders own the raw layer and nothing else. Chain the roles together so each inherits the one below it, and you can set the whole thing up in a short script and check it with a grants query.

Start with who is involved

Security is a key responsibility for engineers, and it is definitely not the most glamorous part of the job. Like a lot of things in data, it gets complicated if you let it.

Before writing any code or creating any roles, figure out who is interacting with the data in the first place. Over a typical simple architecture, there are four groups:

• Developers. The engineers building the data infrastructure day to day. That is you, or the people you work closely with.
• Analysts. Analysts and stakeholders who consume what you publish, connect reporting tools, and export data.
• Loaders. The systems and tools pulling from source systems into the central architecture.
• Automation. Orchestration tools, schedulers and automated CI/CD runs.

You might have more groups, you might have fewer. In my experience this covers most of the user bases and systems involved, or at least gets you started.

Then take the name of the group and make it the name of the role. That one habit is why these models stay readable a year later.

The analyst role

The analyst role gets select permissions on the marts. No create, no drop. They are reading data so they can build reports and analyze it.

You will run into people who want access to the warehouse layer or the raw data, and ultimately that is your call. I like to keep this one limited to the marts.

Limiting it gives you more control over the warehouse. It also keeps rogue analysts from creating tables and interacting with things upstream of the development team.

The developer role

Developers need everything the analyst has, plus more. They select from the raw source data, read only, no building or dropping there.

In the dev environment they get ownership: create, insert, select, drop, whatever they want in their own schema or database. That is the safe space.

Production and CI are read only for developers. Not every developer should be able to create, drop or insert into production.

If you are a smaller team you might allow it. I still think keeping production off limits for most people is good practice, with exceptions for a manager or an admin level account.

The automation role

If developers cannot touch production, how does production get updated? That is what the automation role is for.

It inherits the developer read permissions and adds build and deploy on production and CI. It is the only role that can make changes there, which is what keeps those processes consistent and easy to keep an eye on.

Raw data stays select only for automation as well. It can deploy across the analytics side, and it cannot create or drop in raw.

If you want a person to have that admin level ability for ad hoc work, grant that user the automation role rather than handing out individual permissions. You keep knowing exactly who can do what.

The loader role

The loader role has full permissions in the raw layer and nothing in analytics. Extract and load tools, data streams, custom apps, whatever is landing data gets a user assigned to this role.

That boundary is the point. Third party tools and custom processes cannot reach anything on the analytics side, because their scope stops at raw.

What it looks like in Snowflake

In the demo account there are two databases that match the diagram: RAW for landed source data and ANALYTICS for the staging, warehouse and marts layers.

Switching roles changes what you can even see in the object browser:

• analyst sees the marts schema only.
• developer sees essentially everything, with restrictions on what it can create or delete.
• automation looks much the same, since it inherits developer, and it cannot create in raw.
• loader sees the raw database and nothing in analytics.

Trying things that should fail

As analyst, selecting from a marts table returns data as expected. Selecting from staging, which is further upstream, comes back unauthorized. Creating a test dimension table is blocked too.

As developer, selecting from staging works. Creating a table in the production warehouse schema returns insufficient privileges.

Creating the same table in my own dev schema succeeds, and I can drop it again just as easily:

create table dev_mk.dim_test as
select 1 as id;

drop table dev_mk.dim_test;

As automation, that same create in the production schema goes through. In practice that role is not a person at a keyboard, it is GitHub Actions, Airflow or dbt Cloud running the deployment.

And loader is the mirror image. It cannot select from anything in analytics, and it can create and insert freely in raw.

Reviewing the grants

If you want to check your work, look at the grants directly. Using an admin role, you can list what each role actually holds:

show grants to role analyst;

Analyst comes back with usage on specific databases and schemas, down to table level select. Developer shows the same plus usage on the production schemas, with ownership only on its dev schema.

Automation shows ownership across production and CI. Loader shows the reverse: ownership of everything in the raw database and nothing for analytics.

The setup scripts

The exact syntax depends on your database, so treat this as the shape rather than a copy and paste. Here it is on Snowflake.

1. Create the roles and their warehouses

On Snowflake you also have virtual warehouses, which are compute. I like to tie compute to the role so you can monitor and manage cost per user group.

create role analyst;
create role developer;
create role automation;
create role loader;

create warehouse analyst_wh with warehouse_size = 'xsmall';
grant usage on warehouse analyst_wh to role analyst;

Repeat that for the developer, automation and loader warehouses. As things scale, cost attribution by group becomes genuinely useful.

2. Create the users

Ingestion tools get their own users: Fivetran, Airbyte, Stitch, or a plain Python user. Each person on the team gets a user too.

create user fivetran_user default_role = loader;
grant role loader to user fivetran_user;

create user michael default_role = developer;
grant role developer to user michael;

For automation you can create separate users per tool, say one for GitHub Actions and one for Airflow or Prefect, or a single automation user. Either way they share the same role and therefore the same permissions.

3. Set up the hierarchy

This part is small and genuinely important. Roles can be granted to other roles, not just to users.

grant role analyst to role developer;
grant role developer to role automation;

Now a change to analyst flows through to both of the others. You are not copying the same select grants into three places, which is what keeps the setup clean as it grows.

4. Grant the permissions

The last part is the granular grants, and this is where you customize to your own layout:

grant usage on database analytics to role analyst;
grant usage on schema analytics.marts to role analyst;
grant select on all tables in schema analytics.marts to role analyst;

Build that out per role and you get exactly the behavior described above, aligned with the four groups you started from.

Most of these concepts translate across pretty much any database you use. The names and the structure matter more than the syntax.

Related reading

• The 4-Role Security Model for Your Data Warehouse
• The Risks of Lazy Database Roles
• Why Data Security Is More Than Just Restricting Access
• How to use SnowSQL: Install, Config & Query

Final takeaway

Every team has its own naming conventions, and this is the approach that has worked well for me across client warehouses: name roles after user groups, grant each one the narrowest useful slice of the pipeline, and let a hierarchy carry the shared parts. Set up once, it covers every layer from ingestion to end user reporting.