Motivation

If you manage access control for hundreds or thousands of users, tables, and roles, you’ve probably thought about different ways to organize roles and grants.

Here’s one battle-tested technique for segmenting your roles:

Functional roles are high level roles that are granted to users. These roles tend to map to job duties; they are the user-facing roles of your RBAC system.

Access roles are low level roles that contain permissions (e.g. usage, ownership, select, insert) to objects like databases, schemas, tables, views, etc. They are the building blocks of functional roles.

For example, a Finance Team functional role inherits from Accounting Viewer and Payments Viewer access roles, which have the select permission on the Accounting Database and Payments Database, respectively.

Benefits

This approach has a few benefits: consistency, reduction of manual provisioning, and usability.

Consistency

Every database (or schema, etc.) has an identical set of default access roles: Viewer, Editor, and Owner. So when creating or updating a functional role, you can always compose it using uniform building blocks (with similar granularities).

Reduction of Manual Provisioning (DRY)

When granting access to tables, it’s common to repeat a lot of the same grants over and over:

• GRANT USAGE ON DATABASE …
• GRANT USAGE ON SCHEMA …
• GRANT SELECT ON TABLE …

If you forget any of these grants, access fails and is difficult to debug. Access roles are programmatically generated, which reduces the amount of boilerplate roles you need to provision manually.  Less time debugging grants, more time for other tasks that actually need your attention.

Usability

Traditional Roles

In a traditional role-based access control system, a user will be granted access to many roles.  

In this example, employee Kevin has been granted access to 7 separate roles in order to be equipped for his real-life job function.

Functional Roles

Conversely, when using functional roles, you reduce the “role surface” visible to users. These functional roles are logical consolidations of access, which make your system easier to understand and more auditable.

Employee Kevin now has only two functional roles granted to him, which encompass the same access as the traditional role system. Only now, the access roles are in the background and require less manual manipulation.

Policy-driven access management

We’ve previously discussed Policy Driven Controls and its benefits:

• Team-oriented access policies like “members of the finance team can read finance data.”
• Compliance-oriented access policies like “GDPR scoped users can request short-lived access to GDPR scoped data.”

Functional roles are flexible enough to enable policy-driven access management. This approach can also help you clean up your existing role structure:

• Prune roles with overlapping responsibilities to the same data.
• Revoke access for over-provisioned users who have access but aren’t using it.
• Track anomalous data usage and audit access based on compliance goals.
• Empower business teams to govern their data with tagging.

Further Reading

For more perspective on how others in the Snowflake ecosystem are addressing role structures, check out Snowflake’s Aligning Object Access with Business Functions documentation and SnowDDL’s Role hierarchy documentation.

About Spyglass

Since you’re here, let me tell you what we’ve cooked up at Spyglass. In short, we make Snowflake data access controls easy - or provide an automated and better way to do the above.

If you’ve nodded your head while reading this, reach out at spyglass.software (or demo@spyglass.software) and we’ll show you a product demo to give you a taste of the future of data access management.

‍