- CREATE OR REPLACE TRIGGER sys.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_username VARCHAR2(32);
- BEGIN
- IF (ora_is_servererror(1017)) THEN
- -- get ip FOR remote connections :
- 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
- INTO v_pid, v_program
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');
- v_username := sys_context('userenv','authenticated_identity');
- dbms_application_info.read_module(v_module, v_action);
- message := to_char(SYSDATE, 'YYYY-MM-DD HH24:MI:SS') ||
- 'Password Erro: logon denied from' || nvl(ip, 'localhost') || ' ' ||
- v_pid || 'User:' || v_os_user || 'with' || v_program || '-' ||
- v_module || '' || v_action||' dbuser:' || v_username;
- sys.dbms_system.ksdwrt(2, message);
- END IF;END;/
来源: http://www.bubuko.com/infodetail-2647590.html