EM Event: Number of failed login attempts exceeds threshold value [Part 2]

Hello,
As a second part of last week’s post, here is a way to accomplish that without using audit. Using a simple logon trigger! 🙂
Take a look:

First step: Create a table to store the data.

CREATE TABLE SYS.TRG_LOGON (SCHEMA VARCHAR2(50), SERVER VARCHAR2(200), FAILTIME DATE, ERROR VARCHAR2(200));

Second step: Create the logon trigger.

CREATE OR REPLACE TRIGGER SYS.TRG_LOGON
AFTER SERVERERROR ON DATABASE
BEGIN
-- IF (IS_SERVERERROR(1017)) THEN
INSERT INTO TRG_LOGON VALUES(SYS_CONTEXT('USERENV', 'AUTHENTICATED_IDENTITY'), SYS_CONTEXT('USERENV', 'HOST'), SYSDATE, dbms_standard.server_error(1));
COMMIT;
-- END IF;
END;
/

Note that I’m detecting all errors in database!
But if you are looking for any specific error like wrong password showed in last post, you can uncomment the commented lines ans specify the error.

Ok, but now, lets check the audited login fail:

SQL> SELECT * FROM sys.aud_logon ORDER BY WHEN DESC;
 
SCHEMA                         SERVER                         WHEN                  ERROR
------------------------------ ------------------------------ --------------------  -----------------
TESTUSER                       APPMACHINE                      10-04-2016 18:15:56  1017

That’s it. Hope it helps you!
Enjoy!