turbot/aws_compliance

Query: iam_security_audit_role

Usage

powerpipe query aws_compliance.query.iam_security_audit_role

Steampipe Tables

SQL

with security_audit_role_count as(
select
'arn:' || a.partition || ':::' || a.account_id as resource,
count(policy_arn),
a.account_id,
a._ctx
from
aws_account as a
left join aws_iam_role as r on r.account_id = a.account_id
left join jsonb_array_elements_text(attached_policy_arns) as policy_arn on true
where
policy_arn = 'arn:aws:iam::aws:policy/SecurityAudit'
group by
a.account_id,
a.partition,
a._ctx
)
select
resource,
case
when count > 0 then 'ok'
else 'alarm'
end as status,
case
when count = 1 then 'SecurityAudit policy attached to 1 role.'
when count > 1 then 'SecurityAudit policy attached to ' || count || ' roles.'
else 'SecurityAudit policy not attached to any role.'
end as reason
, account_id
from
security_audit_role_count;

Controls

The query is being used by the following controls: