steampipe plugin install oci

Table: oci_core_network_security_group - Query OCI Core Network Security Groups using SQL

A Network Security Group in OCI Core service is a virtual firewall for your virtual network resources. It provides inbound and outbound security rules that specify the type of traffic and the ports on which the traffic is allowed. Network Security Groups are associated with a Virtual Cloud Network (VCN) and can be associated with specific subnets or a specific set of Compute instances.

Table Usage Guide

The oci_core_network_security_group table provides insights into the Network Security Groups within OCI Core service. As a security analyst, you can explore group-specific details through this table, including security rules, associated resources, and other metadata. Leverage this table to identify potential security risks, such as overly permissive security rules and to ensure compliance with your organization's security policies.

Examples

Basic info

Explore which network security groups are active within your system and when they were created. This can help maintain security standards and identify any potentially unauthorized or outdated groups.

select
display_name,
id,
vcn_id,
lifecycle_state as state,
time_created
from
oci_core_network_security_group;
select
display_name,
id,
vcn_id,
lifecycle_state as state,
time_created
from
oci_core_network_security_group;

List NSGs that are not available

Discover the segments that are not currently available in your network security groups. This is useful to assess the elements within your network that might be causing issues due to their unavailability.

select
display_name,
id,
lifecycle_state as state
from
oci_core_network_security_group
where
lifecycle_state <> 'AVAILABLE';
select
display_name,
id,
lifecycle_state as state
from
oci_core_network_security_group
where
lifecycle_state <> 'AVAILABLE';

Count the number of rules per NSG

  1. "Explore the distribution of rules across different Network Security Groups (NSG) in your environment."
  2. "Identify Network Security Groups (NSGs) in your environment that have unrestricted inbound access from the internet."
  3. "Uncover Network Security Groups (NSGs) in your environment that have unrestricted SSH and RDP access from the internet.
select
display_name,
id,
jsonb_array_length(rules) as rules_count
from
oci_core_network_security_group;
select
display_name,
id,
json_array_length(rules) as rules_count
from
oci_core_network_security_group;

List NSGs whose inbound access is open to the internet

select
display_name,
id,
r ->> 'direction' as direction,
r ->> 'sourceType' as source_type,
r ->> 'source' as source
from
oci_core_network_security_group,
jsonb_array_elements(rules) as r
where
r ->> 'direction' = 'INGRESS'
and r ->> 'sourceType' = 'CIDR_BLOCK'
and r ->> 'source' = '0.0.0.0/0'
select
display_name,
id,
json_extract(r.value, '$.direction') as direction,
json_extract(r.value, '$.sourceType') as source_type,
json_extract(r.value, '$.source') as source
from
oci_core_network_security_group,
json_each(rules) as r
where
json_extract(r.value, '$.direction') = 'INGRESS'
and json_extract(r.value, '$.sourceType') = 'CIDR_BLOCK'
and json_extract(r.value, '$.source') = '0.0.0.0/0'

List NSG whose SSH and RDP access is not restricted from the internet

select
display_name,
id,
r ->> 'direction' as direction,
r ->> 'sourceType' as source_type,
r ->> 'source' as source,
r ->> 'protocol' as protocol,
r -> 'tcpOptions' -> 'destinationPortRange' ->> 'max' as min_port_range,
r -> 'tcpOptions' -> 'destinationPortRange' ->> 'min' as max_port_range
from
oci_core_network_security_group,
jsonb_array_elements(rules) as r
where
r ->> 'direction' = 'INGRESS'
and r ->> 'sourceType' = 'CIDR_BLOCK'
and r ->> 'source' = '0.0.0.0/0'
and (
(r ->> 'protocol' = 'all')
or (
(r -> 'tcpOptions' -> 'destinationPortRange' ->> 'min') :: integer <= 22
and (r -> 'tcpOptions' -> 'destinationPortRange' ->> 'max') :: integer >= 22
)
or (
(r -> 'tcpOptions' -> 'destinationPortRange' ->> 'min') :: integer <= 3389
and (r -> 'tcpOptions' -> 'destinationPortRange' ->> 'max') :: integer >= 3389
)
);
select
display_name,
id,
json_extract(r.value, '$.direction') as direction,
json_extract(r.value, '$.sourceType') as source_type,
json_extract(r.value, '$.source') as source,
json_extract(r.value, '$.protocol') as protocol,
json_extract(r.value, '$.tcpOptions.destinationPortRange.max') as min_port_range,
json_extract(r.value, '$.tcpOptions.destinationPortRange.min') as max_port_range
from
oci_core_network_security_group,
json_each(rules) as r
where
json_extract(r.value, '$.direction') = 'INGRESS'
and json_extract(r.value, '$.sourceType') = 'CIDR_BLOCK'
and json_extract(r.value, '$.source') = '0.0.0.0/0'
and (
(json_extract(r.value, '$.protocol') = 'all')
or (
cast(
json_extract(r.value, '$.tcpOptions.destinationPortRange.min') as integer
) <= 22
and cast(
json_extract(r.value, '$.tcpOptions.destinationPortRange.max') as integer
) >= 22
)
or (
cast(
json_extract(r.value, '$.tcpOptions.destinationPortRange.min') as integer
) <= 3389
and cast(
json_extract(r.value, '$.tcpOptions.destinationPortRange.max') as integer
) >= 3389
)
);

Count the number of NSGs per VCN

Explore which Virtual Cloud Networks (VCNs) have the most Network Security Groups (NSGs) to understand your cloud network's security distribution. This can help in assessing the security coverage and identifying areas that might need additional security measures.

select
vcn_id,
count(id) as no_of_nsg
from
oci_core_network_security_group
group by
vcn_id;
select
vcn_id,
count(id) as no_of_nsg
from
oci_core_network_security_group
group by
vcn_id;

Schema for oci_core_network_security_group

NameTypeOperatorsDescription
_ctxjsonbSteampipe context in JSON form.
compartment_idtext=The OCID of the compartment in Tenant in which the resource is located.
defined_tagsjsonbDefined tags for resource. Defined tags are set up in your tenancy by an administrator. Only users granted permission to work with the defined tags can apply them to resources.
display_nametext=A user-friendly name. Does not have to be unique.
freeform_tagsjsonbFree-form tags for resource. This tags can be applied by any user with permissions on the resource.
idtext=The OCID of the network security group.
lifecycle_statetext=The network security group's current state.
regiontextThe OCI region in which the resource is located.
rulesjsonbLists of security rules in the specified network security group.
sp_connection_nametext=, !=, ~~, ~~*, !~~, !~~*Steampipe connection name.
sp_ctxjsonbSteampipe context in JSON form.
tagsjsonbA map of tags for the resource.
tenant_idtext=, !=, ~~, ~~*, !~~, !~~*The OCID of the Tenant in which the resource is located.
tenant_nametextThe name of the Tenant in which the resource is located.
time_createdtimestamp with time zoneThe date and time the network security group was created.
titletextTitle of the resource.
vcn_idtext=The OCID of the network security group's VCN.

Export

This table is available as a standalone Exporter CLI. Steampipe exporters are stand-alone binaries that allow you to extract data using Steampipe plugins without a database.

You can download the tarball for your platform from the Releases page, but it is simplest to install them with the steampipe_export_installer.sh script:

/bin/sh -c "$(curl -fsSL https://steampipe.io/install/export.sh)" -- oci

You can pass the configuration to the command with the --config argument:

steampipe_export_oci --config '<your_config>' oci_core_network_security_group