감사 로그

-- 최근 24시간 내 로그인 실패 조회
SELECT
  event_time,
  user_identity.email AS user_email,
  source_ip_address,
  response.error_message,
  response.status_code
FROM system.access.audit
WHERE action_name IN ('login', 'tokenLogin', 'samlLogin')
  AND response.status_code != 200
  AND event_date >= CURRENT_DATE() - INTERVAL 1 DAY
ORDER BY event_time DESC;

-- 특정 테이블에 대한 접근 이력
SELECT
  event_time,
  user_identity.email AS user_email,
  action_name,
  request_params.full_name_arg AS object_name,
  source_ip_address
FROM system.access.audit
WHERE service_name = 'unityCatalog'
  AND request_params.full_name_arg LIKE 'production.ecommerce.customers%'
  AND event_date >= CURRENT_DATE() - INTERVAL 7 DAYS
ORDER BY event_time DESC;

-- GRANT/REVOKE 이벤트 추적
SELECT
  event_time,
  user_identity.email AS changed_by,
  action_name,
  request_params.securable_type AS object_type,
  request_params.securable_full_name AS object_name,
  request_params.principal AS granted_to,
  request_params.changes AS permission_changes
FROM system.access.audit
WHERE service_name = 'unityCatalog'
  AND action_name IN ('updatePermissions', 'grantPermission', 'revokePermission')
  AND event_date >= CURRENT_DATE() - INTERVAL 30 DAYS
ORDER BY event_time DESC;

-- 비정상적으로 많은 테이블을 조회한 사용자
SELECT
  user_identity.email AS user_email,
  COUNT(DISTINCT request_params.full_name_arg) AS unique_tables_accessed,
  COUNT(*) AS total_queries
FROM system.access.audit
WHERE service_name = 'unityCatalog'
  AND action_name = 'getTable'
  AND event_date = CURRENT_DATE()
GROUP BY user_identity.email
HAVING unique_tables_accessed > 50
ORDER BY unique_tables_accessed DESC;

-- 클러스터 생성/삭제 이벤트
SELECT
  event_time,
  user_identity.email AS user_email,
  action_name,
  request_params.cluster_name,
  request_params.num_workers,
  request_params.node_type_id
FROM system.access.audit
WHERE service_name = 'clusters'
  AND action_name IN ('create', 'permanentDelete', 'edit')
  AND event_date >= CURRENT_DATE() - INTERVAL 7 DAYS
ORDER BY event_time DESC;

-- Secret 접근 이벤트 (누가 어떤 Secret을 읽었는지)
SELECT
  event_time,
  user_identity.email AS user_email,
  action_name,
  request_params.scope AS secret_scope,
  request_params.key AS secret_key,
  source_ip_address
FROM system.access.audit
WHERE service_name = 'secrets'
  AND event_date >= CURRENT_DATE() - INTERVAL 7 DAYS
ORDER BY event_time DESC;

-- 업무 시간 외 활동 탐지 (한국 기준 22시~06시, 주말)
SELECT
  event_time,
  user_identity.email AS user_email,
  service_name,
  action_name,
  source_ip_address
FROM system.access.audit
WHERE event_date >= CURRENT_DATE() - INTERVAL 7 DAYS
  AND (
    HOUR(event_time) < 6 OR HOUR(event_time) >= 22  -- 야간
    OR DAYOFWEEK(event_time) IN (1, 7)               -- 주말
  )
  AND user_identity.email NOT LIKE '%service-principal%'  -- SP 제외
ORDER BY event_time DESC
LIMIT 100;

SELECT
  event_date,
  service_name,
  COUNT(*) AS event_count
FROM system.access.audit
WHERE event_date >= CURRENT_DATE() - INTERVAL 30 DAYS
GROUP BY event_date, service_name
ORDER BY event_date;

SELECT
  service_name,
  COUNT(*) AS event_count,
  COUNT(DISTINCT user_identity.email) AS unique_users
FROM system.access.audit
WHERE event_date >= CURRENT_DATE() - INTERVAL 7 DAYS
GROUP BY service_name
ORDER BY event_count DESC;

-- 최근 1시간 내 로그인 실패가 10건 이상이면 알림
SELECT COUNT(*) AS failed_logins
FROM system.access.audit
WHERE action_name IN ('login', 'samlLogin')
  AND response.status_code != 200
  AND event_time > CURRENT_TIMESTAMP() - INTERVAL 1 HOUR;
-- 알림 조건: failed_logins > 10

-- Account Admin 또는 Workspace Admin 권한 변경 감지
SELECT
  event_time,
  user_identity.email AS changed_by,
  request_params
FROM system.access.audit
WHERE action_name IN ('setAdmin', 'removeAdmin', 'updatePermissions')
  AND event_time > CURRENT_TIMESTAMP() - INTERVAL 1 HOUR
  AND response.status_code = 200;
-- 알림 조건: 결과가 1건 이상

-- 단기간 내 다수의 테이블 SELECT 감지
SELECT
  user_identity.email,
  COUNT(DISTINCT request_params.full_name_arg) AS tables_accessed
FROM system.access.audit
WHERE service_name = 'unityCatalog'
  AND action_name = 'getTable'
  AND event_time > CURRENT_TIMESTAMP() - INTERVAL 1 HOUR
GROUP BY user_identity.email
HAVING tables_accessed > 20;
-- 알림 조건: 결과가 1건 이상

-- 장기 보존을 위한 감사 로그 복제
CREATE TABLE IF NOT EXISTS compliance.audit.long_term_audit AS
SELECT * FROM system.access.audit WHERE 1=0;

-- 매일 증분 복제 (Job으로 스케줄)
INSERT INTO compliance.audit.long_term_audit
SELECT *
FROM system.access.audit
WHERE event_date = CURRENT_DATE() - INTERVAL 1 DAY;

-- ✅ 좋은 예: 파티션 필터 + 서비스 필터
SELECT * FROM system.access.audit
WHERE event_date >= '2025-03-01' AND event_date <= '2025-03-31'
  AND service_name = 'unityCatalog';

-- ❌ 나쁜 예: 파티션 필터 없이 전체 스캔
SELECT * FROM system.access.audit
WHERE action_name = 'login';