3b.1.102. Security Lists per Subnet (OCI360_SECLISTS)

#	SUBNET_NAME	SECLIST_NAME	VCN_NAME	COMPARTMENT_NAME	SECLIST_ID	SUBNET_ID
1	Private Subnet-vcn_test	Security List for Private Subnet-vcn_test	vcn_test	Test	ocid1.securitylist.oc1.iad.000000000000000000000000000000000000000000000000000000000656	ocid1.subnet.oc1.iad.000000000000000000000000000000000000000000000000000000000669
2	Public Subnet-vcn_test	Default Security List for vcn_test	vcn_test	Test	ocid1.securitylist.oc1.iad.000000000000000000000000000000000000000000000000000000000652	ocid1.subnet.oc1.iad.000000000000000000000000000000000000000000000000000000000674
3	Public Subnet	Default Security List for vcn-20200428-1946	vcn-20200428-1946	compt_dev_app	ocid1.securitylist.oc1.iad.000000000000000000000000000000000000000000000000000000000647	ocid1.subnet.oc1.iad.000000000000000000000000000000000000000000000000000000000671
4	subnet_dev_app	sl_subnet_dev_app	vcn_dev	compt_ss_net	ocid1.securitylist.oc1.iad.000000000000000000000000000000000000000000000000000000000645	ocid1.subnet.oc1.iad.000000000000000000000000000000000000000000000000000000000672
5	subnet_dev_db	sl_subnet_dev_db	vcn_dev	compt_ss_net	ocid1.securitylist.oc1.iad.000000000000000000000000000000000000000000000000000000000657	ocid1.subnet.oc1.iad.000000000000000000000000000000000000000000000000000000000677
6	subnet_dev_dmz	sl_subnet_dev_dmz	vcn_dev	compt_ss_net	ocid1.securitylist.oc1.iad.000000000000000000000000000000000000000000000000000000000655	ocid1.subnet.oc1.iad.000000000000000000000000000000000000000000000000000000000675
7	subnet_hml_app	sl_subnet_hml_app	vcn_hml	compt_ss_net	ocid1.securitylist.oc1.iad.000000000000000000000000000000000000000000000000000000000646	ocid1.subnet.oc1.iad.000000000000000000000000000000000000000000000000000000000676
8	subnet_hml_db	sl_subnet_hml_db	vcn_hml	compt_ss_net	ocid1.securitylist.oc1.iad.000000000000000000000000000000000000000000000000000000000659	ocid1.subnet.oc1.iad.000000000000000000000000000000000000000000000000000000000673
9	subnet_hml_dmz	sl_subnet_hml_dmz	vcn_hml	compt_ss_net	ocid1.securitylist.oc1.iad.000000000000000000000000000000000000000000000000000000000644	ocid1.subnet.oc1.iad.000000000000000000000000000000000000000000000000000000000680
10	subnet_hub_dmz	sl_subnet_hub_dmz	vcn_hub	compt_ss_net	ocid1.securitylist.oc1.iad.000000000000000000000000000000000000000000000000000000000648	ocid1.subnet.oc1.iad.000000000000000000000000000000000000000000000000000000000678
11	subnet_prd_app	sl_subnet_prd_app	vcn_prd	compt_ss_net	ocid1.securitylist.oc1.iad.000000000000000000000000000000000000000000000000000000000658	ocid1.subnet.oc1.iad.000000000000000000000000000000000000000000000000000000000670
12	subnet_prd_db	sl_subnet_prd_db	vcn_prd	compt_ss_net	ocid1.securitylist.oc1.iad.000000000000000000000000000000000000000000000000000000000650	ocid1.subnet.oc1.iad.000000000000000000000000000000000000000000000000000000000668
13	subnet_prd_dmz	sl_subnet_prd_dmz	vcn_prd	compt_ss_net	ocid1.securitylist.oc1.iad.000000000000000000000000000000000000000000000000000000000660	ocid1.subnet.oc1.iad.000000000000000000000000000000000000000000000000000000000679

#: click on a column heading to sort on it

 Name                                                                                Null?    Type
 ----------------------------------------------------------------------------------- -------- --------------------------------------------------------
 ID                                                                                           VARCHAR2(128)
 VCN_ID                                                                                       VARCHAR2(128)
 DEFINED_TAGS$ORACLE_TAGS$CREATEDBY                                                           VARCHAR2(64)
 DEFINED_TAGS$ORACLE_TAGS$CREATEDON                                                           VARCHAR2(32)
 DISPLAY_NAME                                                                                 VARCHAR2(64)
 TIME_CREATED                                                                                 VARCHAR2(32)
 FREEFORM_TAGS$VCN                                                                            VARCHAR2(32)
 COMPARTMENT_ID                                                                               VARCHAR2(128)
 LIFECYCLE_STATE                                                                              VARCHAR2(16)
 EGRESS_SECURITY_RULES$PROTOCOL                                                               VARCHAR2(4)
 EGRESS_SECURITY_RULES$DESCRIPTION                                                            VARCHAR2(32)
 EGRESS_SECURITY_RULES$DESTINATION                                                            VARCHAR2(64)
 EGRESS_SECURITY_RULES$TCP_OPTIONS$DESTINATION_PORT_RANGE$MAX                                 NUMBER
 EGRESS_SECURITY_RULES$TCP_OPTIONS$DESTINATION_PORT_RANGE$MIN                                 NUMBER
 EGRESS_SECURITY_RULES$IS_STATELESS                                                           VARCHAR2(8)
 EGRESS_SECURITY_RULES$DESTINATION_TYPE                                                       VARCHAR2(32)
 INGRESS_SECURITY_RULES$SOURCE                                                                VARCHAR2(16)
 INGRESS_SECURITY_RULES$PROTOCOL                                                              VARCHAR2(2)
 INGRESS_SECURITY_RULES$DESCRIPTION                                                           VARCHAR2(8)
 INGRESS_SECURITY_RULES$SOURCE_TYPE                                                           VARCHAR2(16)
 INGRESS_SECURITY_RULES$TCP_OPTIONS$DESTINATION_PORT_RANGE$MAX                                NUMBER
 INGRESS_SECURITY_RULES$TCP_OPTIONS$DESTINATION_PORT_RANGE$MIN                                NUMBER
 INGRESS_SECURITY_RULES$UDP_OPTIONS$DESTINATION_PORT_RANGE$MAX                                NUMBER
 INGRESS_SECURITY_RULES$UDP_OPTIONS$DESTINATION_PORT_RANGE$MIN                                NUMBER
 INGRESS_SECURITY_RULES$ICMP_OPTIONS$CODE                                                     VARCHAR2(4)
 INGRESS_SECURITY_RULES$ICMP_OPTIONS$TYPE                                                     NUMBER
 INGRESS_SECURITY_RULES$IS_STATELESS                                                          VARCHAR2(8)
 EGRESS_SECURITY_RULES$ICMP_OPTIONS$CODE                                                      VARCHAR2(1)
 EGRESS_SECURITY_RULES$ICMP_OPTIONS$TYPE                                                      NUMBER(1)
 EGRESS_SECURITY_RULES$TCP_OPTIONS$SOURCE_PORT_RANGE$MAX                                      NUMBER(1)
 EGRESS_SECURITY_RULES$TCP_OPTIONS$SOURCE_PORT_RANGE$MIN                                      NUMBER(1)
 EGRESS_SECURITY_RULES$UDP_OPTIONS$DESTINATION_PORT_RANGE$MAX                                 NUMBER(1)
 EGRESS_SECURITY_RULES$UDP_OPTIONS$DESTINATION_PORT_RANGE$MIN                                 NUMBER(1)
 EGRESS_SECURITY_RULES$UDP_OPTIONS$SOURCE_PORT_RANGE$MAX                                      NUMBER(1)
 EGRESS_SECURITY_RULES$UDP_OPTIONS$SOURCE_PORT_RANGE$MIN                                      NUMBER(1)
 INGRESS_SECURITY_RULES$TCP_OPTIONS$SOURCE_PORT_RANGE$MAX                                     NUMBER(1)
 INGRESS_SECURITY_RULES$TCP_OPTIONS$SOURCE_PORT_RANGE$MIN                                     NUMBER(1)
 INGRESS_SECURITY_RULES$UDP_OPTIONS$SOURCE_PORT_RANGE$MAX                                     NUMBER(1)
 INGRESS_SECURITY_RULES$UDP_OPTIONS$SOURCE_PORT_RANGE$MIN                                     NUMBER(1)


SELECT distinct
       tsub.DISPLAY_NAME SUBNET_NAME,
       ts.DISPLAY_NAME SECLIST_NAME,
       tvcn.DISPLAY_NAME VCN_NAME,
       tcomp.NAME COMPARTMENT_NAME,
       ts.ID SECLIST_ID,
       tsub.ID SUBNET_ID
FROM   OCI360_SECLISTS ts,
       OCI360_VCNS tvcn,
       OCI360_COMPARTMENTS tcomp,
       OCI360_SUBNETS tsub
WHERE  ts.COMPARTMENT_ID = tcomp.ID
AND    ts.VCN_ID = tvcn.ID
AND    tsub.security_list_ids LIKE '%' || ts.ID || '%'
AND    substr(ts.id,instr(ts.id,'.',1,3)+1,instr(ts.id,'.',1,4)-instr(ts.id,'.',1,3)-1) = 'iad'
ORDER BY COMPARTMENT_NAME, VCN_NAME, SUBNET_NAME, SECLIST_NAME;


13 rows selected.