Hi Everyone,
I'm a beginner in pl sql development and looking for some help for following logic.
I'm trying to capture failed login attempt to Oracle database using trigger. Number of failed_login attempts allowed =10, failed_login_ionterval = 10 minutes, and user should be the same user(While keeping track of other users as well). After 10 minutes the variables should reset to 0. The information is coming from dba_audit_trail table.
CREATE OR REPLACE TRIGGER SYS.LOGON_FAILURE_NOTIFICATION
AFTER SERVERERROR ON DATABASE
DECLARE
failed_login_attempts_count number := 10;
failed_logon_interval number :=10; ---This should be in minutes
failed_username varchar(20) ---This is to compare if it is the same user that kept failing.
BEGIN
IF (IS_SERVERERROR(1017) or IS_SERVERERROR(1004) or IS_SERVERERROR(1005) or IS_SERVERERROR(28000))
and (failed_login_attempts_count >=10)
and (failed_logon_interval <=10)
and (Username in failed username)
THEN
Send an alert email
ELSE
failed_login_attempts_count = 0;
failed_logon_interval = 0;
END IF
END
I'm good with email part but would need help with how to keep track of more than one user
A little guidance will have me walking on this. Please contribute.
Thanks