turbot/aws_compliance

Query: iam_policy_unused

Usage

powerpipe query aws_compliance.query.iam_policy_unused

SQL

with in_use_policies as (
select
attached_policy_arns
from
aws_iam_user
union
select
attached_policy_arns
from
aws_iam_group
where
jsonb_array_length(users) > 0
union
select
attached_policy_arns
from
aws_iam_role
)
select
arn as resource,
case
when arn in (
select
jsonb_array_elements_text(attached_policy_arns)
from
in_use_policies
) then 'ok'
else 'alarm'
end as status,
case
when arn in (
select
jsonb_array_elements_text(attached_policy_arns)
from
in_use_policies
) then title || ' in use.'
else title || ' not in use.'
end as reason,
account_id
from
aws_iam_policy;

Controls

The query is being used by the following controls: