Control: Ensure an email address is specified for users with ACCOUNTADMIN role

Description

Snowflake recommendsto associate an actual person's email address to ACCOUNTADMIN users, so that Snowflake Support knows who to contact in an urgent situation.

Usage

Run the control in your terminal:

powerpipe control run snowflake_compliance.control.security_overview_iam_user_with_accountadmin_role_have_email

Snapshot and share results via Turbot Pipes:

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

SQL

This control uses a named query:

with users_with_account_admin_role as (
  select
    grantee_name
  from
    snowflake_role_grant
  where
    role = 'ACCOUNTADMIN'
    and granted_to = 'USER'
)
select
  name as resource,
  case
    when name not in (select * from users_with_account_admin_role) then 'skip'
    when email != '' then 'ok'
    else 'alarm'
  end as status,
  case
    when name not in (select * from users_with_account_admin_role) then name || ' does not have ACCOUNTADMIN role.'
    when email != '' then name || ' email address set.'
    else name || ' email address not set.'
  end as reason,
  account
from
  snowflake_user;