turbot/terraform_oci_compliance

Query: vcn_network_security_group_restrict_ingress_ssh_all

Usage

powerpipe query terraform_oci_compliance.query.vcn_network_security_group_restrict_ingress_ssh_all

Steampipe Tables

SQL

with all_sg_security_rule as (
select
*
from
terraform_resource
where
type = 'oci_core_network_security_group_security_rule'
), all_sg as (
select
*
from
terraform_resource
where
type = 'oci_core_network_security_group'
), non_complaint as (
select
attributes_std ->> 'network_security_group_id' as nsg_id,
count(*) as count
from
all_sg_security_rule
where
attributes_std ->> 'direction' = 'INGRESS'
and attributes_std ->> 'source_type' = 'CIDR_BLOCK'
and attributes_std ->> 'source' = '0.0.0.0/0'
and (
attributes_std ->> 'protocol' = 'all'
or (
(attributes_std -> 'tcp_options' -> 'destination_port_range' ->> 'min')::integer <= 22
and (attributes_std -> 'tcp_options' -> 'destination_port_range' ->> 'max')::integer >= 22
)
)
group by nsg_id
)
select
a.address as resource,
case
when (split_part(b.nsg_id , '.', 2)) is null then 'ok'
else 'alarm'
end as status,
split_part(a.address, '.', 2) || case
when (split_part(b.nsg_id , '.', 2)) is null then ' ingress restricted for SSH from 0.0.0.0/0'
else ' ingress rule(s) allowing SSH from 0.0.0.0/0'
end || '.' reason
, path || ':' || start_line
from
all_sg as a
left join non_complaint as b on a.name = (split_part(b.nsg_id , '.', 2));

Controls

The query is being used by the following controls: