Oracle監査により、指定されたIP、アプリケーション、ユーザー接続のみを許可

使用するデータテーブル
データベース・ユーザーのログイン検証-ユーザー

CREATE TABLE SYS.DB_LOGO_RULE_USER (
  USER_ID NUMBER(18,0), 
  USER_NAME VARCHAR2(30 BYTE), 
  OPERATETIME DATE DEFAULT SYSDATE NOT NULL ENABLE, 
  CHECK_LEVEL NUMBER(1,0) DEFAULT 0 NOT NULL ENABLE
);
COMMENT ON COLUMN SYS.DB_LOGO_RULE_USER.USER_ID IS '  id';
COMMENT ON COLUMN SYS.DB_LOGO_RULE_USER.USER_NAME IS '    ';
COMMENT ON COLUMN SYS.DB_LOGO_RULE_USER.OPERATETIME IS '    ';
COMMENT ON COLUMN SYS.DB_LOGO_RULE_USER.CHECK_LEVEL IS '    ，0：   ；1：ip  ；2：    ';
COMMENT ON TABLE SYS.DB_LOGO_RULE_USER  IS '         -  ，      Kindey.S，  kindey123;
CREATE UNIQUE INDEX SYS.DB_LOGO_RULE_USER_ID ON SYS.DB_LOGO_RULE_USER (USER_ID);
CREATE UNIQUE INDEX SYS.DB_LOGO_RULE_USER_NAME ON SYS.DB_LOGO_RULE_USER (USER_NAME);

データベースユーザログインチェック-ipアドレス

CREATE TABLE SYS.DB_LOGO_RULE_IP (
  USER_NAME VARCHAR2(30 BYTE), 
  IP_ADDRESS VARCHAR2(30 BYTE), 
  OPERATETIME DATE DEFAULT SYSDATE NOT NULL ENABLE, 
  REMARK VARCHAR2(100 CHAR)
);
COMMENT ON COLUMN SYS.DB_LOGO_RULE_IP.USER_NAME IS '    ';
COMMENT ON COLUMN SYS.DB_LOGO_RULE_IP.IP_ADDRESS IS 'ip  ';
COMMENT ON COLUMN SYS.DB_LOGO_RULE_IP.OPERATETIME IS '    ';
COMMENT ON TABLE SYS.DB_LOGO_RULE_IP  IS '         -ip  ，      Kindey.S，  kindey123;

データベース・ユーザーのログイン検証-ターミナル

CREATE TABLE SYS.DB_LOGO_RULE_MDL (
  USER_NAME VARCHAR2(30 BYTE), 
  IP_ADDRESS VARCHAR2(30 BYTE), 
  MODULE VARCHAR2(30 BYTE), 
  OPERATETIME DATE DEFAULT SYSDATE NOT NULL ENABLE
);

COMMENT ON COLUMN SYS.DB_LOGO_RULE_MDL.IP_ADDRESS IS 'ip  ';
COMMENT ON COLUMN SYS.DB_LOGO_RULE_MDL.USER_NAME IS '     ';
COMMENT ON COLUMN SYS.DB_LOGO_RULE_MDL.MODULE IS '    ';
COMMENT ON COLUMN SYS.DB_LOGO_RULE_MDL.OPERATETIME IS '    ';
COMMENT ON TABLE SYS.DB_LOGO_RULE_USER  IS '         -  ，      Kindey.S，  kindey123;

schemaフリップフロップ

create or replace trigger CHECK_LOGO_HXWORK
  after logon on HXWORK.schema
/*
authro:Kindey.S
date:2018-07-23
version:1.0.0.0
remark:check ip for schema HXWORK, not allowed id cann't connnect this schema.
date:2019-01-02
version:1.0.1.1
remark:      sqlplus    /               
*/
declare
 CHECK_LEVEL number(1,0);
 COUNT_IP number(10,0);
 COUNT_MDL number(10,0);
begin
  select CHECK_LEVEL into CHECK_LEVEL from DB_LOGO_RULE_USER where CHECK_LEVEL != 0 and USER_NAME = user;
  if CHECK_LEVEL is null then CHECK_LEVEL := 0; end if;
  if CHECK_LEVEL = 1 then 
    select count(1) into COUNT_IP from DB_LOGO_RULE_IP
    where USER_NAME = user and IP_ADDRESS = nvl(SYS_CONTEXT('USERENV', 'IP_ADDRESS'),'127.0.0.1');
    if COUNT_IP = 0 then
      RAISE_APPLICATION_ERROR(-20000
        , 'User '||user ||'checked by IP address,'
        ||'Can not log in from this IP address (' || SYS_CONTEXT('USERENV', 'IP_ADDRESS') || ')'
      );
    end if;
  elsif CHECK_LEVEL = 2 then
    select count(1) into COUNT_MDL
    from DB_LOGO_RULE_MDL
    where USER_NAME = user
    and IP_ADDRESS = SYS_CONTEXT('USERENV', 'IP_ADDRESS')
    and (MODULE = SYS_CONTEXT('USERENV', 'MODULE') or MODULE is null);
    if COUNT_MDL = 0 then
      RAISE_APPLICATION_ERROR(-20000
        , 'User '||user ||'checked by module,'
        ||'Can not log in from this IP address (' || SYS_CONTEXT('USERENV', 'IP_ADDRESS')
        ||' and this module (' || SYS_CONTEXT('USERENV', 'MODULE') || ')'
      );
    end if;
  end if;
end;
/