# SQL 威胁狩猎实验室：身份验证异常调查
**状态：开发中** | 检测即代码 · 可度量 · CI 门控
在合成企业数据集上进行确定性、版本控制的身份验证威胁狩猎：七项内置真实基准的 SQL 检测、经过度量的 precision/recall、ATT&CK 标记、静态威胁情报富化，以及当检测逻辑回归时会导致失败的 pytest + GitHub Actions pipeline。
姊妹仓库：[dlpz-SEC/detection-as-code](https://github.com/dlpz-SEC/detection-as-code)（本仓库镜像其约定的 Sigma → CI pipeline），以及 ADTE（Autonomous Detection Triage Engine），本实验室严重性模型的 0–100 评分即是其手动前身（参见[严重性模型](#severity-model-and-the-path-to-adte)）。
## 结果
- 跨越 22 个账户和 18 台设备的 **161 个登录事件**
- **17 个分诊发现** (8 个高危 / 9 个中危 / 0 个低危)
- **6 个数据完整性问题** (孤儿设备 + 资产清单不匹配 + 无设备用户)
- **3 个严重过时的端点** (补丁发布超过 180 天)
- 所有规则的**可度量检测精确度为 0.94** (参见 [docs/METRICS.md](docs/METRICS.md))
上述所有数字均由 `scripts/compute_metrics.py` 从实时数据库中生成 —— 没有任何手工输入。完整的单规则指标：[docs/METRICS.md](docs/METRICS.md)。使用显示的查询重现下表中的任何内容，例如 `sqlite3 -header -column data/security.db 'SELECT * FROM v_triage_queue;'`。
```
pip install -r requirements.txt
bash run_detection.sh # build + run + print triage queue
python scripts/compute_metrics.py # regenerate metrics/output docs
pytest # 46 tests: per-rule logic, metrics, ADTE handoff
python scripts/adte_bridge.py # score findings through ADTE (offline)
```
## 为什么会有这个项目
无法度量的检测只是声明，而不是防御控制。由于这里的每一个异常都是预设的，因此存在真实基准标签（`data/ground_truth.sql`），所以附录报告的是**可度量的** precision 和 recall，而不是主观形容词。这些检测是版本化、经过测试且受 CI 门控的：当规则的逻辑被破坏时，回归测试就会变红。这就是 SQL 工作表与检测即代码之间的区别。
## 阶段 1：基线枚举
在狩猎异常之前，先确立“正常”的标准。所有日期和阈值均来自 `hunt_config` 表（SQLite 视图无法接受参数），因此查询中不会散落任何魔术字面量。
### 1.1 地理登录基线
在基线时间窗口内，每个（用户、国家/地区）对应一行 —— 取代了之前的 `GROUP_CONCAT` + `INSTR` 子字符串测试，那是一种脆弱的 CSV-in-a-column 技巧。
```
SELECT DISTINCT username, country
FROM log_in_attempts
WHERE success = 1
AND login_date < (SELECT value FROM hunt_config WHERE key = 'baseline_end');
```
大多数用户仅从美国进行身份验证；E002 展示了有记录的美加 (US/CA) 旅行模式，该模式稍后将被用作不可能旅行的调优测试。完整基线见 [results/DETECTION_OUTPUT.md](results/DETECTION_OUTPUT.md)。
### 1.2 端点补丁姿态
补丁年限是相对于 `hunt_config.asof_date` 来度量的，而不是硬编码的日期。
```
SELECT device_id, owner_name, days_since_patch, patch_risk
FROM v_patch_status WHERE patch_risk = 'CRITICAL';
```
三台端点超过了 180 天的阈值 —— SRV-001（285天，一台备份服务器）、DEV-011（245天）和 DEV-012（219天）。DEV-011 还存在所有权冲突（阶段 4）。
## 阶段 2：异常检测
七项检测。每一项都指出了相对于先前版本修复的具体缺陷。每条规则都带有 MITRE ATT&CK 技术；已部署的 SQL 位于 [sql/detection_queries.sql](sql/detection_queries.sql)，其实时输出位于 [results/DETECTION_OUTPUT.md](results/DETECTION_OUTPUT.md)。
### Rule 1 — 陌生国家/地区 · `T1078`
从不在用户基线内的国家/地区成功登录。包含两个明确分支：**具有**基线的用户（不在白名单测试），以及**没有**基线的用户（新账户/休眠账户 —— 仅标记首次成功）。
标记了 E001 (CN)、E007 (DE)、E016 (RU，附带)，并且 —— 按照设计 —— 将休眠账户 E017 标记为**良性误报 (FP)**。这个精心设计的 FP 正是 Rule 1 可度量 precision 为 **0.75，而不是可疑的 1.00** 的原因。
### Rule 2 — 不可能旅行 · `T1078`
在每个用户的成功登录上使用 `LAG()`，根据预先计算的国家/地区质心距离进行速度门控。只有当**所需速度超过 900 km/h**（民用航班的巡航速度）时，转移才会被标记 —— 仅凭“N 小时内出现在不同国家/地区”并不能作为证据。
| user | leg | hours | required km/h | verdict |
|---|---|---|---|---|
| E007 | DE → US | 4.3 | ~1,835 | flagged (impossible) |
| E001 | CN → US (return) | 10.8 | ~1,040 | flagged (over gate) |
| E001 | US → CN (outbound) | 13.3 | ~840 | **not** flagged (under gate) |
| E002 | US → CA | 5.5 | ~345 | **not** flagged (legitimate travel) |
**E001，已校准：** 外向的 US→CN 航段（~840 km/h）*低于* 门控 —— 仅从速度来看处于可能的边缘。是紧跟在 03:27 登录之后的**返程**航段（~1,040 km/h）使该案件定性。数字并没有将其变成非黑即白；它们准确地展示了判断的界限所在。US↔CA 走廊还被额外记录在 `travel_exceptions` 表中，作为有记录的降级为 MEDIUM（绝不是静默抑制），因为质心距离夸大了边境地区的旅行。
### Rule 3 — 非工作时间登录 · `T1078`
在用户**自身学习到的时间窗口**（基线最小值/最大值 ± 2h）之外的登录，然后回退到部门时间窗口，最后是全局默认值 —— 而不是硬编码的 9-5 点。`baseline_source` 显示触发了哪一个层级。
### Rule 4 — 暴力破解（先失败后成功） · `T1110.001`
在成功之前一小时内发生的失败，**并带有 `NOT EXISTS` 检查以确保期间没有发生成功登录**。
标记了 E004：来自 **185.220.101.45** 的 8 次连续失败 → 成功，富化步骤将其命名为 **Tor exit node**（参见 [富化](#threat-intel-enrichment)）。
### Rules 5a / 5b — 密码喷洒 vs 失败的暴力破解 · `T1110.003` / `T1110.001`
之前的“密码喷洒”规则**按账户**对失败进行分组，这永远无法检测到真正的喷洒。现将其拆分为两个形状正确的检测：
### Rule 6 — 非活跃账户活动 · `T1078.002`
对任何处于非 Active 状态账户的任何活动 —— 故意没有设置时间或成功过滤。
E016（已离职）收到了两次失败的美国 (US) 尝试，随后是一次**来自俄罗斯 (RU) 的成功登录** (95.213.45.67)。技术为 **T1078.002 (Domain Accounts)** —— 已从执行摘要早先的 T1078.004 (Cloud Accounts) 更正；这是本地部署的域身份验证。
## 阶段 3：分诊与优先级排序
合并后的队列是实时的 `v_triage_queue` 视图 —— 每一行都带有其 ATT&CK 技术以及源 IP 上的任何静态威胁情报匹配。该表是生成的，因此“使用 `SELECT * FROM v_triage_queue` 重现”的说明是真实有效的（在以前并不是）。参见 [results/DETECTION_OUTPUT.md](results/DETECTION_OUTPUT.md)。
### 威胁情报富化
源 IP 与静态 `ip_enrichment` 表进行匹配，该表**镜像了 ADTE 的离线情报范围**（`adte/intel/_mock.py`）—— 因此本实验室和分诊引擎能完全相同地命名相同的基础设施，且无需任何 API keys：
| Source IP | Names as | Seen in |
|---|---|---|
| 185.220.101.45 | `tor-exit` (mock-tor-list, conf 0.85) | E004 brute force |
| 45.33.32.201 / .156 | `scanner` (mock-scanner-feed, conf 0.75) | spray / failed brute force |
| 95.213.45.67 (RU) | *unenriched* — documented feed gap | E016 success |
这取代了之前的逻辑，该逻辑仅仅因为 IP 不在 `10.x` 中就称其为“可疑”。
## 严重性模型及通往 ADTE 的路径
本实验室使用固定的 HIGH/MEDIUM/LOW 严重性等级。这被刻意设计为 ADTE 的**手动前身**，其局限性 —— 无法进行信号叠加、无数值区间、无判定策略 —— 促成了构建一个确定性的 0–100 引擎。映射到 ADTE 的 `classify_verdict` 区间如下：
| Lab severity | ADTE verdict band | Illustrative ADTE scoring |
|---|---|---|
| HIGH | `high_risk` (score > 70; Critical ≥ 90) | E016 RU success: impossible-travel-class + IP reputation + hour anomaly + cluster context → ≥ 75 |
| MEDIUM | `medium_risk` (30–70) | Unfamiliar country alone → 30–50 |
| LOW | `low_risk` (< 30) | After-hours by IT staff → ~10 |
当实验室为每条规则发出一个标签时，ADTE 会将加权信号叠加为一个单一的分数 —— 这正是本实验室所揭示的差距。
### 具体化的交接
上面的映射表并非假设：`scripts/adte_bridge.py` 会将本实验室标记的每个用户通过 ADTE 的实际引擎进行运行，**完全离线**（静态情报表，无 API keys），并将对比结果写入 [docs/ADTE_HANDOFF.md](docs/ADTE_HANDOFF.md)。实际运行的重点如下：
实验室刻意设计的良性 FP (E017) 得分为 8/low_risk —— 引擎与真实基准一致；E016 的已离职账户接管得分为 48，其 `ip_reputation` 为**零**（RU 源是离线情报源的缺口，在数字中可见）；E012 的*失败*暴力破解 —— 在实验室中为 MEDIUM —— 是 ADTE 的**最高分 (62)**，因为信号密度（scanner IP + 失败爆发 + 奇怪时间）超过了单规则的影响。手动输入标志，输出确定性分数和路由：这就是本实验室旨在推动的工作流程。
## 阶段 4：数据完整性验证
基于损坏的资产清单做出的隔离决策会孤立错误的设备。在采取行动之前先进行验证。
### 孤儿设备
`v_orphan_devices` → **DEV-099**，列出的所有者是 E999，但该用户在目录中不存在。无法对其进行归因、通知或验证。
### 资产清单不匹配 —— 错误设备陷阱
`v_inventory_mismatch` → **DEV-011**：主资产清单显示为 Karen Taylor (E011)，备份资产清单显示为 Grace Lee (E007)。**E007 是不可能旅行发现的主体。** 如果基于错误的资产清单进行隔离，你要么隔离了一个不相关员工的设备，要么错过了与正在进行的调查相关的设备。在采取任何行动之前，必须手动核对这两份资产清单 —— `test_integrity.py` 的一个测试用例锁定了 E007-同时也是旅行主体 的关联，因此该场景不会在代码重构中悄无声息地消失。
### 无设备的活跃用户
`v_users_without_devices` → E017–E020：没有分配设备的活跃员工（以前计算过但从未展示）。E016 被正确地**排除在外** —— 它是已离职状态，而不是缺失设备的活跃用户。
## SIEM 转换
三个选择/关联型的检测是位于 [`rules/auth/`](rules/auth) 下的 Sigma 规则；四个有状态的检测（陌生国家/地区、不可能旅行、非工作时间、失败的暴力破解）以 SPL（[`queries/splunk/`](queries/splunk)）和 KQL（[`queries/kql/`](queries/kql)）狩猎查询的形式提供。为什么每条规则是或不是 Sigma 规则 —— 以及为什么这种诚实的划分本身就是重点 —— 都记录在 [docs/SIGMA_NOTES.md](docs/SIGMA_NOTES.md) 中。
## 附录：可度量的检测性能
由 `scripts/compute_metrics.py` 根据真实基准生成（重新生成时此区块会更新；如果内容过期，CI 将失败）：
| Detection Rule | ATT&CK | | Flagged | TP | FP | FN | Precision | Recall |
|---|---|---|---|---|---|---|---|---|
| Unfamiliar Country | T1078 | event | 4 | 3 | 1 | 0 | 0.75 | 1.00 |
| Impossible Travel | T1078 | event | 2 | 2 | 0 | 0 | 1.00 | 1.00 |
| After-Hours Login | T1078 | event | 5 | 5 | 0 | 0 | 1.00 | 1.00 |
| Brute Force (success) | T1110.001 | event | 1 | 1 | 0 | 0 | 1.00 | 1.00 |
| Password Spray | T1110.003 | campaign | 1 | 1 | 0 | 0 | 1.00 | 1.00 |
| Failed Brute Force | T1110.001 | campaign | 1 | 1 | 0 | 0 | 1.00 | 1.00 |
| Inactive Account | T1078.002 | event | 3 | 3 | 0 | 0 | 1.00 | 1.00 |
| **Overall (micro)** | — | — | **17** | **16** | **1** | **0** | **0.94** | **1.00** |
**调优说明**（数字无法捕捉的定性层面）：
| Rule | Tuning note |
|---|---|
| Unfamiliar Country | Cross-reference HR travel; the no-baseline branch is the FP source, by design |
| Impossible Travel | Velocity gate + `travel_exceptions`; centroid distance overstates border hops |
| After-Hours | Weakest signal; global teams inflate FPs in production — corroborate, don't alert alone |
| Brute Force | `NOT EXISTS` intervening-success; raise threshold in noisy estates |
| Password Spray | Threshold is distinct-accounts (breadth), not total failures |
| Inactive Account | Highest fidelity; a terminated account should never authenticate |
有关该方法和低噪声数据集的注意事项，请参见 [docs/METRICS.md](docs/METRICS.md)：真实的遥测数据携带的良性异常要多得多，因此生产环境的精确度会较低 —— 这里的价值在于带有标签的**真实基准方法**，而不是绝对的数字。
## 仓库布局
```
.
├── data/ setup_database.sql, ground_truth.sql, enrichment.sql
├── sql/ detection_queries.sql (all views)
├── rules/auth/ 3 Sigma rules (+ custom lifecycle blocks)
├── queries/ splunk/*.spl, kql/*.kql translations
├── scripts/ build_db.py, compute_metrics.py, validate_rules.py,
│ adte_bridge.py (offline ADTE scoring handoff)
├── tests/ pytest suite (46 tests)
├── docs/ METRICS.md, SIGMA_NOTES.md, SECURITY_REVIEW.md,
│ ADTE_HANDOFF.md, RESPONSE_PLAYBOOK.md, field_dictionary.md
├── results/ DETECTION_OUTPUT.md, adte_handoff.json (generated)
└── .github/workflows/detection-pipeline.yml
```
## 开发与 AI 辅助方法论
检测逻辑、真实基准标签和指标都是确定性的并且归人类所有。误报验证遵循 Trail of Bits 的 `fp-check` 三阶段方法论（数据流 → 对攻击者的意义 → 影响），并采用纯手工方式应用 —— 该插件本身并未安装在此环境中，而且我们没有断言发生过未实际执行的运行，而是直接针对真实基准**度量**了 FP 率。该审查确认了一项候选检测（E017 的无基线标记）属于操作健壮性产物而非真正的正例 —— 它被保留并记录为可度量的 precision 代价，而不是被静默丢弃 —— 并且暴露了改造前逻辑中的三个缺陷（暴力破解计数错误、不可能旅行误报、喷洒覆盖缺口），所有这些都已在此修复。完整报告请见：[docs/SECURITY_REVIEW.md](docs/SECURITY_REVIEW.md)。