Snowflake Role Permissions
urn:js:virtue:aspire:standard:7.1
TL;DR
This standard defines boundaries for access to Snowflake databases based on user type. These standards will be used by Governance when approving PRs coming via Barbossa Compass repository.
Definition
Categories of access
The following table summarises 3 access categories and associated privileges we can use to map to user types.
| Policy Type | Database Permissions | Schema Permissions | Object Permissions | SQL Grants |
|---|---|---|---|---|
| Read | Usage | Usage | Read | SELECT |
| Write | Usage | Usage | Read, Write | (Read) + DELETE, INSERT, UPDATE, TRUNCATE, USAGE, OPERATE |
| Manage | Usage | Usage | Read, Write, Create | (Write) + ALTER, DROP, CREATE |
User Type + Category mapping
Below is a mapping of user type to access category. Access categories can be either provided through policies directly on a role, or via a role granted to a role as part of a hierachy.
Note that all users have access to the Scratch DB and schema to ‘Manage’ tables and views. This access is assumed but not included in the below table.
| User Type | Database | Access Category | Service User Only |
|---|---|---|---|
| Analyst | All | Read | No |
| Engineer/Developer (non-Support) | Production, Pre-Production | Read | No |
| Engineer/Developer (non-Support) | Development | Manage | No |
| Engineer/Developer (Support) | Production, Pre-Production | Write (selective) | No |
| ETL | All | Write, Manage (selective) | Yes |
| CICD | All | Manage | Yes |
General Principles
|Principle Number|Description|Rationale| |:–|:–|:–| |#1|Engineers with sensitive data access in Production or Pre-Production should not have Write or Manage access to Development. This ensures sensitive data cannot be drawn out of production and granted to role that should not have access to it|Protection of sensitive data| |#2|Engineering roles do not need policies that provide Manage permissions to Development databases. These Engineering roles should have the Developement ‘Object Owner’ role granted to them to provide those Manage permissions.|Barbossa performance optimisation through reduction in policies to process|