turbot/azure_compliance

Query: iam_user_no_built_in_contributor_role

Usage

powerpipe query azure_compliance.query.iam_user_no_built_in_contributor_role

SQL

with all_contributor_permission_users as (
select
distinct
u.display_name,
d.role_name,
u.account_enabled,
u.user_principal_name,
d.subscription_id
from
azuread_user as u
left join azure_role_assignment as a on a.principal_id = u.id
left join azure_role_definition as d on d.id = a.role_definition_id
where
d.role_name = 'Contributor'
), distinct_tenant as (
select
distinct tenant_id,
subscription_id,
_ctx
from
azure_tenant
)
select
u.user_principal_name as resource,
case
when c.user_principal_name is not null then 'alarm'
else 'ok'
end as status,
case
when c.user_principal_name is not null then u.display_name || ' has contributor role assigned.'
else u.display_name || ' does not have contributor role assigned.'
end as reason,
t.tenant_id
from
distinct_tenant as t,
azuread_user as u left join all_contributor_permission_users as c on c.user_principal_name = u.user_principal_name;

Controls

The query is being used by the following controls: