Hi Tom,

I'm creating a trigger for our environment and this is what is should do:

- Catch failed user login attempt and send an email to Database team to investigate if
failed_login attempts_count are >=10 and the attempts are made within <=10 minutes.

I have made this trigger and facing following difficulties.
a. Initially it worked if the failed_logon_attempts_count>=10 but it wasn't taking into consideration the 10 minutes period. This means even if 10 attempts are made spanning more than 10 minutes instead of resetting it would still send an email.
b. Today I may have done something that caused it to stop working for 10 attempts as well, this means no emails are sent even if the 10 attempts are made within 10 minutes.

I found that select into variable is not working. Can you please suggest if my logic is fine and if it is then why is it that it initially worked and then broke. If not can you please provide some guidance.

Here's my trigger code:

CREATE OR REPLACE TRIGGER "SYS"."FAILED_LOGON_NOTIFICATIONS"
AFTER SERVERERROR ON DATABASE

DECLARE

failed_username varchar2(30);
failed_login_attempts_count number;


CURSOR cur_dat (in_failed_username varchar2)
IS
SELECT to_char(dat.timestamp,'YYYY-MM-DD HH24:MI:SS') timestamp,
dat.username,
dat.RETURNCODE,
dat.SESSIONID,
dat.COMMENT_TEXT,
dat.USERHOST,
dat.TERMINAL,
dat.SES_ACTIONS
FROM( SELECT TIMESTAMP, username, RETURNCODE, SESSIONID, COMMENT_TEXT, USERHOST, TERMINAL, SES_ACTIONS
FROM sys.dba_audit_trail
) dat
WHERE TIMESTAMP BETWEEN SYSDATE -10/24/60 AND SYSDATE
AND RETURNCODE IN (1017,1004,1005,28000)
AND username = failed_username
ORDER BY TIMESTAMP DESC;

BEGIN

IF (IS_SERVERERROR(1017) or IS_SERVERERROR(1004) or IS_SERVERERROR(1005) or IS_SERVERERROR(28000))
THEN

SELECT USERNAME
INTO FAILED_USERNAME
FROM SYS.DBA_AUDIT_TRAIL
WHERE SESSIONID = SYS_CONTEXT('USERENV','SESSIONID');


SELECT COUNT(*) INTO failed_login_attempts_count
FROM (SELECT LCOUNT, TIMESTAMP
FROM USER$
INNER JOIN DBA_AUDIT_TRAIL
ON DBA_AUDIT_TRAIL.USERNAME = USER$.NAME
WHERE LCOUNT >=10
AND TIMESTAMP BETWEEN SYSDATE -10/24/60
AND SYSDATE AND RETURNCODE IN (1017,1004,1005,28000)
AND NAME = failed_username);


IF (failed_login_attempts_count >=10) -- When I change this to less <=10 it works, but that's not what I want. And in the email received the failed_login_attempts_count variable suggest that there are 0 attempts. This means the variable is not working.

THEN

SEND EMAIL TO DBA TEAM

END IF;
END IF;
END failed_logon_notifications;

SQL> CREATE OR REPLACE TRIGGER "SYS"."FAILED_LOGON_NOTIFICATIONS"
  2  AFTER SERVERERROR ON DATABASE
  3  DECLARE
  4    failed_username varchar2(30);
  5    procedure logger(m varchar2) is
  6      pragma autonomous_transaction;
  7    begin
  8      insert into sys.log_tab values (m); commit;
  9    end;
 10
 11  BEGIN
 12    IF (IS_SERVERERROR(1017) or IS_SERVERERROR(1004) or IS_SERVERERROR(1005) or IS_SERVERERROR(28000))
 13    THEN
 14      logger('in trigger');
 15
 16      SELECT USERNAME
 17      INTO FAILED_USERNAME
 18      FROM SYS.DBA_AUDIT_TRAIL
 19      WHERE SESSIONID = SYS_CONTEXT('USERENV','SESSIONID');
 20      logger('got FAILED_USERNAME='||FAILED_USERNAME);
 21
 22      for i in (
 23        select * from (
 24          SELECT "TIMESTAMP" ts
 25          FROM DBA_AUDIT_TRAIL
 26          WHERE RETURNCODE IN (1017,1004,1005,28000)
 27          AND   USERNAME = failed_username
 28          order by TIMESTAMP desc
 29        )
 30        where rownum < 10
 31      )
 32      loop
 33        logger('timestamp='||to_char(i.ts,'ddmmyyyy hh24miss'));
 34      end loop;
 35
 36    END IF;
 37  END;
 38  /

Trigger created.

SQL> conn demo/qww
ERROR:
ORA-01017: invalid username/password; logon denied
<code>

and now check the log

<code>
SQL> select to_char(sysdate,'ddmmyyyy hh24miss') from dual;

TO_CHAR(SYSDATE
---------------
06032016 134247

SQL> select * from sys.log_tab;

MSG
-------------------------------------
in trigger
got FAILED_USERNAME=DEMO
timestamp=06032016 054106

SQL> select * from (
  2    SELECT to_char("TIMESTAMP",'ddmmyyyy hh24miss') ts
  3    FROM DBA_AUDIT_TRAIL
  4    WHERE RETURNCODE IN (1017,1004,1005,28000)
  5    AND   USERNAME = 'DEMO'
  6    order by TIMESTAMP desc
  7  )
  8  where rownum < 10;

TS
---------------
06032016 134106