The 4-Role Security Model for Your Data Warehouse

Security is not the most exciting topic in a data architecture series. I'd argue it's one of the most important. If you're going to invest the time to build the pipeline, the modeling, and the automation, it's worth being responsible about who can touch what.

This post covers database security specifically: users, roles, and how to pair them together so the permission model reinforces the rest of your strategy instead of fighting it.

Users, roles, and objects

Three pieces are in play.

Objects are the things in the database: tables, views, schemas, databases. The actual storage.

Users are the individuals or systems that want to access those objects. That's you and me, and it's also the dedicated user you create for an ingestion tool or a Python job.

Roles sit in the middle. A role is a container for permissions. Users get assigned to roles, and the permissions pass through.

The default behavior on a lot of teams is to skip the middle piece. Permissions get assigned directly to individual users, some users get more than others, and it stays that way. It works, but it doesn't scale and it's hard to audit.

Why the pass-through matters

Say you create a role with read-only access to one schema and read/write to another. From that role's perspective, everything else in the database is invisible.

A role on its own does nothing. It becomes useful when you assign it to the people and systems using the database. Now that user inherits exactly those permissions.

The payoff shows up on the second user. Instead of granting the same set of object permissions again and maintaining two parallel definitions, you assign the new user to the existing role. They inherit everything. When you need to adjust a permission or add a condition later, you change it in one place and it flows to everyone holding that role.

It's the same principle as building a modular staging layer. Don't repeat yourself. Here it just happens to apply to security.

Add a second role with its own access, assign users to one or both, and you can describe any user's access by which roles they hold. One user sees two objects. Another holds both roles and sees all three in different capacities. A third sees only one.

The four baseline roles

Here's the model I help teams implement, mapped onto the architecture we've been building throughout this series. Four roles, each aligned to a specific part of the pipeline. You can tweak it to fit your use case, but if you have nothing in place today, start here.

1. Loader

The loader role owns the ingestion side of the warehouse. Full read and write on the raw layer. It can create schemas, drop schemas, select, whatever it needs.

The users assigned to it are typically not people. They're dedicated database users for Fivetran, Stitch, a custom Python process, or whatever else is landing data. Create a dedicated user per tool. You likely should be doing this anyway.

Just as important is what this role can't see. It has no visibility into the transformation layers, dev, or production. There's no reason for it to read or build there, so it doesn't get the option. If something goes wrong or an account gets compromised, the blast radius is the landing zone.

Start with the least amount of permission necessary and add on only when there's a reason.

2. Analyst

The analyst role is read-only on the marts layer. This covers business stakeholders, data analysts, and power users building reports.

They don't need to see the raw data or the intermediate models. Limiting the view gives you control over who sees which schemas and tables. You can also run multiple analyst-style roles, one per department, each scoped to the schemas that department needs.

The read-only part does more work than it looks like. If an analyst wants a new column or a custom metric, they can't quietly add it themselves. They have to come through the data team, which means it follows your conventions and you know it exists.

That friction is intentional. Nobody loves it on day one. What it prevents is the bigger problem: conflicting metrics and duplicate definitions scattered across places you don't know about. Security roles end up being more than security. They're a forcing function that keeps the project from fanning out.

3. Developer

This is the one most of us live in. Read access to the raw data, full ownership of the dev environment, read-only on production and CI.

Ownership of dev is what lets developers create and update their own environment, which is exactly what the dev workflow depends on. Read-only on raw keeps developers out of the ingestion layer. If new source data is needed, it goes through the data ingestion process rather than someone hand-loading a table.

Read-only on production keeps people from building things directly in prod. It might seem limited, but it covers 95% of the work.

Same idea as the analyst role: the friction is on purpose. It prevents accidental interactions with production data and reinforces the workflow you've already designed.

4. Automation

The automation role is what updates production. Not your developers.

Read access to raw, full ownership of the production schemas and the CI schema. The scheduled jobs, the deployment triggers, the daily refresh, all of it runs as a user assigned to this role. You can give it read-only on dev too, though it won't have much reason to look there.

Separating this from the developer role is what makes production deployment a process rather than a person.

The full picture

Put the four roles back on top of the architecture and every component is covered: the landing zone by loader, the dev environment by developer, production and CI by automation, and the marts layer by analyst.

The permission model ends up reinforcing the workflow you were already trying to build. Once it's in place, it's set. You can keep building and scaling on top of it without rethinking access every time someone new joins.