Snowflake Roles & Privileges

After inheriting an app's Snowflake DEV, PREPROD, and PROD databases, my adaptability was tested by six years of governance drift and an urgent legal restriction limiting enterprise‑wide caregiver access to Epic data.

Patches would not do: the database needed a complete overhaul of roles and permissions.

It was both an opportunity to restore order from chaos as well as a mandate to fundamentally rethink how we managed permissions on every database object.

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.

To meet this challenge, I took a two-pronged approach:

• First, I participated in DataOps conversations about the grants convention creation process, contributing insights from hands-on testing and real-world scenarios. This collaborative effort ensured the new standards would be practical on my database, 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 a diagram of role assignments that everyone in the enterprise could replicate. It was a simple and stable foundation for permission management going forward.

Conventions

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 of Role Assignments

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>"]

Takeaways

• Compliance can be a catalyst: use mandated changes as opportunities to pay down technical debt.
• Automation is essential at scale: manual permission management doesn't survive six years of organic growth.
• Conventions prevent drift: documented standards reduce the variability introduced by rotating teams.
• Test before codifying: real-world testing should inform convention design, not follow it.