When I inherited full responsibility for three Snowflake databases, the timing couldn't have been more challenging. A new legal restriction had just taken effect which limited enterprise-wide caregiver access to Epic data. This regulatory change triggered a complete overhaul of roles and grants across our entire Snowflake platform.

My audit of the databases revealed dire truths: after six years of incremental changes applied by a mix of US and India-based engineers — each with varying skill levels and preferences — database permissions across the databases had become unwieldy and inconsistent.

Opportunity

Rather than simply applying band-aid fixes to meet the legal requirements, I saw a chance to establish order from chaos by fundamentally rethinking how we managed permissions. The mandate to review every grant gave us a good reason to redesign the entire system from the ground up.

The scope was substantial: 60 schemas, 729 tables, 239 views, 532 stored procedures, and 8 file formats across three databases—all needing careful review and restructuring.

Approach

I took a two-pronged approach:

  • First, I participated in the organization's new grants convention creation process, contributing insights from hands-on testing and real-world scenarios. This collaborative effort ensured the new standards would be practical, not just theoretical.

  • Second, I automated the implementation. Rather than manually writing thousands of grant and revoke statements, I developed a chat prompt that accepted input variables from SQL object queries. This prompt-driven approach generated over 8,800 SQL grant/revoke commands—transforming what would have been two weeks of tedious, error-prone manual work into two days of systematic execution.

Outcomes

The refactoring was completed on schedule with zero downtime. More importantly, we agreed on conventions and diagrams for role assignments that everyone in the enterprise could replicate, and we established a sustainable foundation for permission management going forward.

Convention

Role assignments for DEV and PROD databases differed slightly, but they abided by a few simple guidelines.

Main principles

  • Assign roles at Schema level rather than sub-Schema level like tables, views, etc.
  • Create a unique set of Database roles for Admins, Writers, and Readers for each schema in the database.

Role types

This combination of Team, Database, and Functional role types allows a great deal of flexibility when assigning privileges to application database consumers:

  • Team roles - these are Account-level roles dedicated to the team members that supports a Snowflake database.
  • Database roles - these are Admin, Writer, and Reader Database roles unique to each Snowflake database.
  • Functional roles - these are Account-level roles for Snowflake database consumers. They typically contain users and service accounts, and they follow a syntax convention representing the function they serve.

Implementation notes

  • In a PROD database, grant either the Database Reader (DBR_RO) or Writer (DBR_W) role to a Functional role. Only grant the Database Admin (DBR_ADMIN) role to a Functional role containing only a service account when the service account needs to dynamically create Stages.
  • In a DEV database, grant Database Reader (DBR_RO), Writer (DBR_W) and DBR_ADMIN roles to Functional roles as needed.

Diagram

Here's how the relationships between the roles and privileges in the convention generally played out:

graph TD;

%% 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["
 	<div style='text-align:left; white-space:nowrap'>
 		CREATE FILE FORMAT
 		CREATE PROCEDURE
 		CREATE STAGE
 		CREATE TABLE
 		CREATE TEMPORARY TABLE
 		CREATE VIEW
 		OWNERSHIP - FUTURE FILE FORMAT
 		OWNERSHIP - FUTURE PROCEDURE
 		OWNERSHIP - FUTURE STAGE
 		OWNERSHIP - FUTURE TABLE
 		OWNERSHIP - FUTURE VIEW
 	</div>"]
SCHEMANM_DBR_W -->|Privileges| PrivW["
 	<div style='text-align:left; white-space:nowrap'>
 		DELETE - FUTURE TABLE
 		INSERT - FUTURE TABLE
 		TRUNCATE - FUTURE TABLE
 		UPDATE - FUTURE TABLE
 		USAGE - FUTURE FILE FORMAT
 		USAGE - FUTURE PROCEDURE
 		USAGE - FUTURE STAGE
 	</div>"]
SCHEMANM_DBR_RO -->|Privileges| PrivRO["
 	<div style='text-align:left; white-space:nowrap'>
 		SELECT - FUTURE TABLE
 		SELECT - FUTURE VIEW
 		USAGE
 	</div>"]

%% 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[<a href="">same_service_account_for_DEV_and_PROD.org</a>]
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[<a href="">service_account_for_PROD.org</a>]
SCHEMANM_FR_US_PROD_DEVENG -->|External Users| HUMANS["
	<div style='text-align:left; white-space:nowrap'>
		<a href="">human1@company.org</a>
		<a href="">human2@company.org</a>
		<a href="">...</a>
	</div>"]

Takeaway

When faced with technical debt and compliance pressure simultaneously, look for the automation opportunity. The time invested in building the right tooling pays dividends in accuracy, speed, and future maintainability.