Mastering the Basics With Snowflake RBAC: Part I

Once upon a time, you kicked off a trial Snowflake account. It was an immediate hit. Long before the free trial ran out, developers were already framing solutions for lingering problems of performance and broken SLAs. New possibilities were uncovered sharing data and manipulating semi-structured data.

A deathly slow Alteryx workflow in marketing began to run at astonishing speeds. The trial version turned into a full-fledged production account, workloads were added across cloud providers and regions. And all was happy in your snowy data kingdom.

Well, not exactly.

One day you noticed that the number of users who were account administrators was jaw-dropping. Separating developers from your production environment and vice versa was impossible without the risk of bringing work to a grinding halt. Databases were created that SYSADMINS didn’t know about and then couldn’t see.

Worst of all, a partner warned you that she could see data she probably shouldn’t and not so politely asked to revert to SFTP. Roles and users and privileges weren’t growing, they were metastasizing. What’s worse is you could feel it, but you couldn’t see it.

Under the hood, your permissions structures probably were starting to look something like this graph visualization of the grant relationships in an ungoverned Snowflake sandbox environment:

In the visualization of a simple POC Snowflake, you see only a small section of the hairball of grants and relationships a successful Snowflake implementation needs to wrangle, govern–and in this instance, avoid.

When you zoom out, the challenge of successfully planning, governing, and auditing an environment become almost overwhelming. This is what you’re really up against. The diagram below demonstrates the actual relationship between all users, roles, and grants in that same sandbox environment.

Every line is a grant.

If you haven’t already found a way to master this list, then it’s time to tackle it–and there’s a place to start.

• Untangle Snowflake RBAC
• Govern the five major pre-built accounts
• Map roles to business functions
• Quickly spin up new environments with appropriate permissions
• Work with managed access schemas

If you’re just getting started on your snowy data kingdom, then this is the time to get out in front of Snowflake RBAC and security best practices.

In this blog series, we’ll look at practical steps to audit, simplify, and batten down permissions. We’ll drill into these structures, find and discover critical issues, and address them.

These practices structuring access control are multi-layered and a lot to bite off in one go so we’ll tackle them progressively. In the next post, we’ll look at basics to understanding and cleaning up the five out-of-the-box roles: ACCOUNTADMIN, SECURITYADMIN, USERADMIN, SYSADMIN, and PUBLIC

In the meantime, some critical homework on a baseline best practice: at minimum make sure your ACCOUNTADMINs have set themselves up with dual factor authentication. Ideally, have each of your users that has administrative rights be configured for MFA through Snowflake or your IdP.

And with that, I’ll sign off with a question:

What does your Snowflake grant universe look like?

… and a query:

/*
If using built-in MFA through Snowflake, audit whether all administrative roles are configured for MFA. Then ensure that, at minimum, the ACCOUNTADMIN roles have been. Note that MFA enabled externally cannot be identified directly through Snowflake and should be validated separately.
*/

select g.role, u.name, u.ext_authn_duo, u.disabled, u.last_success_login
from "SNOWFLAKE"."ACCOUNT_USAGE"."USERS" u
inner join "SNOWFLAKE"."ACCOUNT_USAGE"."GRANTS_TO_USERS" g
on u.name = g.grantee_name
where u.ext_authn_duo = 'false' and u.disabled = 'false' and upper(g.role) like '%ADMIN%';

Want to see how you can take your Snowflake migration to the next level? Learn more about how our experts can help your business today.

About the Author

Adam Nathan is the Director of Sales Engineering at CoEnterprise and leads our consultancy’s Snowflake practice. With over two decades in data warehousing, business intelligence, and advanced analytics, he’s passionate about supporting clients in the adoption and growth of their Snowflake implementations. Outreach for Snowflake RBAC consulting support, thoughtful suggestions, and irate critiques on these posts are all welcome, in that order. You can reach him at adam.nathan@coenterprise.com.