Control: At least two users must be assigned ACCOUNTADMIN role

Description

By default, each account has one user who has been designated as an account administrator (i.e. user granted the system-defined ACCOUNTADMIN role). Snowflake recommend designating at least one other user as an account administrator. This helps ensure that your account always has at least one user who can perform account-level tasks, particularly if one of your account administrators is unable to log in.

Usage

Run the control in your terminal:

powerpipe control run snowflake_compliance.control.security_overview_iam_two_users_accountadmin_role

Snapshot and share results via Turbot Pipes:

powerpipe login
powerpipe control run snowflake_compliance.control.security_overview_iam_two_users_accountadmin_role --share

SQL

This control uses a named query:

with users_with_account_admin_role as (
  select
    role,
    granted_to,
    grantee_name,
    granted_by,
    created_on,
    account
  from
    snowflake_role_grant
  where
    role = 'ACCOUNTADMIN'
    and granted_to = 'USER'
)
select
  account as resource,
  case when count(grantee_name) > 1 then
    'ok'
  else
    'alarm'
  end as status,
  'ACCOUNTADMIN role is granted to ' || count(grantee_name) || ' user(s).' as reason,
  account
from
  users_with_account_admin_role
group by
  account;