turbot/aws_insights

Query: iam_roles_allow_all_action

Usage

powerpipe query aws_insights.query.iam_roles_allow_all_action

SQL

with roles_allow_all_actions as (
select
r.name as role_name,
r.account_id as account_id,
p.name as policy_name
from
aws_iam_role as r,
jsonb_array_elements_text(r.attached_policy_arns) as policy_arn,
aws_iam_policy as p,
jsonb_array_elements(p.policy_std -> 'Statement') as stmt,
jsonb_array_elements_text(stmt -> 'Action') as action
where
policy_arn = p.arn
and stmt ->> 'Effect' = 'Allow'
and action = '*'
order by
r.name
),
all_action as (
select
case
when c.role_name is not null then 'allows all actions'
else 'limited actions'
end as allow_all_action
from
aws_iam_role as r
left join roles_allow_all_actions as c on c.role_name = r.name
)
select
allow_all_action,
count(*)
from
all_action
group by
allow_all_action;

Dashboards

The query is used in the dashboards: