Домашнее задание - Практическое занятие №9: проект БД и форма входа, этап 2

Задание 1. Структура DDL для учетных записей и журнала безопасности

Спроектируйте SQL-скрипт создания двух таблиц: app_users и security_audit_log со всеми необходимыми индексами, ограничениями целостности и полями защиты от Brute-force атак (failed_attempts, locked_until, password_expiry).

Задание 2. Хранимая SQL-процедура верификации пароля и защиты от перебора

Разработайте хранимую процедуру/функцию verify_user_login(p_login, p_password, p_ip_address), которая строго реализует 5-этапный алгоритм:

1. Поиск пользователя по логину (при отсутствии — запись AUTH_FAILURE и возврат ошибки «Неверный логин или пароль»).
2. Проверка блокировки (locked_until > CURRENT_TIMESTAMP): если активно — отказ со статусом ACCOUNT_LOCKED.
3. Проверка активности (is_active = FALSE): отказ со статусом ACCOUNT_DEACTIVATED.
4. Вычисление хэша SHA256(p_password || salt) и сравнение с эталонным password_hash.
5. При несовпадении: failed_attempts = failed_attempts + 1; если >= 3 → установка locked_until = CURRENT_TIMESTAMP + INTERVAL '15 minute', логирование.
6. При успехе: сброс failed_attempts = 0, locked_until = NULL, запись AUTH_SUCCESS в лог, возврат роли.

Задание 3. Анализ записей журнала аудита безопасности

Напишите SQL-запрос выборки из таблицы security_audit_log, позволяющий специалисту службы безопасности выявить потенциальные атаки перебора (IP-адреса, с которых за последние 15 минут зафиксировано более 5 неудачных попыток входа с разными или одинаковыми логинами).

Задание 4. Контрольные вопросы для самопроверки

1. Почему в журнал безопасности (Security Audit Log) категорически запрещено записывать введенный пользователем пароль?
2. Какую уязвимость порождает ситуация, когда при неверном логине система сообщает «Пользователь не найден», а при верном логине и неверном пароле — «Пароль неверен»?
3. В какой именно момент процедуры аутентификации должен происходить сброс счетчика failed_attempts в ноль?