Query: iam_subscriptions_with_custom_roles_no_overly_permissive

with custom_roles as (
  select
    role_name,
    role_type,
    title,
    action,
    _ctx,
    subscription_id
  from
    azure_role_definition,
    jsonb_array_elements(permissions) as s,
    jsonb_array_elements_text(s -> 'actions') as action
  where
    role_type = 'CustomRole'
    and assignable_scopes @> '["/"]'
    and action in ('*', '*:*')
)
select
  cr.subscription_id as resource,
  case
    when count(*) > 0 then 'alarm'
    else 'ok'
  end as status,
  case
    when count(*) = 1 then 'There is one subscription where custom roles are overly permissive.'
    when count(*) > 1 then 'There are ' || count(*) || ' subscriptions where custom roles are overly permissive.'
    else 'There is no subscription where custom roles are overly permissive.'
  end as reason
  
  , sub.display_name as subscription
from
  custom_roles cr,
  azure_subscription sub
where
  sub.subscription_id = cr.subscription_id
group by
  cr.subscription_id,
  cr._ctx,
  sub.display_name;