Imagine you just inherited a Snowflake database needing a permissions overhaul to keep a not-so-small swath of enterprise caregivers from accessing sensitive health information and Epic intellectual property.

Only it's not just one database: it's three — DEV, PREPROD, and PROD — each carrying six years of permissions drift layered across multiple undocumented naming and grant conventions, whose original logic left with the people who built them.

The math: 60 schemas, 729 tables, 239 views, 532 stored procedures, and 8 file formats = maybe two to three weeks minimum to untangle by hand if you don't introduce errors, which, at this scale, are inevitable.

And Legal needed it done yesterday.

No sweat.

snowflake-logo

Patch or fix?

Sure, incremental patches are tempting, but every patch leaves the underlying mess ever more difficult to work with. Six years from now, someone else will be sitting where you're sitting, facing the same problem with even more accumulated drift.

So yeah, you're stuck with fix, because a complete roles and permissions overhaul across all three environments means doing the right thing.

Getting Through It

After committing to a sane future, you split into two tracks running simultaneously. Both have to go well.

The first track - convention design. Before you write a single grant statement, you need to know what right looks like. This means sitting in DataOps conversations and actively pushing back on ideas that sound clean in theory but fall apart in practice. The crucial move here: you run hands-on tests during the design phase, not after. Every edge case you find before the convention is finalized is one you don't have to undo later.

The second track - automation. This is the uncomfortable truth about working at this scale: manual grant management is not just slow, it's a trap. You will make errors. You will miss things. You will blow your cognitive budget on the wrong problems.

Why not take a prompt-driven approach? Have SQL object queries feed input variables into a chat prompt to generate and apply all the 8,800 grant and revoke statements for you. This puts you in the director's chair. It still takes a couple of days — but that time goes to monitoring runs and validating results, not grinding out statements by hand. You're steering the work instead of drowning in it.

Zero downtime. On schedule.

What the Convention Actually Looks Like

The convention that works rests on two core principles worth holding onto:

Assign at the schema level, not the object level. If you're granting permissions table by table, you've already lost. The schema is your unit of governance.

Create a distinct Admin, Writer, and Reader role for every schema. Not shared across schemas. Not inherited from a parent. Distinct, per schema, every time. Note: this per-schema split fits this case specifically, where every schema has different owners.

From there, you're working with three role types:

  • Team roles — account-level roles for the people who support the database day to day.
  • Database roles — Admin, Writer, and Reader roles scoped to a specific database, unique per schema. These are the backbone.
  • Functional roles — account-level roles for consumers: users, service accounts, downstream systems. Named for what they do, not how the database is built.

The elegance is in how they connect. In PROD, you grant a Database Reader or Writer role to a Functional role. Admin access goes to a Functional role only when a service account genuinely needs to create Stages dynamically. In DEV, you have more flexibility — grant what the work actually requires.

Role Assignment Diagram

graph TD;

classDef left text-anchor:start;

%% Colorize Database Roles
style SCHEMANM_DBR_ADMIN fill:#cfc,stroke:#0c0,color:#333
style SCHEMANM_DBR_W fill:#cfc,stroke:#0c0,color:#333
style SCHEMANM_DBR_RO fill:#cfc,stroke:#0c0,color:#333

%% Colorize Team Roles
style DATABASENM_DB_ADMIN fill:#ccf,stroke:#00c,color:#333
style DATABASENM_DB_W fill:#ccf,stroke:#00c,color:#333
style DATABASENM_DB_RO fill:#ccf,stroke:#00c,color:#333

%% Colorize Functional Roles
style SCHEMANM_FR_US_PROD_ADMIN fill:#fcc,stroke:#c00,color:#333
style SCHEMANM_FR_US_PROD_DEVENG fill:#fcc,stroke:#c00,color:#333

%% Database to Schema
DATABASENM_DB[Database: DATABASENM_DB] --> STG_DATABASENM_SCHEMANM[Schema: STG_DATABASENM_SCHEMANM]

%% Schema to Roles
STG_DATABASENM_SCHEMANM -->|Team Role| DATABASENM_DB_ADMIN
STG_DATABASENM_SCHEMANM -->|Database Role| SCHEMANM_DBR_ADMIN
STG_DATABASENM_SCHEMANM -->|Database Role| SCHEMANM_DBR_W
STG_DATABASENM_SCHEMANM -->|Database Role| SCHEMANM_DBR_RO

%% Privileges
DATABASENM_DB_ADMIN -->|Privileges| OWNERSHIP
SCHEMANM_DBR_ADMIN -->|Privileges| PrivDA["CREATE FILE FORMAT<br/>CREATE PROCEDURE<br/>CREATE STAGE<br/>CREATE TABLE<br/>CREATE TEMPORARY TABLE<br/>CREATE VIEW<br/>OWNERSHIP - FUTURE FILE FORMAT<br/>OWNERSHIP - FUTURE PROCEDURE<br/>OWNERSHIP - FUTURE STAGE<br/>OWNERSHIP - FUTURE TABLE<br/>OWNERSHIP - FUTURE VIEW"]:::left
SCHEMANM_DBR_W -->|Privileges| PrivW["DELETE - FUTURE TABLE<br/>INSERT - FUTURE TABLE<br/>TRUNCATE - FUTURE TABLE<br/>UPDATE - FUTURE TABLE<br/>USAGE - FUTURE FILE FORMAT<br/>USAGE - FUTURE PROCEDURE<br/>USAGE - FUTURE STAGE"]:::left
SCHEMANM_DBR_RO -->|Privileges| PrivRO["SELECT - FUTURE TABLE<br/>SELECT - FUTURE VIEW<br/>USAGE"]:::left

%% Database Roles to Database Roles
SCHEMANM_DBR_RO -->|DBR to DBR Role| SCHEMANM_DBR_W
SCHEMANM_DBR_W -->|DBR to DBR Role| SCHEMANM_DBR_ADMIN

%% Database Roles to Team Account Roles
SCHEMANM_DBR_ADMIN -->|DBR to Team Role| DATABASENM_DB_ADMIN
SCHEMANM_DBR_W -->|DBR to Team Role| DATABASENM_DB_W
SCHEMANM_DBR_RO -->|DBR to Team Role| DATABASENM_DB_RO

%% Database Roles to Functional (External) Roles
SCHEMANM_DBR_ADMIN -->|DBR to Functional Role| SCHEMANM_FR_US_PROD_ADMIN
SCHEMANM_DBR_RO -->|DBR to Functional Role| SCHEMANM_FR_US_PROD_DEVENG

%% Team Roles to Users
DATABASENM_DB_ADMIN -->|Team Users| ADMINS[Most team members ...]
DATABASENM_DB_W -->|Team Accounts| WRITERS[same_service_account_for_DEV_and_PROD.org]
DATABASENM_DB_RO -->|Team Users| READERS[Not as many team members, if any ...]

%% Functional Roles to Users
SCHEMANM_FR_US_PROD_ADMIN -->|External Accounts| SERVICE_ACCOUNT[service_account_for_PROD.org]
SCHEMANM_FR_US_PROD_DEVENG -->|External Users| HUMANS["human1@company.org, human2@company.org, ..."]

What Travels

You now have a documented convention and a role assignment diagram that other database owners in your enterprise can actually use: a practical reference guide built from implementation experience, not a theoretical framework. Next time you land in a situation like this, remember the hard-won principles that served you well:

Compliance pressure is a gift, if you use it right. A legal mandate gave this project the urgency it needed to get prioritized. The technical debt that might have been deferred for another two years got addressed because it had to be. Don't waste that forcing function on a patch.

At hundreds of objects across multiple environments, automation isn't a nice-to-have. It's the only path that ends without errors.

Documented conventions are the only thing that prevents this from happening again. Role assignment diagrams, decision records, naming conventions — these give rotating teams a fighting chance at consistency, so the next person doesn't have to start from scratch.

Test before you codify. The convention should reflect what actually works, not what sounds right in a meeting. Find the edge cases while you can still fix them cheaply.