審計資料庫登陸失敗觸發器

1. 輸入日誌
oerr ora 1017
01017, 00000, "invalid username/password; logon denied"

alert.log
2011-07-01 10:14:04 logon denied from 172.16.15.57 ccclyi with sqlplus.exe
2011-07-01 10:14:53 logon denied from 172.16.15.57 ccclyi with plsqldev.exe
2011-07-01 10:25:27 logon denied from 884 oracle with (TNS V1-V3)

no transcation, so no exception check~

2. 建立觸發器

CREATE OR REPLACE TRIGGER LOGON_DENIED_TO_ALERT
AFTER SERVERERROR ON DATABASE
DECLARE
  message   varchar2(120);
  ip        varchar2(15);
  v_os_user varchar2(80);
  v_module  varchar2(50);
  v_action  varchar2(50);
  v_pid     varchar2(10);
  v_sid     number;
BEGIN
  IF (ora_is_servererror(1017)) THEN
    IF sys_context('userenv', 'network_protocol') = 'tcp' THEN
      ip := sys_context('userenv', 'ip_address');
    ELSE
      select distinct sid into v_sid from v_$mystat;
      select p.spid into v_pid from v$process p, v$session v where p.addr = v.paddr and v.sid = v_sid;
    END IF;

    v_os_user := sys_context('userenv', 'os_user');
   
    --v_module is much useful, "plsqldev.exe"
    dbms_application_info.read_module(v_module, v_action);

    message := to_char(sysdate, 'yyyy-mm-dd hh24:mi:ss')||' logon denied from '||nvl(ip, v_pid)
            ||' '||v_os_user||' with '||v_module||' '||v_action;
  
  --write alert.log
    sys.dbms_system.ksdwrt(2, message);

  END IF;
END;
/