turbot/azure_compliance

Query: iam_subscriptions_with_custom_roles_no_overly_permissive

Usage

powerpipe query azure_compliance.query.iam_subscriptions_with_custom_roles_no_overly_permissive

SQL

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;

Controls

The query is being used by the following controls: