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!

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s