登录失败触发器

eagle198699

如果有用户登录失败，导致用户被锁，可以通过触发器，记录登录IP到alert log中，但是目前发现这个触发器还不能记录用户登录了哪个用户，但是如果账号被锁，可以结合时间和alert log的记录检查。

 

CREATE OR REPLACE TRIGGER log_errors AFTER SERVERERROR ON DATABASE
Declare
username Varchar2(30);
ipaddress varchar2(16);
terminal varchar2(50) ;
os_user Varchar2(30);
BEGIN
select sys_context('userenv','session_user') Into username from dual;
select sys_context('USERENV','IP_ADDRESS') into ipaddress from dual;
select SYS_CONTEXT('USERENV','TERMINAL') into terminal from dual;
select sys_context('userenv','os_user') into os_user from dual;
   IF (IS_SERVERERROR (1017)) THEN
     BEGIN
      sys.dbms_system.ksdwrt(2,to_char(sysdate)||':'||ipaddress||':'||username||':'||terminal||':'||os_user||':'||'attempted to logon,but password is error!');
     END;
   ELSIF (IS_SERVERERROR (28000))  THEN
     BEGIN
      sys.dbms_system.ksdwrt(2,to_char(sysdate)||':'||ipaddress||':'||username||':'||terminal||':'||os_user||':'||'logon failed,account is locked!');
     END;  
   END IF;
END;
/