turbot/aws_compliance

Query: iam_policy_all_attached_no_star_star

Usage

powerpipe query aws_compliance.query.iam_policy_all_attached_no_star_star

Steampipe Tables

SQL

with star_access_policies as (
select
arn,
is_aws_managed,
count(*) as num_bad_statements
from
aws_iam_policy,
jsonb_array_elements(policy_std -> 'Statement') as s,
jsonb_array_elements_text(s -> 'Resource') as resource,
jsonb_array_elements_text(s -> 'Action') as action
where
s ->> 'Effect' = 'Allow'
and resource = '*'
and (
(action = '*'
or action = '*:*'
)
)
and is_attached
group by
arn,
is_aws_managed
)
select
p.arn as resource,
case
when s.arn is not null and s.is_aws_managed then 'info'
when s.arn is null then 'ok'
else 'alarm'
end status,
case
when s.arn is not null and s.is_aws_managed then p.name || ' is an AWS managed policy with ' || coalesce(s.num_bad_statements, 0) || ' statements that allow action "*" on resource "*".'
else p.name || ' contains ' || coalesce(s.num_bad_statements, 0) || ' statements that allow action "*" on resource "*".'
end as reason
, p.account_id
from
aws_iam_policy as p
left join star_access_policies as s on p.arn = s.arn
where
p.is_attached;

Controls

The query is being used by the following controls: