Legal forced restrictions on which enterprise employees could see the Epic data contained in an application's five-year-old Snowflake database with 60 DEV and PROD schemas. But having been maintained by a mix of US and India-based database engineers with a variety of skills and preferences, the grants of roles and privileges on the database had grown unwieldy and inflexible.

Assuming ownership of the database during a platform-wide architectural revisit of Snowflake grants, my role helped elicit a clear convention through diagrams and use cases, and then applied the convention to the database.

My refactoring of 729 tables, 239 views, 532 stored procedures, and 8 file formats involved over 8800 SQL commands. Creating an AI prompt with object maps from custom SQL queries to generate these commands lightened the lift and shortened the work to less than 15 hours, which included validation and re-mapping of previous grants in each schema to the new architecture.

Convention

Role assignments for DEV and PROD databases differ slightly, but they abide 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:

Team Role
Database Role
Database Role
Database Role
Privileges
Privileges
Privileges
Privileges
DBR to DBR Role
DBR to DBR Role
DBR to Team Role
DBR to Team Role
DBR to Team Role
DBR to Functional Role
DBR to Functional Role
Team Users
Team Accounts
Team Users
External Accounts
External Users
SCHEMANM_DBR_ADMIN
SCHEMANM_DBR_W
SCHEMANM_DBR_RO
DATABASENM_DB_ADMIN
DATABASENM_DB_W
DATABASENM_DB_RO
SCHEMANM_FR_US_PROD_ADMIN
SCHEMANM_FR_US_PROD_DEVENG
Database: DATABASENM_DB
Schema: STG_DATABASENM_SCHEMANM
OWNERSHIP

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

DELETE - FUTURE TABLE
INSERT - FUTURE TABLE
TRUNCATE - FUTURE TABLE
UPDATE - FUTURE TABLE
USAGE - FUTURE FILE FORMAT
USAGE - FUTURE PROCEDURE
USAGE - FUTURE STAGE

SELECT - FUTURE TABLE
SELECT - FUTURE VIEW
USAGE
Most team members ...
Not as many team members, if any ...