3b.1.107. Obsolete Security Ingress Rules (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.DISPLAY_NAME,
ts.VCN_NAME,
ts.COMPARTMENT_NAME,
ts.TIME_CREATED,
ts.LIFECYCLE_STATE,
ts.SOURCE,
ts.SOURCE_TYPE,
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,
ts.SOURCE_IP,
ts.CIDR_BLOCK,
ip_to_dec(ts.SOURCE_IP),
cidr_dec_min(ts.CIDR_BLOCK),
cidr_dec_max(ts.CIDR_BLOCK)
FROM (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,
tvcn.CIDR_BLOCK,
tvcn.ID VCN_ID,
SUBSTR(ts."INGRESS_SECURITY_RULES$SOURCE",1,instr(ts."INGRESS_SECURITY_RULES$SOURCE",'/',1,1)-1) SOURCE_IP
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" = 'CIDR_BLOCK'
AND ts."INGRESS_SECURITY_RULES$PROTOCOL"!='17'
AND SUBSTR(ts."INGRESS_SECURITY_RULES$SOURCE",instr(ts."INGRESS_SECURITY_RULES$SOURCE",'/',1,1)+1)=32
AND cidr_dec_min(ts."INGRESS_SECURITY_RULES$SOURCE") between cidr_dec_min(tvcn.CIDR_BLOCK) and cidr_dec_max(tvcn.CIDR_BLOCK)
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,
tvcn.CIDR_BLOCK,
tvcn.ID VCN_ID,
SUBSTR(ts."INGRESS_SECURITY_RULES$SOURCE",1,instr(ts."INGRESS_SECURITY_RULES$SOURCE",'/',1,1)-1) SOURCE_IP
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" = 'CIDR_BLOCK'
AND ts."INGRESS_SECURITY_RULES$PROTOCOL"='17'
AND SUBSTR(ts."INGRESS_SECURITY_RULES$SOURCE",instr(ts."INGRESS_SECURITY_RULES$SOURCE",'/',1,1)+1)=32
AND cidr_dec_min(ts."INGRESS_SECURITY_RULES$SOURCE") between cidr_dec_min(tvcn.CIDR_BLOCK) and cidr_dec_max(tvcn.CIDR_BLOCK)
AND substr(ts.id,instr(ts.id,'.',1,3)+1,instr(ts.id,'.',1,4)-instr(ts.id,'.',1,3)-1) = 'iad') ts
WHERE SOURCE_IP NOT IN
( SELECT tpips.ip_address
from OCI360_SUBNETS tsub,
OCI360_PRIVATEIPS tpips
where tpips.SUBNET_ID = tsub.id
AND tsub.VCN_ID = ts.VCN_ID
)
ORDER BY COMPARTMENT_NAME,VCN_NAME,DISPLAY_NAME,LIFECYCLE_STATE,PROTOCOL;
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:55