A Snowflake database needed a permissions overhaul to keep enterprise users from accessing sensitive health information and to prevent exposure of an electronic health record (EHR) vendor's intellectual property.
Or rather, three databases, one each for DEV, PREPROD, and PROD, together making up a single logical database. Each carried 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 — just over 1,500 objects in all. It would take a minimum of two to three weeks of grant-herding by hand, assuming zero errors along the way, which at this scale was wishful thinking.
And, of course, Legal needed it done yesterday.
No Shortcuts
Incremental patches were tempting, but every patch would have just moved the strays around and left openings for new vulnerabilities. Six years on, some poor soul would be facing the same problem with even more accumulated drift.
A complete roles and permissions overhaul across three environments, all done together, was the only way forward.
On the Trail
Committing to a full overhaul meant looking ahead to figure out which way was right, while working out how to avoid blisters along the way. Both had to go well.
Looking ahead — convention design. Before a single grant statement got written, somebody had to know what right looked like. That meant sitting in DataOps conversations and actively pushing back on ideas that sounded clean in theory but fell apart in practice. The crucial move was running hands-on tests during the design phase, not after. Every edge case caught in the design phase was one fewer to unwind in production.
Avoiding blisters — automation. Just over 1,500 objects across three databases, three roles per schema, and six years of old grants on every one of those objects to revoke: the statement count ran into the thousands. Typed by hand, a list that long doesn't get finished without a mistake somewhere in the middle, and every hour spent typing would have been an hour not spent checking.
Rules of the Road
The convention settled on two rules:
Assign at the schema level, not the object level. Granting permissions table by table was a losing game. The schema became 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, because in these databases each schema could have a different owner.
Three role types did the work:
- Team roles — account-level roles for the people who supported the database day to day.
- Database roles — Admin, Writer, and Reader roles scoped to a specific database, unique per schema. These were the backbone.
- Functional roles — account-level roles for consumers: users, service accounts, downstream systems. Named for what they did, not how the database was built.
In PROD, a Database Reader or Writer role was granted to a Functional role. Admin access went to a Functional role only when a service account genuinely needed to create Stages dynamically. DEV allowed more flexibility: whatever the work actually required.
The diagram below shows the Reader and Admin cases.
How the Roles Connect
The names in the diagram follow one pattern. SCHEMANM_DBR_ADMIN, _W, and _RO are the three database roles (DBR) for a schema, one set per schema. DATABASENM_DB_ADMIN, _W, and _RO are the team roles (DB), one set per database. SCHEMANM_FR_US_PROD_* are the functional roles (FR), named for the consumer they serve. Database roles are green, team roles blue, functional roles red.
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, ..."]
From the Saddle
A prompt-driven approach made the job more about steering the work than getting trampled by it. A few SQL object queries fed their output into a chat prompt template that both generated and applied the thousands of GRANT and REVOKE statements needed. The work still took a couple of days, but most of the time went to monitoring runs and validating results, not to grinding out statements by hand. Promises were kept, dates were met, and nobody using the database noticed a thing.
The end of the trail is easier to reach from the saddle than on foot.
Before the Next Drive
- A hard deadline pushes toward patching. Fix the whole anyway.
- Test the convention on real schemas before making it the convention.
- Let AI write the statements; spend your time on reading them.
- Document the convention so the next person (including you) doesn't have to start from scratch.