turbot/aws_compliance

Query: iam_inline_policy_no_administrative_privileges

Usage

powerpipe query aws_compliance.query.iam_inline_policy_no_administrative_privileges

SQL

with full_administrative_privilege_policies as (
select
arn,
inline_policies_std,
name,
account_id,
region,
_ctx,
'iam_user' as type
from
aws_iam_user
union
select
arn,
inline_policies_std,
name,
account_id,
region,
_ctx,
'iam_role' as type
from
aws_iam_role
union
select
arn,
inline_policies_std,
name,
account_id,
region,
_ctx,
'iam_group' as type
from
aws_iam_group
),
bad_policies as (
select
arn,
count(*) as statements_num
from
full_administrative_privilege_policies,
jsonb_array_elements(inline_policies_std) as policy_std,
jsonb_array_elements(policy_std -> 'PolicyDocument' -> '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 = '*:*'
)
)
group by
arn
)
select
p.arn as resource,
case
when bad.arn is null then 'ok'
else 'alarm'
end status,
p.name || ' contains ' || coalesce(bad.statements_num,0) ||
' statements that allow action "*" on resource "*".' as reason
, p.account_id
from
full_administrative_privilege_policies as p
left join bad_policies as bad on p.arn = bad.arn;

Controls

The query is being used by the following controls: