turbot/aws_compliance

Query: ec2_instance_no_iam_role_with_org_write_access

Usage

powerpipe query aws_compliance.query.ec2_instance_no_iam_role_with_org_write_access

SQL

with iam_roles as (
select
r.arn as role_arn,
i.arn as intance_arn
from
aws_iam_role as r,
jsonb_array_elements_text(instance_profile_arns) as p
left join aws_ec2_instance as i on p = i.iam_instance_profile_arn
where
i.arn is not null
),
iam_role_with_permission as (
select
arn
from
aws_iam_role,
jsonb_array_elements(assume_role_policy_std -> 'Statement') as s,
jsonb_array_elements_text(s -> 'Principal' -> 'Service') as service,
jsonb_array_elements_text(s -> 'Action') as action
where
arn in (
select
role_arn
from
iam_roles
)
and s ->> 'Effect' = 'Allow'
and service = 'ec2.amazonaws.com'
and (
(
action in (
'organizations:accepthandshake',
'organizations:attachpolicy',
'organizations:cancelhandshake',
'organizations:createaccount',
'organizations:creategovcloudaccount',
'organizations:createorganization',
'organizations:createorganizationalunit',
'organizations:createpolicy',
'organizations:declinehandshake',
'organizations:deleteorganization',
'organizations:deleteorganizationalunit',
'organizations:deletepolicy',
'organizations:deregisterdelegatedadministrator',
'organizations:detachpolicy',
'organizations:disableawsserviceaccess',
'organizations:disablepolicytype',
'organizations:enableawsserviceaccess',
'organizations:enableallfeatures',
'organizations:enablepolicytype',
'organizations:inviteaccounttoorganization',
'organizations:Leaveorganization',
'organizations:moveaccount',
'organizations:registerdelegatedadministrator',
'organizations:removeaccountfromorganization',
'organizations:updateorganizationalunit',
'organizations:updatepolicy',
'*:*'
)
)
)
)
select
i.arn as resource,
case
when p.arn is null then 'ok'
else 'alarm'
end status,
case
when p.arn is null then title || ' has no organization write access.'
else title || ' has organization write access.'
end as reason,
i.account_id
from
aws_ec2_instance as i
left join iam_roles as r on r.intance_arn = i.arn
left join iam_role_with_permission as p on p.arn = r.role_arn;

Controls

The query is being used by the following controls: