The 4-Role Security Model for Your Data Warehouse

A data warehouse security model works best when permissions live on roles instead of on people. A role is a container for access rights: you grant the role access to objects, then assign users to the role and the permissions pass through to them. The baseline I set up with data teams is four roles mapped onto the pipeline. Loader owns the raw landing zone, analyst reads the marts layer, developer owns the dev environment and reads everything else, and automation owns production and CI. Each role starts with the least access it needs and gains more only when there is a reason. Built that way, the permission model reinforces the workflow you already designed instead of fighting it.

Security belongs inside the architecture

Security is not the most interesting topic compared to modeling or orchestration. In my opinion it is no less important.

If you are going to spend months building the pipeline, the models and the automation, it is worth being deliberate about who can touch what. The good news is that this is one of the easier things to get right early, and one of the harder things to untangle later.

Users, roles and objects

Three pieces are in play, and the whole model falls out of how they connect.

• Objects. The things in the database: tables, views, schemas, databases. The actual storage.
• Users. The people or systems that want to reach those objects. That is you and me, and it is also the dedicated user you create for an ingestion tool or a Python job.
• Roles. Containers for access rights that sit in the middle. Users get assigned to roles, and the permissions pass through.

The typical behavior is to skip the middle piece. Permissions get granted straight to individual users, some people end up with more than others, and everything stays as it is.

It works for a while. It does not scale, and it is very hard to audit once a few years have gone by.

Why roles beat granting users directly

Say you create a role with read only access to one schema and read and write access to another. From that role's perspective, every other object in the database is invisible.

On its own, a role does nothing. It becomes useful the moment you assign it to the real people and systems using the database.

The payoff shows up on the second user

Adding the second person is where this pays off. Instead of granting the same objects again and maintaining two parallel definitions, you assign them to the role that already exists.

They inherit everything. And when you need to adjust a permission or add a condition later, you change it in one place and it reaches everyone holding that role.

It is the same principle behind a modular staging layer. Do not repeat yourself. Here it just happens to apply to security.

Reading a user's access

Add a second role with its own access and the picture fills out quickly:

• One user holds a single role and sees two objects, read only on one and read and write on the other.
• Another holds both roles and sees everything, at different levels.
• A third holds one role and sees a single object.

Every answer to "what can this person do" now comes from the roles they hold, not from a scattered list of one off grants.

The four baseline roles

Here is the model I help teams implement, laid over the same architecture the rest of this series builds: a landing zone, a transformation pipeline, dev and production environments, and a marts layer for reporting.

Four roles, each aligned to one part of that picture. You can customize it to your situation, but if you have nothing in place today, this is a good base to start from.

Role 1: Loader

The loader role owns the ingestion side of the warehouse. Full read and write on the raw layer, which means it can create schemas, drop them, select, and do whatever landing data requires.

The users assigned to it are usually not people. They are dedicated database users for Fivetran, Stitch, a custom Python process, or whatever else brings data in. Create a separate user per tool. You probably should be doing that anyway.

What this role cannot see matters just as much. It has no visibility into the transformation layers, dev or production, because an ingestion tool has no reason to read or build there.

If credentials leak or a job goes sideways, the blast radius is the landing zone. That is the whole idea behind starting with a minimal set of permissions and adding only as needed.

Role 2: Analyst

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

They have little reason to see raw data or intermediate models. Limiting the view gives you control over who reaches which schemas and tables, and you can run several analyst style roles, one per department, each scoped to what that group actually uses.

The read only part does more work than it looks like. If an analyst wants another column or a custom metric, they cannot quietly add it themselves. They have to come to the data team.

Role 3: Developer

This is the role 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 development workflow depends on. Read only on raw keeps them out of the ingestion layer, so new source data goes through the loading process rather than someone hand loading a table.

Read only on production keeps people from building things directly in prod. It may seem limited. In practice it covers about 95% of the work.

Role 4: Automation

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

Read access to raw, plus ownership of the production schemas and the CI schema. The scheduled jobs, the deployment triggers and the daily refresh all run as a user assigned to this role.

You can give it read only on dev as well, though it will not have much reason to look there. Separating this from the developer role is what turns production deployment into a process instead of a person.

Why the friction is the point

Two of these roles deliberately hold access back from people who could probably be trusted with it. That is the part that gets pushback, so it is worth saying why.

For analysts, the friction routes requests through your workflow. Nobody loves that on day one. What it prevents is the bigger problem: conflicting metrics and duplicate definitions scattered across places you do not know about.

Independent analysts with their own versions of the same metric is a real problem at a lot of companies. Three reports, three revenue numbers, and you are the one explaining the difference.

For developers, read only on production prevents accidental interaction with live data and reinforces the deployment path you already designed. Security roles end up doing more than security. They are a forcing function that keeps the project from fanning out in directions you cannot see.

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.

You end up with an end to end strategy where the permission model reinforces the workflow rather than sitting beside it.

Once it is in place, it is largely set. You can keep building and scaling on top of it without rethinking access every time someone new joins.

Related reading

• Secure Your Data Warehouse w/ 4 Simple Roles
• The Risks of Lazy Database Roles
• Why Data Security Is More Than Just Restricting Access
• The 3-Environment Design for Your Database

Final takeaway

This is the baseline I set up with data teams, and it holds up whether you are on Snowflake, BigQuery or Postgres. Put the permissions on four roles that match the layers of your pipeline, start everyone at the least access they need, and the security model stops being a separate project and becomes part of the architecture.