3b.1.106. Subnet Internal Reachability per VNIC (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)
WITH
/* Functions by Rodrigo Jorge - www.dbarj.com.br */
/* Convert IP to Decimal */
FUNCTION ip_to_dec (v_in VARCHAR2) RETURN NUMBER DETERMINISTIC IS
v_oct1 NUMBER(3);
v_oct2 NUMBER(3);
v_oct3 NUMBER(3);
v_oct4 NUMBER(3);
BEGIN
v_oct1 := SUBSTR(v_in,1,instr(v_in,'.',1,1)-1);
v_oct2 := SUBSTR(v_in,instr(v_in,'.',1,1)+1,instr(v_in,'.',1,2)-instr(v_in,'.',1,1)-1);
v_oct3 := SUBSTR(v_in,instr(v_in,'.',1,2)+1,instr(v_in,'.',1,3)-instr(v_in,'.',1,2)-1);
v_oct4 := SUBSTR(v_in,instr(v_in,'.',1,3)+1);
RETURN v_oct1*power(256,3)+v_oct2*power(256,2)+v_oct3*power(256,1)+v_oct4*power(256,0);
END;
/* Convert Decimal to IP */
FUNCTION dec_to_ip (v_in NUMBER) RETURN VARCHAR2 DETERMINISTIC IS
v_oct1 NUMBER(3);
v_oct2 NUMBER;
v_oct3 NUMBER;
v_oct4 NUMBER(3);
BEGIN
v_oct4 := trunc(mod(v_in,power(256,1))/power(256,0));
v_oct3 := trunc(mod(v_in,power(256,2))/power(256,1));
v_oct2 := trunc(mod(v_in,power(256,3))/power(256,2));
v_oct1 := trunc(mod(v_in,power(256,4))/power(256,3));
RETURN v_oct1 || '.' || v_oct2 || '.' || v_oct3 || '.' || v_oct4;
END;
/* Bit Or Function */
FUNCTION bitor(x NUMBER, y NUMBER) RETURN NUMBER DETERMINISTIC
IS
BEGIN
RETURN x + y - bitand(x, y);
END;
/* Get Min IP decimal for CIDR */
FUNCTION cidr_dec_min (v_in VARCHAR2) RETURN NUMBER DETERMINISTIC IS
v_ip_num NUMBER;
v_mask_num NUMBER;
v_mask NUMBER(2);
BEGIN
v_ip_num := ip_to_dec(SUBSTR(v_in,1,instr(v_in,'/',1,1)-1));
v_mask := SUBSTR(v_in,instr(v_in,'/',1,1)+1);
v_mask_num := POWER(2,32) - POWER(2,32 - v_mask);
RETURN BITAND(v_ip_num,v_mask_num);
END;
/* Get Max IP decimal for CIDR */
FUNCTION cidr_dec_max (v_in VARCHAR2) RETURN NUMBER DETERMINISTIC IS
v_ip_num NUMBER;
v_mask_num NUMBER;
v_mask NUMBER(2);
BEGIN
v_ip_num := ip_to_dec(SUBSTR(v_in,1,instr(v_in,'/',1,1)-1));
v_mask := SUBSTR(v_in,instr(v_in,'/',1,1)+1);
v_mask_num := POWER(2,32 - v_mask)-1;
RETURN BITOR(v_ip_num,v_mask_num);
END;
SELECT ts.COMPARTMENT_NAME,
ts.VCN_NAME,
tsubsrc.DISPLAY_NAME VNIC_SUBNET_NAME,
tvnic.display_name VNIC_NAME,
tvnic.private_ip,
tvnic.public_ip,
tvnic.is_primary,
tvnic.lifecycle_state vnic_lifecycle_state,
tvnic.availability_domain,
tvnic.id vnic_id,
tsubtrg.DISPLAY_NAME TARGET_SUBNET_NAME,
ts.DISPLAY_NAME SECLIST_NAME,
ts.LIFECYCLE_STATE,
ts.SOURCE,
ts.PROTOCOL,
ts.SOURCE_PORT_RANGE_MIN,
ts.SOURCE_PORT_RANGE_MAX,
ts.DESTINATION_PORT_RANGE_MIN,
ts.DESTINATION_PORT_RANGE_MAX,
ts.ICMP_CODE,
ts.ICMP_TYPE,
ts.IS_STATELESS,
ts.ID
FROM OCI360_VNICS tvnic,
OCI360_PRIVATEIPS tpips,
(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) ts,
(SELECT distinct id, vcn_id, display_name from OCI360_SUBNETS) tsubsrc,
(SELECT distinct security_list_ids, vcn_id, display_name from OCI360_SUBNETS) tsubtrg
WHERE tvnic.SUBNET_ID = tsubsrc.id
AND tvnic.id = tpips.vnic_id
AND tsubsrc.VCN_ID = tsubtrg.VCN_ID
AND tsubtrg.security_list_ids LIKE '%' || ts.ID || '%'
AND substr(tvnic.id,instr(tvnic.id,'.',1,3)+1,instr(tvnic.id,'.',1,4)-instr(tvnic.id,'.',1,3)-1) = 'iad'
AND ts.SOURCE_TYPE = 'CIDR_BLOCK'
AND ip_to_dec(tpips.ip_address) between cidr_dec_min(ts.SOURCE) and cidr_dec_max(ts.SOURCE)
ORDER BY COMPARTMENT_NAME, VCN_NAME, VNIC_SUBNET_NAME, VNIC_NAME, TARGET_SUBNET_NAME;
0 rows selected.
oci360 (c) 2020, All rights reserved. oci360 v20.06 (2020-06-25) based on moat369 v2002 (2020-02-27). Timestamp: 2020-06-30T15:55:53