Changes between Version 1 and Version 2 of failedAttemptsLog

Timestamp:

08/18/26 14:27:48 (2 days ago)

Author:

192063

Comment:

--

failedAttemptsLog

@@ -2 +2 @@
 
 ==== Опис ====
+Овој тригер ќе се тригерува откако ќе се случат 3 последователни failed login attempts.
+За да го реализираме ова, т.е. да водиме евиденција дека се случиле 3 последователни обиди, на бројот за failed login attempts ќе му направиме модул со 3.
+
+
 
 == Тригер ==
 ==== SQL код ====
+{{{
+#!sql
+CREATE TRIGGER trg_failed_login_threshold
+AFTER INSERT ON Login_Attempt
+FOR EACH ROW
+EXECUTE FUNCTION trg_failed_login_threshold();
+}}}
+
+== Функција ==
+==== SQL код ====
+{{{
+#!sql
+CREATE OR REPLACE FUNCTION trg_failed_login_threshold()
+RETURNS TRIGGER
+LANGUAGE plpgsql
+AS $$
+DECLARE
+    failed_count INTEGER; --declarirame promenliva so tip int za broenje na 3 neuspesni obidi
+BEGIN
+    IF NEW.successful = FALSE THEN --dokolku loginot e neuspesen
+        SELECT COUNT(*) 
+        INTO failed_count --zapisi vo failed_count promenlivata
+        FROM Login_Attempt la --od Login_Attempt tabelata
+        WHERE la.user_id = NEW.user_id  -- kade sto e ist user id
+          AND la.successful = FALSE --neuspesen obid
+          AND la.attempt_date >= COALESCE(
+              (
+                  SELECT MAX(successful_login.attempt_date)
+                  FROM Login_Attempt successful_login
+                  WHERE successful_login.user_id = NEW.user_id
+                    AND successful_login.successful = TRUE
+                    AND successful_login.attempt_date < NEW.attempt_date
+              ),
+              '-infinity'::timestamptz
+          );
+
+        IF failed_count > 0 AND MOD(failed_count, 3) = 0 THEN --failed count mora da bide > 0 i failed count modul 3 mora da bide 0
+
+            INSERT INTO System_Event (
+                user_id,
+                user_email,
+                event_type,
+                description
+            )
+            VALUES (
+                NEW.user_id,
+                NEW.email,
+                'THREE_UNSUCCESSFUL_LOGINS',
+                'The user failed to login 3 times in a row'
+            );
+        END IF;
+    END IF;
+    RETURN NEW;
+END;
+$$;
+}}}
 
 
 === View ===
 ==== SQL код ====
+{{{
+#!sql
+CREATE OR REPLACE VIEW Failed_Login_View AS
+SELECT
+    user_id,
+    user_email AS email,
+    event_type,
+    description,
+    event_date
+FROM System_Event
+WHERE event_type = 'THREE_UNSUCCESSFUL_LOGINS';
+}}}