3b.1.103. Security Lists - Ingress (OCI360_SECLISTS)

#: 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 ts.DISPLAY_NAME,
       tvcn.DISPLAY_NAME VCN_NAME,
       tcomp.NAME COMPARTMENT_NAME,
       ts.TIME_CREATED,
       ts.LIFECYCLE_STATE,
       ts."INGRESS_SECURITY_RULES$SOURCE"                                  SOURCE,
       ts."INGRESS_SECURITY_RULES$SOURCE_TYPE"                             SOURCE_TYPE,
       ts."INGRESS_SECURITY_RULES$PROTOCOL"                                PROTOCOL,
       ts."INGRESS_SECURITY_RULES$TCP_OPTIONS$SOURCE_PORT_RANGE$MIN"       SOURCE_PORT_RANGE_MIN,
       ts."INGRESS_SECURITY_RULES$TCP_OPTIONS$SOURCE_PORT_RANGE$MAX"       SOURCE_PORT_RANGE_MAX,
       ts."INGRESS_SECURITY_RULES$TCP_OPTIONS$DESTINATION_PORT_RANGE$MIN"  DESTINATION_PORT_RANGE_MIN,
       ts."INGRESS_SECURITY_RULES$TCP_OPTIONS$DESTINATION_PORT_RANGE$MAX"  DESTINATION_PORT_RANGE_MAX,
       ts."INGRESS_SECURITY_RULES$ICMP_OPTIONS$CODE"                       ICMP_CODE,
       ts."INGRESS_SECURITY_RULES$ICMP_OPTIONS$TYPE"                       ICMP_TYPE,
       ts."INGRESS_SECURITY_RULES$IS_STATELESS"                            IS_STATELESS,
       ts.ID
FROM   OCI360_SECLISTS ts,
       OCI360_VCNS tvcn,
       OCI360_COMPARTMENTS tcomp
WHERE  ts.COMPARTMENT_ID = tcomp.ID
AND    ts.VCN_ID = tvcn.ID
AND    ts."INGRESS_SECURITY_RULES$SOURCE_TYPE" is not null
AND    ts."INGRESS_SECURITY_RULES$PROTOCOL"!='17'
AND    substr(ts.id,instr(ts.id,'.',1,3)+1,instr(ts.id,'.',1,4)-instr(ts.id,'.',1,3)-1) = 'iad'
UNION ALL
SELECT ts.DISPLAY_NAME,
       tvcn.DISPLAY_NAME VCN_NAME,
       tcomp.NAME COMPARTMENT_NAME,
       ts.TIME_CREATED,
       ts.LIFECYCLE_STATE,
       ts."INGRESS_SECURITY_RULES$SOURCE"                                  SOURCE,
       ts."INGRESS_SECURITY_RULES$SOURCE_TYPE"                             SOURCE_TYPE,
       ts."INGRESS_SECURITY_RULES$PROTOCOL"                                PROTOCOL,
       ts."INGRESS_SECURITY_RULES$UDP_OPTIONS$SOURCE_PORT_RANGE$MIN"       SOURCE_PORT_RANGE_MIN,
       ts."INGRESS_SECURITY_RULES$UDP_OPTIONS$SOURCE_PORT_RANGE$MAX"       SOURCE_PORT_RANGE_MAX,
       ts."INGRESS_SECURITY_RULES$UDP_OPTIONS$DESTINATION_PORT_RANGE$MIN"  DESTINATION_PORT_RANGE_MIN,
       ts."INGRESS_SECURITY_RULES$UDP_OPTIONS$DESTINATION_PORT_RANGE$MAX"  DESTINATION_PORT_RANGE_MAX,
       ts."INGRESS_SECURITY_RULES$ICMP_OPTIONS$CODE"                       ICMP_CODE,
       ts."INGRESS_SECURITY_RULES$ICMP_OPTIONS$TYPE"                       ICMP_TYPE,
       ts."INGRESS_SECURITY_RULES$IS_STATELESS"                            IS_STATELESS,
       ts.ID
FROM   OCI360_SECLISTS ts,
       OCI360_VCNS tvcn,
       OCI360_COMPARTMENTS tcomp
WHERE  ts.COMPARTMENT_ID = tcomp.ID
AND    ts.VCN_ID = tvcn.ID
AND    ts."INGRESS_SECURITY_RULES$SOURCE_TYPE" is not null
AND    ts."INGRESS_SECURITY_RULES$PROTOCOL"='17'
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,DISPLAY_NAME,LIFECYCLE_STATE,PROTOCOL;


33 rows selected.