Лог на корисници при 3 неуспешни обиди

Опис

Овој тригер ќе се тригерува откако ќе се случат 3 последователни failed login attempts. За да го реализираме ова, т.е. да водиме евиденција дека се случиле 3 последователни обиди, на бројот за failed login attempts ќе му направиме модул со 3.

Тригер

SQL код

CREATE TRIGGER trg_failed_login_threshold
AFTER INSERT ON Login_Attempt
FOR EACH ROW
EXECUTE FUNCTION trg_failed_login_threshold();

Функција

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 код

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';