Imagine inheriting 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. That's a minimum of two to three weeks of untangling by hand, assuming zero errors along the way, which at this scale is wishful thinking.
And, of course, Legal needed it done yesterday.
Why Take the Long Way
Incremental patches are tempting, but every patch just moves the strays around, and the underlying mess gets ever more difficult to work with. Six years from now, some poor soul (probably you) will be sitting in this same chair, facing the same problem with even more accumulated drift.
So yeah, fix it is. A complete roles and permissions overhaul across all three environments is simply the right thing to do.
Driving Through
Decision made, the work splits into two tracks running simultaneously. Both have to go well.
The first track — convention design. Before a single grant statement gets written, somebody has to know what right looks like. That 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 is to run hands-on tests during the design phase, not after. Every edge case caught before the convention is finalized is one that never has to be undone.
The second track — automation. Here's the uncomfortable truth about working at this scale: manual grant management isn't just slow, it's a trap. Errors will happen. Things will get missed. The cognitive budget gets blown on all the wrong problems.
What Right Looks Like
The whole convention comes down to two principles:
Assign at the schema level, not the object level. Granting permissions table by table is a losing game from the start. The schema is the 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, three role types do the work:
- 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, a Database Reader or Writer role gets granted to a Functional role. Admin access goes to a Functional role only when a service account genuinely needs to create Stages dynamically. DEV allows 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 the Approach Looks Like
A prompt-driven approach makes the job more about steering the work than getting trampled by it. Develop a few SQL object queries, and feed their output into a chat prompt template that generates and applies the thousands of GRANT and REVOKE statements needed. It may still take a couple of days, but that time goes to monitoring runs and validating results, not to grinding out statements by hand.
Reaching the end of the trail on schedule with zero downtime is easier from the saddle than on foot.
Worth Bringing to the Next Overhaul
- Forcing functions are too valuable to waste on just moving strays around.
- Prove the convention in the real world before codifying it.
- Use automation to steer the work rather than get trampled by it.
- Document the convention so the next person (including you) doesn't have to start from scratch.
These are the principles that help future you ride a little easier through the next overhaul.