Mastering the Basics With Snowflake RBAC, Part II: Delegating Down

Over the weekend I was configuring a storage integration for a sandbox environment that we use here at CoEnterprise, and it just wasn’t working. What should have been a straightforward connection to an AWS bucket was starting to run into six different flavors of access denied issues. Was something wrong in AWS? Was it the S3 bucket? The policy JSON? A forgotten permission in Snowflake? An hour ticked by. Then another. It was extremely frustrating—on a Sunday morning no less.

So I started to play with permissions, thoughtfully at first, but then frustrated and scattershot when nothing worked. And eventually, I figured it out and everything came together. But the exercise required ACCOUNTADMIN permissions to configure and tweak, and I’d littered a trail of Hail Mary permissions and grants behind me. It was a sandbox environment, but, still, not good—but not that unusual an outcome either. I’m sure something like this has never happened to you.

The ACCOUNTADMIN role is a magnet for excess, but best practices instruct us to keep it as pristine as the day your Snowflake account was launched wherever a permission can’t be granted to any other role. No role gathers as many unwanted permissions, users, and roles as the ACCOUNTADMIN. Without care, there will be databases, schemas, roles, and users mapped to it that make it increasingly necessary to keep using ACCOUNTADMIN to manage your account—and around it goes. And you can’t get off.

Let’s look at a model of what best and worst practices look like, examine some queries that will help you audit your ACCOUNTADMIN, and provide a strategic framework to delegate grants down.

Here’s an interactive model of the admin roles with some representative permissions, objects, and users mapped to them. Take a moment to filter and un-filter the objects with the controls at bottom and you can highlight problematic relationships that should be delegated to other roles. This model could be built for your environment specifically (let us know if you’re interested in mapping your own environment) but the relationships are easiest to see in their rudimentary form.

What we see in the model are the following issues:

1. Two extraneous, non-administrative roles mapped to ACCOUNTADMIN that should be shared lower in the hierarchy
2. A user that is no longer an account administrator mapped to the ACCOUNTADMIN role
3. Ownership of databases, schemas, and objects that belong at the SYSADMIN role mapped to the ACCOUNTADMIN

In an actual account, the scale of these problems can be substantially larger. The solution for the problems above is tedious, but important and straightforward. 

OWNERSHIP needs to be pushed down to the right level. USAGE needs to be removed and grants unwound wherever possible. The ACCOUNTADMIN role will naturally inherit these permissions and has no need of explicitly mapping them if it even needs them at all in practice. Does the ACCOUNTADMIN actually need ownership of a database?

A little housekeeping on the admin roles will push security permissions down into SECURITYADMIN and, possibly, even further down into the USERADMIN role if you’re leveraging it. 

Object creation and administration should fall to the SYSADMIN role and any subordinate SYSADMIN environment roles created beneath it, (e.g. DEV_SYSADMIN).

So what does belong in the ACCOUNTADMIN role? Here’s a look at the default permissions for the ACCOUNTADMIN (minus views onto the SNOWFLAKE catalogs). We’ll use this list of core ACCOUNTADMIN privileges in the next query to look for out-of-place permissions:

The query below is a useful starting place for looking at what grants on the ACCOUNTADMIN can and should be delegated down. First, it takes the full set of grants to the admin role that are not “out of the box,” and then removes all grants appropriately associated with the core responsibilities of the ACCOUNTADMIN. To avoid eliminating instances of the grants ACCOUNTADMIN should be giving itself, we filter by ACCOUNTADMIN’s global privileges in the previous diagram.

Next, we dive into the Snowflake ACCOUNT_USAGE table to find our problem grants. Note that the results here are a conceptual starting place rather than a definitive list. You will see grants for RESOURCE MONITORs and the like that should remain with the ACCOUNTADMIN role.

use database "SNOWFLAKE";
use schema "SNOWFLAKE"."ACCOUNT_USAGE";
show grants to role ACCOUNTADMIN; 

select privilege,
       granted_on,
       name,
       table_catalog,
       grantee_name
  from "GRANTS_TO_ROLES" admin_grants
 where grantee_name = 'ACCOUNTADMIN' 
   and not (privilege = 'USAGE' and granted_on = 'ROLE' and 'NAME' not in ('SECURITYADMIN', 'SYSADMIN'))
   and deleted_on is null
   and not exists (select distinct "privilege" from table(result_scan(last_query_id())) as admin_global
                    where "grantee_name" = 'ACCOUNTADMIN' 
                      and "granted_by" = '' 
                      and "name" not ilike 'SNOWFLAKE%'
                      and "granted_on" <> 'ROLE'
                      and admin_global."privilege" = admin_grants.privilege );

There’s a saying that the more you handle the fine china, the more likely you are to break it. Avoiding use of the ACCOUNTADMIN role is no different. Most tasks can be done using roles with fewer permissions—not to mention the unnecessary involvement of your highest-level database administrators in lower-level tasks.

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

Do you have ACCOUNTADMINs with no default role assigned, or worse, a default role of ACCOUNTADMIN? Set default roles for each of your ACCOUNTADMINs to roles with the lowest level of permissions possible among their entitled roles.

.. and a query

select role, grantee_name, default_role 
from "SNOWFLAKE"."ACCOUNT_USAGE"."GRANTS_TO_USERS" join "SNOWFLAKE"."ACCOUNT_USAGE"."USERS" 
on users.name = grants_to_users.grantee_name 
where role = 'ACCOUNTADMIN' 
and grants_to_users.deleted_on is null 
and users.deleted_on is null  
order by grantee_name;

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 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.