이것은 의심스러운 로그인 대시보드 리포트의 SQL 버전입니다.
이 대시보드 리포트는 지정된 날짜 범위 내에서 이전 로그인과 의심스럽게 다른 새로운 로그인에 대한 세부 정보를 제공합니다. 비정상적인 로그인 시도를 식별함으로써 관리자는 사용자 계정과 포럼을 잠재적인 보안 위협으로부터 보호하기 위해 선제 조치를 취할 수 있습니다.
-- [params]
-- date :start_date = 2024-01-07
-- date :end_date = 2024-02-08
SELECT
u.id user_id,
u.username,
t.client_ip,
t.user_agent,
t.created_at login_time
FROM user_auth_token_logs t
JOIN users u ON u.id = t.user_id
WHERE t.action = 'suspicious'
AND t.created_at >= :start_date
AND t.created_at <= :end_date
ORDER BY t.created_at DESC
SQL 쿼리 설명
해당 SQL 쿼리는 지정된 날짜 범위 내에서 의심스럽게 표시된 로그인 시도 목록을 검색하여 작동합니다. 쿼리의 작동 방식에 대한 분석은 다음과 같습니다:
- 파라미터 정의: 쿼리는 리포트의 기간을 지정하기 위해
:start_date와:end_date라는 두 가지 파라미터를 정의하여 시작합니다. 두 날짜 파라미터 모두YYYY-MM-DD형식을 지원합니다. - 데이터 선택: 쿼리는 사용자 인증 활동에 대한 세부 정보를 기록하는
user_auth_token_logs테이블과 사용자 자체에 대한 정보를 포함하는users테이블에서 특정 필드를 선택합니다. 선택된 필드는 다음과 같습니다:u.id(user_id): 사용자의 고유 식별자.u.username: 사용자의 사용자 이름.t.client_ip: 로그인 시도가 이루어진 IP 주소.t.user_agent: 로그인 시도에 사용된 브라우저 또는 기기의 사용자 에이전트 문자열.t.created_at(login_time): 로그인 시도가 발생한 타임스탬프.
- 조인 연산: 쿼리는
user_auth_token_logs테이블(t)을users테이블(u)과 공통user_id필드를 기준으로 조인하여 각 로그인 시도를 해당 사용자 정보와 연결합니다. - 필터링:
user_auth_token_logs테이블의action필드에서suspicious로 표시된 로그인 시도만 포함하도록 레코드를 필터링합니다. 또한 지정된 날짜 범위(:start_date부터:end_date까지) 내에 발생한 레코드만 포함하도록 필터링합니다. - 정렬: 마지막으로, 쿼리는
created_at타임스탬프를 기준으로 내림차순으로 결과를 정렬하여 가장 최근의 의심스러운 로그인 시도가 먼저 나열되도록 합니다.
결과 예시
| user | username | client_ip | user_agent | login_time |
|---|---|---|---|---|
| user_id_example_1 | username_example | 2001:0db8:85a3:0000:0000:8a2e:0370:7334 | Mozilla/5.0 (iPhone; CPU iPhone OS 17_2_1 like Mac OS X) AppleWebKit/605.1.15 (KHTML, like Gecko) Version/17.2 Mobile/15E148 Safari/604.1 | 2024-01-09T08:18:57.535Z |
| … | … | … | … | … |