【Oracle】数据库ip黑名单

---

一、创建ip黑名单表

CREATE TABLE tzq.ON_NOLOGON_TABLE
   ( "COMPANY" VARCHAR2(20),
        "IP_ADDRESS" VARCHAR2(30),
        "APP_NAME" VARCHAR2(300)
   ) PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255
 NOCOMPRESS LOGGING
  STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1
  BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT);

insert into tzq.ON_NOLOGON_TABLE values ('黑客帝国','127.0.0.1','tzq');

SELECT * FROM ON_NOLOGON_TABLE;

二、创建触发器，登录数据库的时候触发

CREATE OR REPLACE TRIGGER tzq.check_black_list
  AFTER logon ON DATABASE ----事后触发器
DECLARE
  client_ip VARCHAR2(20);
  v_count   VARCHAR2(100);
BEGIN
  dbms_application_info.set_client_info('logon ' ||
                                        sys_context('userenv'
                                                   ,'ip_address')); ----获取客户端信息
  SELECT sys_context('userenv'
                    ,'ip_address')
    INTO client_ip
    FROM dual;
  ----判断ip是否在黑名单上
  SELECT COUNT(1)
    INTO v_count
    FROM on_nologon_table
   WHERE ip_address = client_ip;
  IF v_count > 0 THEN
    raise_application_error(-20000
                           ,'该ip已被列入黑名单');
  END IF;
END;
/

ALTER TRIGGER tzq.CHECK_BLACK_LIST ENABLE; ---启动触发器