审计与证据

阅读约需 71分钟 分钟,9节中含9个示例。

版次 2026.11.31 示例于 2026年11月19日 通过 108个示例中有108个通过 变化内容

AI 翻译。由人工以英文核验。 我们如何制作图书

译自英文版图书,版次 2026.11.31。 阅读原文

读完本章,你将能够为每一次访问决策和每一次查询保存一份审计日志:日志中不含任何值,并且做成链式结构,使任何修改都会暴露。你会在做出决策的地方写入日志,并把日志的头部(最后一条记录的编号和哈希)写到日志写入方无法更改的地方,这样,截至该头部的任何一条记录若被修改或删除,都会被发现。你还会在策略变更之后解释一项旧决策,把日志与查询引擎自己记录的执行情况相互核对,并根据日志和策略生成访问审查和证据报告,这些都是任何人都可以重新运行的代码。

本书所治理的在线学习平台,其支持负责人问为什么上周有一名分析师能读到评论文本,或者审计人员要求平台证明其访问规则有效,两种情形下都没有人能依据记录作答。第 5 章用 Rego 写出了平台的访问策略,第 6 章的执行层 lib.enforce.Enforcer 针对查询读取的每张表向策略请求决策,并改写查询,使 DuckDB 只返回决策允许的内容。这一层在应用每项决策之后就会忘记它。显而易见的补救办法是记录每条查询的文本,但这等于为策略所保护的内容再造一份副本:支持人员的查询可能在 WHERE 子句中带有学习者的电子邮件地址,而第 7 章要求把一名学习者从其数据到过的所有地方清除。

本章的术语如下,均按本书的用法。审计日志是对谁在何时做了什么、结果如何的记录,保存下来以便日后有人核查。平台的审计日志为每次查询保存一条决策记录。策略修订版本是对决策所依赖的、除请求之外的一切内容所取的指纹。日志若能检测出对它的更改(虽然不能阻止更改),就称为可察觉篡改(tamper-evident)。访问审查把每个人可以读取的内容与其实际使用的内容进行比较。证据是一份表明某项控制有效的报告,由记录生成,而不是从屏幕上截取。

代码是本书的共享模块 lib.audit。其中的 Audited 类包装了第 6 章的 Enforcer,但不修改它:它为每次查询写入一条记录,然后让执行层运行该查询。多数示例重放平台一天的查询 lib.audit.DAY:2026 年 10 月 1 日的 13 条查询,来自支持人员、财务、通过课程助手的讲师、市场部门、数据科学家、助手以自身账户发出的查询,以及一名平台不认识的用户。示例运行的是 DuckDB 2.0.0、sqlglot 30.33.0 和 regopy 1.5.2。

在决策发生之处记录每一项决策

最近一次修改见于版次 2026.11.31

审计记录的完整程度,取决于写入者所掌握的信息。查询引擎知道自己执行了哪条 SQL,却不知道是谁发出的查询、出于什么目的。策略知道自己的决策,却看不到查询本身。只有执行层同时掌握全部信息:主体(即发出请求的用户或程序)、查询读取的表和列、每张表的决策,以及交给引擎的 SQL。Open Policy Agent(OPA)正是在这一层写下自己的记录。OPA 的决策日志保存描述策略查询的事件,包括被查询的策略、其输入、bundle 元数据,以及使审计和离线调试成为可能的其他信息(OPA,决策日志)。每个事件都带有 decision_id、被查询规则的 path、input、result、timestamp,以及 bundles[_].revision(即评估时 bundle 的修订版本)。regopy 在 Python 内部运行策略,只返回查询的值,不提供其他内容,因此其余字段都必须由执行层自行构造。

NIST 的安全控制目录为这些字段提供了一份检查清单。控制项 AU-3 要求审计记录确立六件事:(a) 发生了何种类型的事件,(b) 事件何时发生,(c) 在何处发生,(d) 事件的来源,(e) 事件的结果,(f) 与事件相关的个人、主体或对象的身份(NIST SP 800-53 Rev. 5)。

lib.audit.Audited 接收一个目录和一个 Log,后者即审计日志文件。它的 sql 方法接收与 Enforcer.sql 相同的参数,另加 via,即发送该查询的助手(如果有助手发送的话)。它先为查询制定计划,就查询读取的每张表询问策略,写下一条记录,然后才把改写后的查询交给 DuckDB。因此,经由这一层的查询,都不会在没有记录的情况下到达 DuckDB;记录无法写入时,查询就不会运行。第 6 章给本章留下了一种情形:课程助手回答 instructor-17 时,查询以讲师的身份运行,两种身份都应出现在日志中。助手对 instructor-17 的查询,以及它为评分附上姓名的尝试,各自留下一条记录,展示了涉及两种身份时一条记录包含什么:

decision-record.py Python 3.12.15 · duckdb 2.0.0 · pyarrow 26.0.1 · pyiceberg 0.12.0 · regopy 1.5.2 · sqlglot 30.33.0 通过
"""The course assistant's two queries, sent as instructor-17: one allowed, one denied, each recorded with both identities.The point rests on the `user` and `via` lines of both records and on the denial in record 2."""from lib import auditfrom lib.catalog import open_catalogfrom lib.enforce import Deniedfrom lib.platform import scratch# Chapter 6's enforcement layer, wrapped so that every query it's given is recorded before DuckDB runs anything.log = audit.Log(scratch() / "audit.jsonl")platform = audit.Audited(open_catalog(["crm.learners", "learning.reviews"]), log)# instructor-17 asks the course assistant how their reviews look. The assistant writes the SQL; the query runs as the# instructor, with the assistant recorded as the one that sent it.question = "select course_id, count(*) as reviews, round(avg(rating), 2) as rating from learning.reviews group by all"platform.sql(question, user="instructor-17", via="assistant-1").fetchall()# Then the assistant tries to put names to the ratings, which the instructor may not see. The denial is recorded before# it's raised, so the script only needs to go on.try:    platform.sql("select l.full_name, r.rating from learning.reviews as r join crm.learners as l using (learner_id)",                 user="instructor-17", via="assistant-1").fetchall()except Denied:    passfor record in log.records():    print(f"record {record['seq']}:")    print(audit.show(record))
输出python decision-record.py
record 1:
  seq: 1
  event: query
  time: 2026-10-01T09:00:00Z
  catalog: platform
  user: instructor-17
  via: assistant-1
  purpose: null
  query: SELECT course_id, COUNT(*) AS reviews, ROUND(AVG(rating), ?) AS rating FROM learning.reviews GROUP BY ALL
  outcome: allowed
  denial: null
  decision: {"table": "learning.reviews", "columns": ["course_id", "rating"], "allow": true, "row_filters": [{"column": "course_id", "in": [2, 75, 80, 87, 103, 134]}], "column_masks": {}, "reasons": ["instructor may read learning.reviews"]}
  policy_revision: f761e4ad08d4...
  rewritten_digest: 385c5cdabeae...
  prev_hash: 000000000000...
record 2:
  seq: 2
  event: query
  time: 2026-10-01T09:01:00Z
  catalog: platform
  user: instructor-17
  via: assistant-1
  purpose: null
  query: SELECT alias_1.full_name, alias_2.rating FROM learning.reviews AS alias_2 JOIN crm.learners AS alias_1 USING (learner_id)
  outcome: denied
  denial: instructor-17 may not read crm.learners: no group of this user may read this table
  decision: {"table": "crm.learners", "columns": ["full_name", "learner_id"], "allow": false, "row_filters": [], "column_masks": {}, "reasons": ["no group of this user may read this table"]}
  decision: {"table": "learning.reviews", "columns": ["learner_id", "rating"], "allow": true, "row_filters": [{"column": "course_id", "in": [2, 75, 80, 87, 103, 134]}], "column_masks": {}, "reasons": ["instructor may read learning.reviews"]}
  policy_revision: f761e4ad08d4...
  rewritten_digest: null
  prev_hash: 0d3e61eaadeb...

记录 1 涵盖了 AU-3 的六项内容。event 是类型,time 说明时间,采用带 Z 的 UTC 格式,因为控制项 AU-8 要求时间戳“使用协调世界时”或注明其偏移量;示例中的时间取自固定时钟。catalog 说明地点。user 和 via 是来源:查询以 instructor-17 的身份运行,由 assistant-1 发送。再加上决策的 table,它们就是 AU-3 第 f 项所要求的身份。query 和决策的 columns 说明读取了什么,outcome 以及决策的 allow、row_filters、column_masks 和 reasons 说明结果如何,细到行过滤器保留下来的课程 ID。后面的几节会解释 policy_revision、rewritten_digest 和 prev_hash。

记录 2 是一次拒绝,其中包含两张表的决策:该连接可以读取 learning.reviews,但不可以读取 crm.learners,而只要有一张表被拒绝,整个查询就会被拒绝。没有 via,日志只会说讲师试图读取学习者的姓名。有了它,审阅者就知道这条查询是助手写的,从而既审视讲师的行为,也审视助手的行为。

工作准则: 在执行层写下审计记录,因为主体、查询和决策在这里汇合;并且要在查询运行之前写入,使写入失败时查询随之停止。记录所涉及的每一种身份,既包括当事人,也包括代其行事的任何助手;同时记录每张表的决策及其过滤器、掩码和原因。当由引擎自行执行策略时(例如 SQL 引擎 Trino 通过 OPA 执行),应改为启用 OPA 的决策日志,并确保其输入写明所涉及的每一种身份:例如 Trino 的 opa.identity.extra-credentials-keys 会把所列出的凭据“作为身份上下文中的 extraCredentials 转发给 OPA”(Trino 484,OPA 访问控制)。

不要把值写进记录

最近一次修改见于版次 2026.11.31

决策记录中保存的是各类 ID、表名与列名、决策和摘要,而不包含其查询所读取数据中的任何值,因为审计日志中的所有内容本身都会成为需要保护的数据。NIST 对 AU-3 的讨论指出,审计轨迹可能暴露个人身份信息,在记录输入时尤其如此;其增强项 AU-3(3) 要求系统将审计记录中的此类信息限制在隐私风险评估所指明的要素之内(NIST SP 800-53 Rev. 5)。是限制,而不是删除:AU-3 的 f 项要求记录主体,因此每条记录都会写明一个主体,例如 instructor-17,即某位讲师的账号。GDPR 将个人数据定义为“与已识别或可识别的自然人有关的任何信息”(GDPR, Article 4(1)),而一个人可以被直接或间接识别,尤其是通过姓名或在线标识符之类的标识符。像这样的账号 ID 就可能是这样的标识符。平台还有一个更具体的理由,要把学习者排除在日志之外:第 7 章会把一名学习者从其数据触及的每一张表中抹除,而若要从本章新增的链中抹除某一条记录,则与篡改无从区分。

风险在于查询文本。支持人员根据工单中的电子邮件查找某位学习者时,会把这个邮件地址写进查询。lib.audit.normalized 保留查询的形状,舍去其中的值:sqlglot 解析查询,凡是字面量,无论出自 DuckDB 的哪一种语法,都会变成 ?。目录中不存在的名称也一样处理,例如用双引号写出的值;但查询自身定义的别名或 CTE 的名称除外,它们会依次变成 alias_1、alias_2 等。注释可能包含任何内容,因此一律不予记录;拒绝决定的理由同样先去掉其中的值再记录。该层交给 DuckDB 的 SQL 只以 rewritten_digest 的形式保留,它是在只有该层持有的密钥下计算的 HMAC-SHA256,交叉核对用它与 DuckDB 自己的日志相匹配。OPA 的做法则相反,它从完整的输入出发,并且可以被告知去掉策略所指明的部分:“在编码并上传决策日志之前,查询 data.system.log.mask 路径”(OPA, decision logs)。平台的这一层则从不写入这些值。

下面的情形中,agent-a05 先按电子邮件、再按 ID 查找一位 EMEA 学习者,而 analyst-1 读取学习者的电子邮件,策略对其做了假名化。两个值都没有打印出来;下面几行说明各个值保存在何处:

query-without-literals.py Python 3.12.15 · duckdb 2.0.0 · pyarrow 26.0.1 · pyiceberg 0.12.0 · regopy 1.5.2 · sqlglot 30.33.0 通过
"""A record keeps a query's shape and none of its values, while DuckDB's own query log keeps the email as it was typed.The printed lines the prose rests on are the asserts about the email and the four count lines at the end."""from lib import auditfrom lib.catalog import open_catalogfrom lib.platform import load, scratchlog = audit.Log(scratch() / "audit.jsonl")platform = audit.Audited(open_catalog(["crm.learners"]), log)# agent-a05 looks up an EMEA learner by the email in a ticket, then by their ID. Neither value is ever printed.learners = load("crm.learners").to_pylist()learner = next(row for row in learners if row["support_region"] == "emea")      # any EMEA learner will do, since the example needs only oneemail, learner_id = learner["email"], learner["learner_id"]platform.sql(f"select learner_id, full_name from crm.learners where email = '{email}'", "agent-a05").fetchall()platform.sql(f"select email, phone from crm.learners where learner_id = {learner_id}", "agent-a05").fetchall()platform.sql("select learner_id, email from crm.learners", "analyst-1").fetchall()      # pseudonymized with the keyfor record in log.records():    print("query in the record:", record["query"])engine = audit.engine_queries(platform.enforcer.connection)      # DuckDB's own query log, literals and allprint("the email, in the audit log:", email in log.path.read_text())assert email not in log.path.read_text()        # the record keeps `?` where the email was, so nothing matchesprint("the email, in DuckDB's query log:", any(email in query for query in engine))assert any(email in query for query in engine)  # DuckDB logs the SQL text it runs, literals includedprint("the pseudonymization key, in DuckDB's query log:", any(platform.enforcer.key in query for query in engine))assert not any(platform.enforcer.key in query for query in engine)print("its placeholder, in DuckDB's query log:", any("$pseudonym_key" in query for query in engine))assert any("$pseudonym_key" in query for query in engine)# Guessing which learner the agent looked up, from a list of the platform's emails: rebuild the text DuckDB ran with# each email, digest it, and compare. First with a plain SHA-256 of the query, as a log might keep, then with the# record's keyed digest, by someone without the layer's key and by someone who holds it.ran = next(query for query in engine if email in query)guesses = [ran.replace(email, row["email"]) for row in learners]    # the same text, with each learner's email in its placelogged = log.records()[0]["rewritten_digest"]                    # record 1 is the agent's lookup by emailprint("emails tried:", f"{len(guesses):,}")print("emails a plain SHA-256 of the query confirms:", sum(audit.sha256(g) == audit.sha256(ran) for g in guesses))print("emails the keyed digest confirms, without the layer's key:",      sum(audit.digest(g, "a-guessed-key") == logged for g in guesses))     # a wrong key, the only kind a guesser hasprint("emails the keyed digest confirms, with the layer's key:", sum(audit.digest(g, platform.key) == logged for g in guesses))
输出python query-without-literals.py
query in the record: SELECT learner_id, full_name FROM crm.learners WHERE email = ?
query in the record: SELECT email, phone FROM crm.learners WHERE learner_id = ?
query in the record: SELECT learner_id, email FROM crm.learners
the email, in the audit log: False
the email, in DuckDB's query log: True
the pseudonymization key, in DuckDB's query log: False
its placeholder, in DuckDB's query log: True
emails tried: 5,000
emails a plain SHA-256 of the query confirms: 1
emails the keyed digest confirms, without the layer's key: 0
emails the keyed digest confirms, with the layer's key: 1

这些记录保留的是 WHERE email = ? 和 WHERE learner_id = ?,在日志中搜索该电子邮件,一无所获。DuckDB 的查询日志中确实有这个电子邮件,因为它记录的是 DuckDB 所运行的 SQL 文本,字面量也包含在内。假名化密钥则不在其中:第 6 章把密钥作为参数传入,因此在密钥本应出现的位置,DuckDB 的日志里只显示占位符 $pseudonym_key。

对包含电子邮件的查询计算的带密钥摘要,就是该电子邮件的假名,而使它保持为假名的正是密钥,第 6 章对平台的假名已作过说明。重写过程是仓库中的代码,因此任何人只要手里有一份电子邮件清单,比如来自旧的导出文件或一次数据泄露,就能为每个地址重建 DuckDB 所运行的文本并加以检验。在尝试的 5,000 个电子邮件地址中,对该文本做普通 SHA-256 能确认 1 个:即支持人员所查找的那位学习者。记录中的带密钥摘要,在没有该层密钥时能确认 0 个,有密钥时能确认 1 个。密钥只留在该层之内,读日志的人拿不到;需要用到密钥的交叉核对也同样留在该层。

DuckDB 接收值的方式不止带引号的字符串一种,名称里同样可以夹带值。针对 normalized 的回归测试,把同一位学习者的电子邮件、姓名和 ID 以上述每一种方式输入到 agent-a05 的查询中。这些请求留下如下痕迹,每个请求一条记录:

query-shape-hard-cases.py Python 3.12.15 · duckdb 2.0.0 · pyarrow 26.0.1 · pyiceberg 0.12.0 · regopy 1.5.2 · sqlglot 30.33.0 通过
"""Eleven places to type a learner's values into a query, and what each record keeps of it: the query's shape, with a`?` for each value and each name the catalog doesn't hold, and a numbered alias for each alias or CTE the query defines.The printed count of records that hold the email, the name or the IDis the line the prose rests on."""import jsonimport refrom lib import auditfrom lib.catalog import open_catalogfrom lib.enforce import Deniedfrom lib.platform import load, scratchlog = audit.Log(scratch() / "audit.jsonl")platform = audit.Audited(open_catalog(["crm.learners"]), log)# An EMEA learner's email, name and ID, typed into agent-a05's queries as values and as names. None is ever printed.learner = next(row for row in load("crm.learners").to_pylist() if row["support_region"] == "emea")email, name, learner_id = learner["email"], learner["full_name"], learner["learner_id"]cases = {                                                 # each query puts the same values in one more way    "a string": f"select learner_id from crm.learners where email = '{email}'",    "a dollar-quoted string": f"select learner_id from crm.learners where email = $${email}$$",    "an escape string": f"select learner_id from crm.learners where email = e'{email}'",    "a list": f"select learner_id from crm.learners where email in ['{email}']",    "a number": f"select email from crm.learners where learner_id = {learner_id}",    "a comment": f"select email from crm.learners where learner_id = {learner_id} -- {name}",    "a name in double quotes": f'select learner_id from crm.learners where full_name = "{name}"',    # a column, to DuckDB    "a column alias": f'select learner_id as "{email}" from crm.learners',    "a table alias": f'select "{name}".email from crm.learners as "{name}"',    "a CTE's name": f'with "{email}" as (select learner_id from crm.learners) select learner_id from "{email}"',    "a table the catalog doesn't hold": f'select learner_id from "{email}"',}for query in cases.values():    try:        platform.sql(query, "agent-a05").fetchall()      # a denial is recorded, then raised: the script carries on    except Denied:        pass# What each record keeps of its query. The email or the name anywhere in its line, or the ID in what it keeps, is a# value that got through, and is never printed.held = []for case, line in zip(cases, log.lines()):               # one record per query, in the order they ran    record = json.loads(line)    kept = record["query"] + (f" (denied: {record['denial']})" if record["denial"] else "")    held.append(email in line or name in line or bool(re.search(rf"\b{learner_id}\b", kept)))     # the ID as a whole word    print(f"{case}:", "(holds a value)" if held[-1] else kept)print("records holding the email, the name or the ID:", sum(held), "of", len(cases))
输出python query-shape-hard-cases.py
a string: SELECT learner_id FROM crm.learners WHERE email = ?
a dollar-quoted string: SELECT learner_id FROM crm.learners WHERE email = ?
an escape string: SELECT learner_id FROM crm.learners WHERE email = ?
a list: SELECT learner_id FROM crm.learners WHERE email IN (?)
a number: SELECT email FROM crm.learners WHERE learner_id = ?
a comment: SELECT email FROM crm.learners WHERE learner_id = ?
a name in double quotes: SELECT learner_id FROM crm.learners WHERE full_name = ? (denied: Column '?' could not be resolved)
a column alias: SELECT learner_id AS alias_1 FROM crm.learners
a table alias: SELECT alias_1.email FROM crm.learners AS alias_1
a CTE's name: WITH alias_1 AS (SELECT learner_id FROM crm.learners) SELECT learner_id FROM alias_1
a table the catalog doesn't hold: SELECT learner_id FROM ? (denied: not a table in the catalog: "?")
records holding the email, the name or the ID: 0 of 11

每条记录都只保留查询的形状而不含任何值,被允许的和被拒绝的都是如此。最后一行统计包含该电子邮件、姓名或 ID 的记录数:0 of 11。

有两点损失。规范化后的文本把无害的值和名称也一并舍去了:在第一条记录中,ROUND(AVG(rating), 2) 变成了 ROUND(AVG(rating), ?);在第二条记录中,别名 r 和 l 变成了 alias_2 和 alias_1。此外,这份日志无法回答“谁查过这位学习者?”。当平台需要这一答案时,例如有学习者询问谁读取过其数据,应把它记在单独的访问日志中:其中使用学习者 ID,设定保留期限,且不成链,第 7 章的抹除会像对待其他任何表一样触及它。

工作准则: 记录查询的形状,绝不记录其中的值:替换每一个字面量,无论引擎接受哪一种语法,也替换每一个目录中不存在的名称;去掉注释;从拒绝理由中取出同样的值;所执行的 SQL 只以带密钥摘要的形式保留,且日志的读者永远拿不到该密钥。切勿对可能含有个人数据的文本使用普通哈希。当需要知道谁读取过某个人的数据时,把它记在受保留期限和抹除约束的单独日志中,而不要记在防篡改的日志里。

在旧决策自身的策略下重放

最近一次修改见于版次 2026.11.31

决策记录保存了请求,但一次决策取决于三样东西:请求、策略的规则,以及策略读取的数据。后两者时常变化。如第 5 章所设定的,平台的策略读取 governance/users.toml、governance/metadata.toml 和 learning.courses,因此,一名支持人员被调往另一个区域,或者有一门课程被添加到 learning.courses,都会改变决策,而无需任何人改动 Rego。如果记录中没有写明当时的策略是什么,那么一旦其中任何一项发生变化,这条决策就无法再被解释:日后重放时会得出不同的答案,日志看起来就像是错的。OPA 将策略的版本记录为 bundles[_].revision。对于调用了非确定性内置函数(例如时间或 HTTP 请求)的策略,OPA 还可以把这些函数的结果记录在 nd_builtin_cache 中,该字段用于调试和决策重放(OPA,决策日志)。平台的策略不调用任何此类函数。

lib.audit.bundle 把策略的各个模块与它的数据文档(即策略所读取的事实,由上述三个来源生成)组合在一起,正如 OPA 以 bundle 的形式分发它们那样。lib.audit.revision 是该 bundle 的 SHA-256:无论是任何一条规则发生变化,还是策略读取的任何一项事实发生变化,这个指纹都会随之改变。每条记录都带有做出该决策的修订版本。lib.audit.Archive 按修订版本保存该层用过的每一个 bundle,这样,一次决策就可以在当初做出它的那份策略下重新做出。bundle 中包含的是规则和平台用户的属性,而不是关于学习者的数据,因此这个归档可以与日志保存同样长的时间。

在该平台上,analyst-1 读取学习者的电子邮件,得到的是化名。假设后来的一个拉取请求把它加入了 EMEA 的支持组,这正是第 6 章借助其谁能看到什么的测试所发现的那种变更。旧记录在两个修订版本下分别重放:

explain-old-decision.py Python 3.12.15 · duckdb 2.0.0 · pyarrow 26.0.1 · pyiceberg 0.12.0 · regopy 1.5.2 · sqlglot 30.33.0 通过
"""Replays a logged decision under the revision it was made with and under the current one, after a change to the usersfile alone. The printed email and match lines are the point: only the archived revision explains the record."""from lib import audit, policyfrom lib.catalog import open_catalogfrom lib.platform import scratchfolder = scratch()archive = audit.Archive(folder / "bundles")         # every policy revision that has decided anything, by revisionlog = audit.Log(folder / "audit.jsonl")# analyst-1 reads learners' emails, which the policy pseudonymizes.platform = audit.Audited(open_catalog(["crm.learners"]), log, archive=archive)platform.sql("select learner_id, email from crm.learners", "analyst-1").fetchall()record = log.records()[0]# Later, a pull request adds analyst-1 to support for EMEA, as in chapter 6. Only users.toml changes, which reaches# the policy through its data document, and no Rego.data = policy.data_document()data["platform"]["users"]["analyst-1"] = {"groups": ["data-science", "support"], "region": "emea"}current, logged = audit.bundle(data=data), archive.get(record["policy_revision"])     # the bundle the record was decided underprint("logged under revision:", record["policy_revision"][:12])print("current revision:", audit.revision(current)[:12])modules_changed = current["modules"] != logged["modules"]assert not modules_changed                      # no Rego changed, so the policy modules are the sameprint("policy modules changed:", modules_changed)data_changed = current["data"] != logged["data"]assert data_changed                             # the data document is the only part that changedprint("data document changed:", data_changed)# Explain the logged decision by making it again, from the request the record holds, under each revision.print("email, as logged:", record["decisions"][0]["column_masks"].get("email", "shown as stored"))for name, revision in [("the current revision", current), ("the logged revision", logged)]:    again = audit.replay(record, revision)    matches = again == record["decisions"]    assert matches == (revision is logged)      # only the revision the record was decided under explains it    print(f"under {name}, email:", again[0]["column_masks"].get("email", "shown as stored"))    print(f"under {name}, the decision matches the log:", matches)
输出python explain-old-decision.py
logged under revision: f761e4ad08d4
current revision: 0350e789b34d
policy modules changed: False
data document changed: True
email, as logged: pseudonymize
under the current revision, email: shown as stored
under the current revision, the decision matches the log: False
under the logged revision, email: pseudonymize
under the logged revision, the decision matches the log: True

修订版本变了,从 f761e4ad08d4 变为 0350e789b34d,而 Rego 并没有改动。策略模块是相同的,不同的是数据文档,它现在列出了新的成员关系。只对策略文件取指纹,就会漏掉这一变化。在当前修订版本下重放时,决策与日志不一致:email 是 shown as stored,因为支持人员能看到联系方式。只持有当前策略的调查者会断定日志或该层有误。而在归档中取出日志所记录的修订版本重放时,email 又回到 pseudonymize,决策与日志一致。

重放解释的是决策,而不是数据行。行过滤器是作为规则被重放的,例如支持人员所在的区域,或者同意过滤器 learner_consented,而哪些行通过了过滤,取决于当天的数据,这些数据此后可能已经变化,或已被删除。这种划分是有意为之:日志说明规则是什么,而表(连同它们各自的快照和保留策略)说明规则匹配到了什么。

工作准则: 为每一次决策取指纹,所用的哈希涵盖除请求之外它所依赖的一切:策略的各个模块及其数据文档,二者合在一起。每个修订版本都要归档,保存期限与日志相同;解释旧决策时,应在它自己的修订版本下重放,绝不使用当前版本。如果策略读取时钟或调用其他服务,也要记录这些结果,就像 OPA 的 nd_builtin_cache 所做的那样。

将记录链接起来,使篡改可见

最近一次修改见于版次 2026.11.31

平台的决策记录只有在对其所做的更改能够被检测到时,才称得上证据。SP 800-53 要求系统“保护审计信息和审计日志工具,防止未经授权的访问、修改和删除”(AU-9),并在 AU-9(3) 中要求“实施密码学机制,以保护审计信息和审计工具的完整性”。其 AU-9(3) 的讨论给出的一个示例是“使用非对称密码学的签名哈希函数”(NIST SP 800-53 Rev. 5),这种签名只有私钥持有者才能生成。下文平台所用的链并非如此,它自身不提供任何保护。NIST 的日志管理指南 SP 800-92 描述了最朴素的机制:为每个日志文件计算一个消息摘要并安全存放,从而检测出对已归档日志的更改(NIST SP 800-92)。该指南发布于 2006 年,将 MD5 和 SHA-1 列为当时常用的摘要算法,其脚注 25 建议使用 SHA-256,平台采用的正是 SHA-256。

每个文件一个摘要,能够检测对已关闭文件的更改。哈希链则能检测对仍在增长的日志的更改:每条记录都带有一个序列号 seq,以及前一行的 SHA-256 值 prev_hash,因此修改一条记录会使它与下一条记录之间的链接断裂,删除一条记录则会留下缺口。lib.audit.check 遍历整条链,并报告每一处断裂。链是可发现篡改的,而不是防篡改的。Schneier 和 Kelsey 设计过一种日志,即使攻击者控制了记录日志的机器,也既无法读取、也无法伪造此前已写入的条目。即便如此,攻击者仍然“可以删除一段条目(或整个日志文件)”,而日志所报告的可信方,要等到这台机器下一次与它联系时才会察觉(Schneier and Kelsey, 1999)。像平台这样的普通链则更弱:任何能写入该文件的人都可以重建它,一路重新编号。因此,它能证明什么,取决于链的链头,即记录的条数与最后一条记录的哈希,这个链头要写在日志写入方无法更改的地方。对该链头之前的任何记录所做的编辑或删除,无论如何掩饰,都会显现出来。在链头之后,经过重建或截断的链能通过所有检查;而没有链头时,从头重建的链同样能通过。check 将链头作为锚点。

为了测试这条链,该示例重放 lib.audit.DAY,即平台在 10 月 1 日收到的 13 条查询,并在中午、即第 9 条记录处写下链头。随后,它以五种方式篡改日志的副本。中午之前,第 3 条记录,即 agent-a05 对某位学习者联系方式的查询,被改为由另一个智能体发起,一次是直接编辑,一次是同时重建了链。中午之后,第 12 条记录,即平台不认识的用户 someone-else 被拒绝的读取,被删除,一次是直接删除,一次是同时重建了链;此外,当天的最后两条记录被截去。运行之前,请先预测:这五种篡改中,哪些是链自身就能发现的,哪些要对照中午的链头才能发现。

chain-catches-edit.py Python 3.12.15 · duckdb 2.0.0 · pyarrow 26.0.1 · pyiceberg 0.12.0 · regopy 1.5.2 · sqlglot 30.33.0 通过
"""Replays the day's queries into a chained log, writes the head down at noon, then tampers with copies of the log fiveways and checks each against the chain alone and against the noon head. The point rests on the two checks of eachchange: the chain finds the plain edit and the plain removal on its own, and only the head finds the rebuilt chain."""import jsonfrom lib import auditfrom lib.catalog import open_catalogfrom lib.platform import scratchlog = audit.Log(scratch() / "audit.jsonl")platform = audit.Audited(open_catalog(["crm.learners", "learning.reviews", "billing.payments", "support.tickets"]), log)audit.run_day(platform, [query for query in audit.DAY if query[0] < "12:00"])     # the morning; times compare as stringsanchor = log.head()                               # the head, written down at noon somewhere the log's writer can't reachaudit.run_day(platform, [query for query in audit.DAY if query[0] >= "12:00"])    # the afternoonlines = log.lines()print("records:", len(lines))print("head written down at noon, at record:", anchor["records"])print("record 3:", json.loads(lines[2])["user"], "reads", json.loads(lines[2])["query"])     # record 3 is index 2def rechained(lines: list[str]) -> list[str]:    """The chain built again, every record renumbered and every prev_hash recomputed: what anyone who can rewrite the    log's file can do."""    rebuilt, previous = [], audit.GENESIS          # the first record points at 64 zeros, not at a record    for seq, line in enumerate(lines, start=1):        rebuilt.append(audit.canonical(json.loads(line) | {"seq": seq, "prev_hash": previous}))        previous = audit.sha256(rebuilt[-1])    return rebuilt# Before noon, record 3 handed to another agent. After noon, record 12 (someone-else's denied read) removed, and the# day's last two records cut off.edited = lines[:2] + [audit.canonical(json.loads(lines[2]) | {"user": "agent-a01"})] + lines[3:]     # index 2 is record 3removed = lines[:11] + lines[12:]                                                                    # index 11 is record 12for name, version in [("untouched", lines), ("record 3 edited", edited), ("record 3 edited, chain rebuilt", rechained(edited)),                      ("record 12 removed", removed), ("record 12 removed, chain rebuilt", rechained(removed)),                      ("last 2 records removed", lines[:-2])]:    print(f"{name}, checked alone:", "; ".join(audit.check(version)) or "no problem found")    print(f"{name}, checked against the noon head:", "; ".join(audit.check(version, anchor)) or "no problem found")
输出python chain-catches-edit.py
records: 13
head written down at noon, at record: 9
record 3: agent-a05 reads SELECT full_name, email, phone FROM crm.learners WHERE learner_id = ?
untouched, checked alone: no problem found
untouched, checked against the noon head: no problem found
record 3 edited, checked alone: record 3 was changed: record 4 doesn't follow it
record 3 edited, checked against the noon head: record 3 was changed: record 4 doesn't follow it
record 3 edited, chain rebuilt, checked alone: no problem found
record 3 edited, chain rebuilt, checked against the noon head: the chain no longer passes through the head written down at record 9
record 12 removed, checked alone: record 12 is missing: record 13 follows record 11
record 12 removed, checked against the noon head: record 12 is missing: record 13 follows record 11
record 12 removed, chain rebuilt, checked alone: no problem found
record 12 removed, chain rebuilt, checked against the noon head: no problem found
last 2 records removed, checked alone: no problem found
last 2 records removed, checked against the noon head: no problem found

仅凭链自身,只能发现直接编辑 record 3 was changed: record 4 doesn't follow it 和直接删除 record 12 is missing: record 13 follows record 11,此外一无所获。它漏掉了两种重建后的链,这两种链从头到尾保持一致;它也漏掉了截断,因为最后一条记录之后没有任何记录来察觉缺失。

链头只保护它之前写入的记录。对照中午的链头,中午之前所做的编辑即使重建了链仍会显现:the chain no longer passes through the head written down at record 9。中午之后隐藏的一切都能通过:重建了链的删除给出 no problem found,截断给出 no problem found。

这就是关于值的规则并非可有可无的原因。从带链的日志中抹除某位学习者,意味着更改或删除一条记录,而只要某项检查能看到这一点,它就无法将其与篡改区分开。不含学习者数据的日志,永远不会遇到第 7 章所说的擦除问题。至于发起查询的人的 ID,则是另一回事:为日志设定保留期,每个保留期开始一条新链,并写下上一条链的最后链头(即每个保留期的链,其最后的链头都要写下),这样整个保留期到期时即可整体删除。

检查一条链意味着重新读取每一条记录。Crosby 和 Wallach 描述了树状结构的日志,它们可以证明“各条已记录的事件仍然存在,并且日志现在的状态与过去所见的状态是一致的”,其证明的大小为对数级,改进了早期的线性构造(Crosby and Wallach, 2009)。对于单个平台自己的日志,我们建议使用每日检查的链;当外部方要审计一份大到无法重新读取的日志时,则使用树。

工作准则: 用 SHA-256 和序列号将每条记录链接到前一条记录,并且只指望这条链保护到最后一个在该层控制之外写下的链头为止的记录。要经常写下链头,写到仅追加的存储中,或写到使用其他凭据的另一个系统中,频率取决于你所能接受的未受保护记录的数量;如果这个数量为零,就在每条记录写入时立即将其发送出这台机器。按计划对照最后一个链头检查这条链。如果你需要 AU-9(3) 所描述的保护,就让一个该层永远不持有其密钥的系统为每个链头签名。切勿将日后可能需要为某一个人而擦除的记录加入链中;改让整个保留期到期后整体清除。

将日志与 DuckDB 的查询日志交叉核对

最近一次修改见于版次 2026.11.27

平台的审计日志记录经过执行层的每一条查询,也只记录这些查询。如果有代码直接在执行层的 DuckDB 连接上运行查询,例如笔记本中的辅助函数,或是拿到该连接的测试,这样的查询既没有决策,也没有记录。要发现这类查询,需要引擎自己保存的第二份记录。DuckDB 2.0.0 带有这样一份记录,默认关闭:它的日志框架有一个 QueryLog 类型,用于记录 DuckDB 执行的查询,通过 CALL enable_logging('QueryLog') 开启,并从 duckdb_logs 视图中读取(DuckDB,日志)。另有一项设置 log_query_path,用于指定记录查询的文件(DuckDB,配置),而执行层的连接会拒绝这项设置,生产环境一节对此有说明。

DuckDB 的日志不是审计日志。每个条目包含 SQL 文本、连接 ID、事务 ID、查询 ID 和时间戳:没有用户,也没有决策。它只能看到改写后的查询,并且归属于被它记录的进程。它能做的是核对审计日志:DuckDB 运行的每一条查询,都应当是某条决策记录交给它的 SQL。Audited 在启动时开启 DuckDB 的日志,并锁定连接的配置。Audited.check_engine_log 用带密钥的摘要,把 DuckDB 日志中的每条查询与上次核对以来所写记录的 rewritten_digest 相匹配,然后清空 DuckDB 的日志,因为其中保存着查询的值。关系对象的查询会在“Output 一节中的某个方法读取该关系时运行,并且每次读取都会再次运行”(DuckDB,关系 API),而收窄后的关系会运行它自己的 SQL,这并非任何记录交给 DuckDB 的。因此 Audited.sql 自己获取每个结果,仅获取一次,再把这些行作为关系交还,该关系建立在它自己的连接上:无论调用方如何处理它,DuckDB 的日志中每条被允许的查询都只出现一次。

第一次核对重放当天的 13 条查询并加以核对。随后又有两条查询经过执行层,一条的调用方从未获取其结果,另一条被收窄为五行;同时,一个笔记本辅助函数使用执行层的连接读取了一位学习者的电子邮件,绕过了执行层。接着进行第二次核对:

query-log-cross-check.py Python 3.12.15 · duckdb 2.0.0 · pyarrow 26.0.1 · pyiceberg 0.12.0 · regopy 1.5.2 · sqlglot 30.33.0 通过
"""DuckDB's query log checked against the audit log: first on the day's queries, then after two more queries through thelayer and a read around it. The point rests on the last check's unrecorded query, the one read that bypassed the layer."""from lib import auditfrom lib.catalog import open_catalogfrom lib.platform import scratchlog = audit.Log(scratch() / "audit.jsonl")platform = audit.Audited(open_catalog(["crm.learners", "learning.reviews", "billing.payments", "support.tickets"]), log)day = audit.run_day(platform)print("queries allowed:", day["allowed"])print("queries denied:", day["denied"])# DuckDB's own query log, kept on the layer's connection, against the records written since the last check. The check# then empties DuckDB's log, which holds the queries' values.found = platform.check_engine_log()print("queries DuckDB ran:", found["engine queries"])print("queries DuckDB ran with no decision record:", len(found["unrecorded"]))# Two more queries through the layer: one whose caller never fetches it, and one the caller narrows to five rows.platform.sql("select learner_id, country from crm.learners", "analyst-1")        # the layer fetches it, so DuckDB logs it onceplatform.sql("select learner_id, country from crm.learners", "analyst-1").limit(5).fetchall()    # narrowed after the layer's fetch# Then a notebook helper that was handed the layer's connection reads a learner's email with it, around the layer.platform.enforcer.connection.sql("select email from crm.learners where learner_id = 541").fetchall()    # no record for this onefound = platform.check_engine_log()print("next check, records checked:", " to ".join(map(str, found["records checked"])))print("next check, queries with no decision record:", len(found["unrecorded"]))for query in found["unrecorded"]:    print("unrecorded:", query)print("next check, decision records DuckDB has no query for:", len(found["not in the engine log"]))
输出python query-log-cross-check.py
queries allowed: 9
queries denied: 4
queries DuckDB ran: 9
queries DuckDB ran with no decision record: 0
next check, records checked: 14 to 15
next check, queries with no decision record: 1
unrecorded: SELECT email FROM crm.learners WHERE learner_id = ?
next check, decision records DuckDB has no query for: 0

交叉核对把当天的查询与其记录相匹配:9 条被允许,4 条被拒绝,DuckDB 运行了 9 条:每条被允许的查询对应一条,被拒绝的查询一条也没有,另有 0 条没有记录。

下一次核对只发现绕过执行层的那次读取,没有发现其他问题。核对覆盖 14 to 15 条记录,即经过执行层的那两条查询,没有发出警报。绕过执行层的读取表现为 1 条没有决策记录的查询,输出时不带其值。最后一行反向核对,查找 DuckDB 没有留下任何查询痕迹的记录:这里为 0。

核对能发现什么,取决于谁持有连接。它能发现不碰 DuckDB 日志的代码绕过执行层的读取,例如这个笔记本辅助函数、某个测试,或被误交连接的作业。它发现不了持有连接并有意隐藏的代码。如果在一次核对之后读取,并在执行层的下一条查询之前再次清空 DuckDB 的日志,那么两个方向都找不到任何东西。而且 DuckDB 的 write_log 函数接受日志类型作为参数,因此可以写入 QueryLog 条目(DuckDB 源码,v2.0.0);配置锁定无法阻止函数调用。要防范这类代码,需要把引擎的日志写到持有连接的进程之外。

核对还会发现执行层自身的捷径,例如第 6 章的 observe,它在执行层的连接上读取存储的表,背后没有用户。应当为这类查询建立它们自己的记录,以执行层作为用户,而不是在核对中设置例外。

工作准则: 凡是执行策略的地方,都要开启引擎自带的查询日志,在执行层内部获取每一个结果,并按计划将日志与上次核对以来所写的记录双向核对:引擎运行的每条查询都必须匹配一条记录,每条被允许的记录也都必须匹配一条查询。然后清空日志,因为它保存着查询的值。把不匹配当作需要解释的事件,而不是要过滤的噪声。核对能发现的是失误,而不是持有连接并有意隐藏的代码:对付后者,只有在进程之外写入的日志才有帮助。不要把引擎的日志当作审计日志:它没有用户,也没有决策。

这部分尚未翻译,因此以英文显示。

Generate the access review from the log

最近一次修改见于版次 2026.11.27

The platform's audit log says what each person read, and the policy says what each person may read; an access review puts the two side by side. SP 800-53 asks organizations to review accounts for compliance with account management requirements (AC-2 j), and to review the privileges assigned to roles and classes at a set frequency, to validate the need for them, reassigning or removing them where necessary (AC-6(7)) (NIST SP 800-53 Rev. 5). A list of who holds which role answers neither, because it says nothing about need. Code can compute the facts a review needs: what the policy allows each person, what the log shows they used, what they were denied, and memberships that point at nothing. Whether to remove access stays a decision for the data's owners.

lib.audit.review evaluates the platform's policy for every user in users.toml against every table, reads the log, and lists for each user the tables they may read, the tables they read, the grants they didn't use, their denied queries with the assistant that sent them, and anything someone should look at. To give it something stale to find, the example adds analyst-2 to the users, still listed in bi, a group the policy no longer has, as happens when a group is retired and nobody edits the users file:

access-review.py Python 3.12.15 · duckdb 2.0.0 · pyarrow 26.0.1 · pyiceberg 0.12.0 · regopy 1.5.2 · sqlglot 30.33.0 通过
"""An access review of every user in the users file, and every user the log names, against the policy and the day's log,with a stale group membership added to find. The printed `look at` lines and the grants-not-used count are what the proserests on."""from lib import audit, policyfrom lib.catalog import open_catalogfrom lib.platform import TABLES, scratchlog = audit.Log(scratch() / "audit.jsonl")platform = audit.Audited(open_catalog(["crm.learners", "learning.reviews", "billing.payments", "support.tickets"]), log)audit.run_day(platform)      # the day's queries, so that the review has a log to read# users.toml as the review finds it: analyst-2 is still listed in bi, a group the policy no longer has.data = policy.data_document()data["platform"]["users"]["analyst-2"] = {"groups": ["bi"]}rows = audit.review(audit.bundle(data=data), log.records(), TABLES)      # all eight of the platform's tablesfor row in rows:    print(f"{row['user']}: may read {len(row['may read'])}, read {len(row['read'])}, denied {row['denied']}"          + (f" (sent by {', '.join(row['denied via'])})" if row["denied via"] else ""))    if row["unused"]:            # a grant unused: a table the policy lets the user read, and the log never shows them reading        print("  not used:", ", ".join(row["unused"]))    for finding in row["findings"]:        print("  look at:", finding)print("users reviewed:", len(rows))print("grants not used:", sum(len(row["unused"]) for row in rows))print("users to look at:", sum(bool(row["findings"]) for row in rows))     # any finding at all needs a personprint("denied queries flagged at:", audit.settings()["review"]["denied_queries_flagged"])     # from governance/audit.toml
输出python access-review.py
agent-a01: may read 2, read 0, denied 0
  not used: crm.learners, support.tickets
agent-a05: may read 2, read 2, denied 0
agent-a09: may read 2, read 0, denied 0
  not used: crm.learners, support.tickets
analyst-1: may read 6, read 2, denied 0
  not used: billing.payments, learning.courses, learning.enrollments, learning.events
analyst-2: may read 0, read 0, denied 0
  look at: in group bi, which the policy doesn't know
assistant-1: may read 2, read 1, denied 0
  not used: learning.courses
finance-1: may read 3, read 1, denied 0
  not used: learning.courses, learning.enrollments
instructor-17: may read 3, read 1, denied 3 (sent by assistant-1)
  not used: learning.courses, learning.enrollments
  look at: 3 denied queries
marketing-1: may read 2, read 1, denied 0
  not used: learning.courses
someone-else: may read 0, read 0, denied 1
  look at: not in users.toml
users reviewed: 10
grants not used: 14
users to look at: 3
denied queries flagged at: 3

Over Oct 1, the day lib.audit.DAY replays, 14 grants went unused, and agent-a01 and agent-a09 read nothing at all. One day is too short to remove anyone's access, since support works in shifts and finance reads some tables only when it closes the month, so these counts are leads, not decisions. A grant unused for a whole review period, such as a quarter, is what to bring to the table's owner. 3 users need a person to look at them:

  • analyst-2 is in a group no rule knows. The membership grants nothing today, and would grant whatever a future rule for bi allows, so remove it.
  • someone-else isn't in users.toml, so a principal the platform doesn't know tried to read learners' emails.
  • instructor-17's line reads may read 3, read 1, denied 3 (sent by assistant-1). In the two minutes after answering the instructor's first question, the assistant tried learners, then learners joined to reviews, then tickets. That is an assistant trying tables one by one, and without via the review would have blamed the instructor.

The threshold for flagging, 3 denied queries, is a rule of thumb, kept in governance/audit.toml with its reasoning, so that changing it is a reviewed change, like a policy change.

Working rule: Generate access reviews from the policy and the audit log, never from a list of role memberships: what each person may read against what they read, their denials, and memberships the policy no longer knows. Run them over a period long enough for every job's rhythm, such as a quarter, and send each finding to the owner of the table or group it concerns. Remove a grant nobody used in the period unless its owner says why it's needed.

以数据形式产出证据

最近一次修改见于版次 2026.11.27

链检查、交叉核对和访问评审,只有在团队之外的人也能检验时,价值才最大。欧盟《通用数据保护条例》(GDPR)以问责原则将这一义务加在组织身上:“控制者应对第 1 款负责,并且能够证明其合规(‘问责’)”(GDPR 第 5(2) 条)。怎样才算证明,是法律人士要回答的问题;本节展示的是代码能够产出什么。NIST 针对账户管理的评估程序列出了评估人员要查验的记录,其中包括访问授权记录、账户管理合规评审和系统审计记录(NIST SP 800-53A Rev. 5,见 NIST 的 OSCAL 目录)。控制台截图不属于其中任何一种:没有人能重新运行它,把它与上一季度的结果比较,或者说出它展示的是哪一版策略。

lib.audit.evidence 根据记录和代码构建平台的报告,包含以下几个部分:

  • 策略,附每个模块和数据文档的摘要;
  • 策略的单元测试,来自第 5 章;
  • 谁能看到什么的对照表,并与策略核对;
  • 分类覆盖情况,来自第 2 章的元数据检查;
  • 审计日志的链检查;
  • 与 DuckDB 日志的交叉核对;
  • 日志中记录的删除;
  • 访问评审。

每个部分都写明它是在哪一版策略下计算的、其输入的摘要,以及他人重新计算所需的一切:归档的包、测试文件、metadata.toml 和各表的模式、审计日志。引擎交叉核对只能部分地重新计算,报告也如实说明这一点。它保存了 DuckDB 所运行的每条查询的带密钥摘要,因此任何持有日志的人都能重做其中的两项计数;但只有这一层读取过 DuckDB 的日志,而且只读了一次,随后就将其清空。下面为 10 月 1 日生成报告,数据包括目录中的每张表和每个管道输出、第 7 章中在 07:00 记入日志的一名学习者的删除,以及当天的查询:

evidence-report.py Python 3.12.15 · duckdb 2.0.0 · openlineage-python 1.55.0 · pyarrow 26.0.1 · pyiceberg 0.12.0 · pyyaml 6.0.3 · regopy 1.5.2 · sqlglot 30.33.0 通过
"""The platform on Oct 1, after an erasure and the day's queries: the first evidence report, written to a file as data, witheach section's result printed. The printed checks, the erasure's lines and the head are what the prose rests on."""import jsonfrom datetime import timedeltafrom lib import audit, retentionfrom lib.platform import scratch# The platform on Oct 1: every table and pipeline output, chapter 7's erasure of learner 3590 at 07:00, then the day's# queries through the audited layer.catalog, graph = retention.open_platform(statistics_off=True)      # statistics off for personal columns: each table rewritten under them, old files sweptlog = audit.Log(scratch() / "audit.jsonl")_, found, done = retention.erase_everywhere(catalog, graph, 3590, "erasure-0001", min_age=timedelta(0))    # zero: no file age guardaudit.record_erasure(log, "erasure-0001", done, retention.scan(catalog, found), at=audit.MIDNIGHT.replace(hour=7))platform = audit.Audited(catalog, log)audit.run_day(platform)# The first report, so there's no head from an earlier one to check the chain against.report = audit.evidence(platform, catalog, anchor=None)reports = scratch()          # in production, another system, which the log's writer can't change(reports / "evidence-2026-10-01.json").write_text(json.dumps(report, indent=2, sort_keys=True), encoding="utf-8")result = {name: section["result"] for name, section in report.items()}print("sections:", ", ".join(report))print("policy tests passed:", f"{result['policy tests']['passed']} of {result['policy tests']['of']}")print("who-sees-what cases the policy agrees with:", f"{result['who sees what']['agree']} of {result['who sees what']['of']}")for problem in result["classification coverage"]["problems"]:    print("classification problem:", problem)print("audit log records:", result["audit log"]["records"])print("chain problems, checked alone:", len(result["audit log"]["chain problems"]))print("queries DuckDB ran with no decision record:", len(result["engine cross-check"]["unrecorded"]))print("decision records DuckDB has no query for:", len(result["engine cross-check"]["not in the engine log"]))for erasure in result["erasures"]:    print(f"erasure {erasure['request']}: {erasure['rows_deleted']} rows deleted, "          f"{erasure['files_holding_the_person']} of {erasure['files_read']} files hold the person")    for kept in erasure["kept"]:        print(f"erasure {erasure['request']}, kept under an exception: {kept['table']}, decided by {kept['decided_by']},",              f"{', '.join(kept['columns_erased'])} erased")print("chain head written down, at record:", report["audit log"]["inputs"]["head"]["records"])     # the next report's anchorprint("engine cross-check, computed again from:", "; ".join(report["engine cross-check"]["computed again from"]))print("engine cross-check, not computed again:", report["engine cross-check"]["not computed again"])
输出python evidence-report.py
sections: policy, policy tests, who sees what, classification coverage, audit log, engine cross-check, erasures, access review
policy tests passed: 12 of 12
who-sees-what cases the policy agrees with: 12 of 12
classification problem: ai.review_corpus: no metadata
classification problem: marketing.audience: no metadata
audit log records: 14
chain problems, checked alone: 0
queries DuckDB ran with no decision record: 0
decision records DuckDB has no query for: 0
erasure erasure-0001: 25 rows deleted, 0 of 115 files hold the person
erasure erasure-0001, kept under an exception: billing.payments, decided by legal team, card_number erased
chain head written down, at record: 14
engine cross-check, computed again from: the audit log; the engine digests, for the two counts
engine cross-check, not computed again: the reading of DuckDB's log, and the unrecorded queries' shapes: the layer read that log once, then emptied it

报告既显示未通过的项,也显示通过的项,因为只能显示通过检查的报告,什么也证明不了。策略通过了 12 of 12 项单元测试,并与 12 of 12 个谁能看到什么的用例一致。分类覆盖情况并不干净:ai.review_corpus 和 marketing.audience 没有元数据,这正是第 2 章和第 4 章发现的缺口,而策略暂时将它们视为敏感数据。

日志自身的检查结果干净,但它们能证明的比看上去的少。由于此前没有报告写下过链头,单独检查时,链有 0 个问题。交叉核对能发现错误,却发现不了存心隐瞒的代码(见上文)。DuckDB 运行了 0 条没有决策记录的查询,另有 0 条记录没有对应的查询。

删除对应的两行 25 rows deleted, 0 of 115 files hold the person 和 billing.payments, decided by legal team, card_number erased,来自第 7 章的删除,内容与日志所记录的一致:请求的 ID、涉及的表、计数、每张按例外保留的表及 governance/retention.toml 给出的理由,以及扫描的结果;其中绝不含学习者的 ID 或取值,因此该记录可以安全地纳入链中。欧洲数据保护委员会建议控制者“核实删除已经执行,并能够证明这一删除”(EDPB,2026);这一行记录的是平台自身的扫描所发现的情况。报告还写下了链头,位于记录 14。把它保存在另一个系统中,它就成为下一份报告核对链所依据的锚点:此前任何记录被修改或删除,届时都会显现出来;而之后的记录,只有在后续的链头覆盖它之后才受到保护。

报告不对任何法律或框架作出断言。它只陈述检查了什么、依据哪一版策略、来自哪些输入,以及结果如何。

工作准则: 按计划并在每次策略变更时,用代码根据记录生成证据,并给每个部分加盖戳记,标明策略版本、其输入的摘要,以及重新计算所需的条件,同时说明哪些部分无人能重新计算。把报告保存在日志写入方无法更改的地方,纳入未通过的发现而不是将其过滤掉,并让每份报告对照上一份所写下的链头来检查链。凡能生成报告之处,绝不以截图代替;第 10 章会在每个拉取请求上运行此类检查。

生产环境中会出什么问题

最近一次修改见于版次 2026.11.27

审计轨迹不再完整、不再真实或无人查阅时,并不会有任何东西随之崩溃。最要紧的失败,是被审计的代码能够自行关闭日志,而 DuckDB 的查询日志正属此类:它属于被其记录的进程。两个强制执行层面对同样的攻击。第一个层没有锁定其配置,任何不带 lib.audit 构建的层都是如此;持有其连接的代码先尝试把日志写入文件,再关闭日志,然后绕过该层读取某位学习者的电子邮件。第二个是 lib.audit 的层,已锁定;此类代码先尝试关闭日志,再绕过该层读取,最后清空日志:

query-log-switched-off.py Python 3.12.15 · duckdb 2.0.0 · pyarrow 26.0.1 · pyiceberg 0.12.0 · regopy 1.5.2 · sqlglot 30.33.0 通过
"""Code holding a layer's connection tries to switch DuckDB's query log off, first on a layer that doesn't lock itsconfiguration and then on one that does. The printed lines show the switch working on the first, failing on the second,and the gap that emptying the log leaves in the second layer's check."""import duckdbfrom lib import auditfrom lib.catalog import open_catalogfrom lib.platform import scratchAROUND = "select email from crm.learners where learner_id = 541"       # a read around the enforcement layerfolder = scratch()# A layer that doesn't lock its configuration: code holding its connection tries a file log, then switches logging off.unlocked = audit.Audited(open_catalog(["crm.learners"]), audit.Log(folder / "unlocked.jsonl"), lock=False)connection = unlocked.enforcer.connectiontry:    connection.execute("set log_query_path = 'queries.log'")       # the layer's connection refuses to write a fileexcept duckdb.Error as error:    print("a query log in a file, on the layer's connection:", type(error).__name__)connection.execute("CALL disable_logging()")                       # nothing stops the switch hereconnection.sql(AROUND).fetchall()                                  # so this read leaves nothing in DuckDB's logfor query in unlocked.check_engine_log()["unrecorded"]:            # only the statements made before the switch remain    print("unlocked, unrecorded:", query)# lib.audit's layer locks its configuration: switching the log off is refused, but emptying it isn't.log = audit.Log(folder / "locked.jsonl")locked = audit.Audited(open_catalog(["crm.learners"]), log)locked.sql("select learner_id, email from crm.learners", "analyst-1").fetchall()     # its entry in DuckDB's log is emptied belowconnection = locked.enforcer.connectiontry:    connection.execute("CALL disable_logging()")except duckdb.Error as error:    print("locked, disable_logging():", type(error).__name__)connection.sql(AROUND).fetchall()connection.execute("CALL truncate_duckdb_logs()")                  # emptying isn't locked, so the log loses its entriesfound = locked.check_engine_log()print("locked, after truncate_duckdb_logs(), queries with no decision record:", len(found["unrecorded"]))print("locked, decision records DuckDB has no query for:", len(found["not in the engine log"]))
输出python query-log-switched-off.py
a query log in a file, on the layer's connection: PermissionException
unlocked, unrecorded: CALL disable_logging()
unlocked, unrecorded: SET log_query_path = ?
locked, disable_logging(): InvalidInputException
locked, after truncate_duckdb_logs(), queries with no decision record: 0
locked, decision records DuckDB has no query for: 1

在未锁定的层上,disable_logging() 可以成功,随后的读取不留任何痕迹。交叉核对只能找到静默之前的语句:被拒绝的 SET log_query_path,以及 CALL disable_logging() 本身。这次拒绝表现为 PermissionException,因为第 6 章禁止该层的连接访问文件。

在已锁定的层上,关闭日志会失败,而清空日志不会。关闭操作以 InvalidInputException 失败,但 truncate_duckdb_logs() 可以成功,事后核对发现有 0 条查询未被记录。反方向的核对则注意到了这一缺口:1 条决策记录在 DuckDB 的日志中没有对应的查询,说明有东西清空了日志。只有当某条记录落在被清空的区间内,它才会察觉;若代码用 write_log 把条目写回,日志看起来仍然是完整的。

以上做法都无法阻止持有该连接的代码。在生产环境中,应在作为独立服务运行的引擎中执行强制策略,例如搭配 OPA 的 Trino,使其日志写在查询者所在进程之外。按控制项 AU-9(2) 的要求,把审计记录存放在另一个系统上:“Store audit records [Assignment: frequency] in a repository that is part of a physically different system or system component than the system or component being audited”(NIST SP 800-53 Rev. 5)。我们建议每条记录一经写入就发送出去,并把链的头部另行保存在别处。

审计轨迹还有三种出错方式:

  • 无人查阅的日志。 NIST 的日志管理指南说明了查阅日志的理由:例行审查有助于在安全事件、违反策略的行为、欺诈活动和运行问题发生后不久就将其识别出来(§2.2,NIST SP 800-92)。只写不读的日志耗费存储,却不提供任何保护。应安排访问审查和证据报告,为每项发现指定负责人,并对等不到一个季度的情况发出告警,例如用户因查询被拒而被标记。
  • 策略变更后无法解释的决策。 缺少策略修订版本的记录,或没有存档策略包的修订版本,会在当前策略下重放,并与自身相矛盾。应把数据文档与规则一起生成指纹,因为数据文档的变更对决策的影响与规则的变更同样确定。
  • 含有个人数据的日志。 个人数据通过多种途径进入日志:查询中的字面量、引擎自身的查询日志、打印出值的错误消息(第 6 章已就 DuckDB 的类型转换错误说明过这一点),以及该层的调试输出。解析器的警告是另一条途径:sqlglot 在将语句保留为文本时发出的警告会引用该语句(sqlglot 源码,v30.24.0),因此 lib.audit 会在解析用户查询时让 sqlglot 保持静默。审计日志只保留 ID 和摘要,并为其他每种日志设定删除操作能够触及的保留期限。
  • 不完整的决策日志。 OPA 可以按规则丢弃决策事件,方法是通过 system.log.drop,而且“OPA will drop events if the rate limit is exceeded”(OPA,决策日志)。应像交叉核对那样,用引擎的查询数量与决策记录比对,而不要假定每项决策都已记录。
  • 与日志放在一起的头部。 锚点若存放在日志写入方可以修改的地方,则检测不到链本身检测不到的任何东西。应把它放在另一个系统中,并使用不同的凭据。

本章的决定

最近一次修改见于版次 2026.11.27
  • 每次查询一条记录,由执行层在查询运行之前写入。 lib.audit.Audited包装了第 6 章的Enforcer,但不改动它,并且自行获取每个结果。记录涵盖 AU-3 的六个项目,其中包括发出该查询的主体和助手。
  • 记录只保存 ID,从不保存值。 无论采用 DuckDB 的哪种语法,每个字面量,以及目录中没有的每个名称,都会在查询和拒绝原因中被替换,注释则被删除。DuckDB 实际运行的 SQL 以 HMAC-SHA256 摘要的形式保存,密钥只由该层持有,因为有了密钥,摘要就可以用来验证猜测。删除操作按请求 ID 记录。
  • 每条记录都写明其策略修订版本, 即策略的各模块和数据文档的 SHA-256,并且每个修订版本都存档以供重放。
  • 记录以 SHA-256 链接并编号, 而这条链只保护到最近一次在该层控制之外记下的链头为止的记录。每份证据报告都会记下链头,我们建议更频繁地这样做。
  • DuckDB 的查询日志是用于排查错误的交叉核对,而不是审计日志。 该层会开启它,锁定其配置,将它与自上次检查以来的记录双向核对,然后清空它。持有该连接的代码可以使它失效。
  • 访问审查和证据报告由系统生成, 依据策略、日志和目录,每一节都标明其策略修订版本和输入。标记被拒绝查询的阈值为3,它是governance/audit.toml中的经验值。审计轨迹的可靠程度,取决于被审计的代码无法改动的部分,而本章的检查会说明代码能够改动系统的哪些部分。真正重要的记录,是被审计的代码无法关闭的那一条。

本章,在本版次中

版次 2026.11.31示例于 2026年11月19日 通过本章 9个示例 个示例中有 9 个通过

译自英文版图书,版次 2026.11.31。

引用本节

格式

每种格式均引用英文原书,本译本以其为准。

每种格式都会注明该版次。其链接会打开当前版次,节标题处会说明该节文本最后一次修改的时间。访问日期和检索日期即为您打开此页的当天。

本页内容

第 8 章,共 10 章: 审计与证据

  1. 在决策发生之处记录每一项决策
  2. 不要把值写进记录
  3. 在旧决策自身的策略下重放
  4. 将记录链接起来,使篡改可见
  5. 将日志与 DuckDB 的查询日志交叉核对
  6. Generate the access review from the log
  7. 以数据形式产出证据
  8. 生产环境中会出什么问题
  9. 本章的决定

返回顶部全部章节