In Part 1, we covered how Snowflake access control works: privileges, roles, the role hierarchy, database roles, ownership and secondary roles.
Knowing the pieces is one thing. Arranging them so that access stays clear, auditable and easy to change is another. Without a plan, most accounts end up with hundreds of roles, grants scattered everywhere, and nobody sure who can see what.
This post builds a design that avoids that:
The pattern: access roles + functional roles
Naming conventions that make roles self-explanatory
Future grants, so new tables are covered automatically
Managed access schemas, so grants are controlled centrally
Hands-on: building the whole design for a production database, then testing and auditing it
Part 1: The Pattern
Two kinds of custom role
The widely recommended design separates roles into two kinds:
