turbot/azure_compliance

Query: iam_subscription_owner_max_3

Usage

powerpipe query azure_compliance.query.iam_subscription_owner_max_3

SQL

with owner_roles as (
select
d.role_name,
d.role_type,
d.name,
d.title,
d._ctx,
d.subscription_id
from
azure_role_definition as d
join azure_role_assignment as a on d.id = a.role_definition_id
where
d.role_name = 'Owner'
)
select
owner.subscription_id as resource,
case
when count(*) <= 3 then 'ok'
else 'alarm'
end as status,
count(*) || ' owner(s) associated.' as reason
, sub.display_name as subscription
from
owner_roles as owner,
azure_subscription as sub
where
sub.subscription_id =owner.subscription_id
group by
owner.subscription_id,
owner._ctx,
sub.display_name;

Controls

The query is being used by the following controls: