Orace触发器实现成功、失败登录会话ip地址

以下请在sys用户下执行

成功登录的ip会话

create or replace trigger trg_login_on after logon on database
begin
  dbms_application_info.set_client_info( sys_context('userenv','ip_address'));
end;
/

查询ip登录成功的会话

select username,program,machine,client_info,sys_context('userenv','ip_address') as ipadd from gv$session s where username is not null order by username,program,machine;

SQL> select username,program,machine,client_info,sys_context('userenv','ip_address') as ipadd from gv$session s where username is not null order by username,program,machine;

username    program             machine    client_info      ipadd
---------- -------------------- ---------- ---------------  -------------
SYS        oraagent.bin@rac1 (TNS V1-V3)             rac1
SYS        oraagent.bin@rac2 (TNS V1-V3)             rac2
SYS        oraagent.bin@rac2 (TNS V1-V3)             rac2
SYS        oracle@rac1 (PZ99)                        rac1
SYS        oracle@rac2 (PZ99)                        rac1
SYS        sqlplus@rac1 (TNS V1-V3)                 rac1
SYSTEM     sqlplus@rac2 (TNS V1-V3)                rac2       172.16.4.122

7 rows selected.

失败登录的ip记录到告警日志

CREATE OR REPLACE TRIGGER logon_denied_to_alert AFTER servererror ON DATABASE
DECLARE
message   VARCHAR2(168);
ip        VARCHAR2(15);
v_os_user VARCHAR2(80);
v_module  VARCHAR2(50);
v_action  VARCHAR2(50);
v_pid     VARCHAR2(10);
v_sid     NUMBER;
v_program VARCHAR2(48);
v_client_pid VARCHAR2(50);
v_machine VARCHAR2(500);
BEGIN
IF (ora_is_servererror(1017)) THEN
IF upper(sys_context('userenv', 'network_protocol'))='TCP' THEN
ip:= sys_context('userenv', 'ip_address');
END IF;
SELECT sid INTO v_sid FROM sys.v_$mystat WHERE rownum < 2;
SELECT p.spid, v.program,v.process,v.machine INTO v_pid, v_program, v_client_pid,v_machine FROM v$process p, v$session v WHERE p.addr = v.paddr AND v.sid = v_sid;
v_os_user := sys_context('userenv', 'os_user');
dbms_application_info.read_module(v_module, v_action);
 message := to_char(SYSDATE, 'YYYY-MM-DD HH24:MI:SS') ||
               ' logon denied from ' || nvl(ip, 'localhost') || ' client program pid is: ' ||
                v_client_pid || ' hostname ' || v_machine || ' with ' || v_program || ' - ' ||
               v_module || ' ' || v_action;

    sys.dbms_system.ksdwrt(2, message);
END IF;
END;
/

rac环境查看每个节点的日志
告警日志：
2019-05-14 10:08:14 logon denied from 172.16.4.122 client program pid is: 33647 hostname rac2 with sqlplus@rac2 (TNS V1-V3) - sqlplus@rac2 (TNS V1-V3)
记录了失败登录的客户端程序pid，以及主机名、程序名

脚本
https://pan.baidu.com/s/1jeJNLotZDPR7yTgvE380Cw