turbot/terraform_oci_compliance
GitHub

Query: vcn_network_security_group_restrict_ingress_ssh_all

Usage

steampipe query terraform_oci_compliance.query.vcn_network_security_group_restrict_ingress_ssh_all

Plugins & 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: