稽核與證據
閱讀約需 97 分鐘 分鐘,9 節 中共有 9 個範例。
讀完本章後,你將能為每一項存取決策與查詢建立一份稽核日誌,日誌中不含任何值,並以雜湊鏈串接,使任何編輯都會被察覺。你會在做出決策的地方寫入日誌,並將日誌的頭(最後一筆紀錄的編號與雜湊值)記錄在日誌寫入者無法更動的位置,如此一來,只要在該頭之前的任何一筆紀錄被編輯或移除,都會顯現出來。你也會學到如何在政策變更之後解釋舊的決策、將日誌與查詢引擎自身對其執行內容的紀錄交叉比對,並依據日誌與政策產生存取檢閱與證據報告,這些都以任何人皆可重新執行的程式碼完成。
本書所治理的線上學習平台,其客服主管若詢問為何上週有分析師能讀取評論文字,或稽核人員要求平台證明其存取規則確實有效,目前沒有人能憑紀錄回答。第 5 章以 Rego 撰寫了平台的存取政策,第 6 章的強制執行層 lib.enforce.Enforcer 會針對查詢讀取的每一張資料表向政策請求決策,並改寫查詢,使 DuckDB 只回傳決策所允許的內容。這一層在套用每項決策之後便將其遺忘。顯而易見的補救做法是記錄每個查詢的文字,但這會為政策所保護的內容製造第二份副本:客服人員的查詢可能在 WHERE 子句中帶有學習者的電子郵件地址,而第 7 章必須將學習者的資料從其流向的所有地方清除。
本章使用的術語如下,與本書其他部分一致:稽核日誌(audit log)是記錄誰在何時做了什麼、結果如何的紀錄,保存下來以便日後有人檢查。平台的稽核日誌為每個查詢保存一筆決策紀錄。政策修訂版本(policy revision)是決策除了請求之外,所依賴的一切內容的指紋。當日誌遭到更動時能夠被偵測出來(但無法被阻止),這份日誌即為可偵測竄改(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。
此部分尚未翻譯,因此以英文呈現。
Record every decision where it's made
An audit record is only as complete as what its writer knows. The query engine knows the SQL it ran, but not who asked or why. The policy knows its decision, but not the query. Only the enforcement layer holds all of it at once: the principal, meaning the user or program that asks, the tables and columns the query reads, each table's decision, and the SQL it hands the engine. That is where Open Policy Agent (OPA) writes its own record. OPA's decision logs hold events that describe policy queries, with the policy queried, its input, the bundle metadata, and other information that makes auditing and offline debugging possible (OPA, decision logs). Each event carries a decision_id, the path of the rule queried, the input, the result, a timestamp, and bundles[_].revision, the bundle's revision at evaluation. regopy, which runs the policy inside Python, returns the value of the query and nothing more, so the layer has to build every other field itself.
NIST's catalog of security controls gives a checklist for those fields. Control AU-3 asks that audit records establish six things: (a) what type of event occurred, (b) when it occurred, (c) where, (d) its source, (e) its outcome, and (f) the identity of the individuals, subjects or objects associated with it (NIST SP 800-53 Rev. 5).
lib.audit.Audited takes a catalog and a Log, the audit log's file. Its sql method takes the same arguments as Enforcer.sql, plus via, the assistant that sent it, when one did. It plans the query, asks the policy about every table the query reads, writes one record, and only then hands the rewritten query to DuckDB. So no query reaches DuckDB through the layer without a record, and when the record can't be written, the query doesn't run. Chapter 6 left this chapter one case: when the course assistant answers instructor-17, the query runs as the instructor, and both identities belong in the log. The assistant's query for instructor-17, and its attempt to put names to the ratings, each leave a record, showing what a record holds when two identities are involved:
1"""The course assistant's two queries, sent as instructor-17: one allowed, one denied, each recorded with both identities.2The point rests on the `user` and `via` lines of both records and on the denial in record 2."""3from lib import audit4from lib.catalog import open_catalog5from lib.enforce import Denied6from lib.platform import scratch78# Chapter 6's enforcement layer, wrapped so that every query it's given is recorded before DuckDB runs anything.9log = audit.Log(scratch() / "audit.jsonl")10platform = audit.Audited(open_catalog(["crm.learners", "learning.reviews"]), log)1112# instructor-17 asks the course assistant how their reviews look. The assistant writes the SQL; the query runs as the13# instructor, with the assistant recorded as the one that sent it.14question = "select course_id, count(*) as reviews, round(avg(rating), 2) as rating from learning.reviews group by all"15platform.sql(question, user="instructor-17", via="assistant-1").fetchall()1617# Then the assistant tries to put names to the ratings, which the instructor may not see. The denial is recorded before18# it's raised, so the script only needs to go on.19try:20 platform.sql("select l.full_name, r.rating from learning.reviews as r join crm.learners as l using (learner_id)",21 user="instructor-17", via="assistant-1").fetchall()22except Denied:23 pass2425for record in log.records():26 print(f"record {record['seq']}:")27 print(audit.show(record))
python decision-record.pyrecord 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...
Record 1 covers AU-3's six items. event is the type, and time says when, in UTC with a Z, since control AU-8 asks for time stamps that "use Coordinated Universal Time" or state their offset; the examples take it from a fixed clock. catalog says where. user and via are the source: the query ran as instructor-17, and assistant-1 sent it. With the decision's table, they are the identities AU-3's item f asks for. query and the decision's columns say what was read, and outcome and the decision's allow, row_filters, column_masks and reasons say how it ended, down to the course IDs the row filter kept. The next sections explain policy_revision, rewritten_digest and prev_hash.
Record 2 is a denial, and it holds both tables' decisions: the join may read learning.reviews but not crm.learners, and one denied table denies the query. Without via, the log would say that the instructor tried to read learners' names. With it, a reviewer knows the assistant wrote that query, and looks at the assistant's behaviour as well as the instructor's.
Working rule: Write the audit record in the enforcement layer, where the principal, the query and the decision meet, and write it before the query runs, so that a failed write stops the query. Record every identity involved, the person and any assistant acting for them, and every table's decision with its filters, masks and reasons. When an engine enforces the policy itself, as the SQL engine Trino does through OPA, turn on OPA's decision log instead, and make sure its input names every identity involved: Trino's opa.identity.extra-credentials-keys, for example, forwards the credentials it lists "to OPA as extraCredentials in the identity context" (Trino 484, OPA access control).
此部分尚未翻譯,因此以英文呈現。
Keep values out of the record
The decision record holds IDs, names of tables and columns, decisions and digests, and no value from the data its query read, because everything an audit log holds becomes data to protect. NIST's discussion of AU-3 warns that the audit trail can reveal personally identifiable information, especially when it records inputs, and its enhancement AU-3(3) asks systems to limit that information in audit records to the elements the privacy risk assessment names (NIST SP 800-53 Rev. 5). Limit, not remove: AU-3's item f asks for the principal, so every record names one, such as instructor-17, one instructor's account. The GDPR defines personal data as "any information relating to an identified or identifiable natural person" (GDPR, Article 4(1)), and a person is identifiable directly or indirectly, in particular through an identifier such as a name or an online identifier. An account ID like this one can be such an identifier. The platform has a sharper reason to keep learners out: chapter 7 erases a learner from every table their data reached, and erasing one record from the chain this chapter adds can't be told from tampering.
The risk is the query text. A support agent who looks a learner up by the email in a ticket writes that email into the query. lib.audit.normalized keeps the query's shape and drops its values: sqlglot parses the query, and every literal, in any of DuckDB's syntaxes, becomes ?. So does every name the catalog doesn't hold, such as a value typed in double quotes, except that an alias or a CTE's name the query defines becomes alias_1, alias_2 and so on. Comments, which can hold anything, are left out, and a denial's reason is recorded with the same values taken out. The SQL the layer hands DuckDB is kept only as rewritten_digest, an HMAC-SHA256 under a key only the layer holds, which the cross-check matches against DuckDB's own log. OPA starts from the whole input instead, and can be told to drop what a policy names: "queries the data.system.log.mask path prior to encoding and uploading decision logs" (OPA, decision logs). The platform's layer never writes the values at all.
Here agent-a05 looks up an EMEA learner by email, then by ID, while analyst-1 reads learners' emails, which the policy pseudonymizes. Neither value is printed; the lines below say where each is kept:
1"""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.2The printed lines the prose rests on are the asserts about the email and the four count lines at the end."""3from lib import audit4from lib.catalog import open_catalog5from lib.platform import load, scratch67log = audit.Log(scratch() / "audit.jsonl")8platform = audit.Audited(open_catalog(["crm.learners"]), log)910# agent-a05 looks up an EMEA learner by the email in a ticket, then by their ID. Neither value is ever printed.11learners = load("crm.learners").to_pylist()12learner = next(row for row in learners if row["support_region"] == "emea") # any EMEA learner will do, since the example needs only one13email, learner_id = learner["email"], learner["learner_id"]14platform.sql(f"select learner_id, full_name from crm.learners where email = '{email}'", "agent-a05").fetchall()15platform.sql(f"select email, phone from crm.learners where learner_id = {learner_id}", "agent-a05").fetchall()16platform.sql("select learner_id, email from crm.learners", "analyst-1").fetchall() # pseudonymized with the key1718for record in log.records():19 print("query in the record:", record["query"])20engine = audit.engine_queries(platform.enforcer.connection) # DuckDB's own query log, literals and all21print("the email, in the audit log:", email in log.path.read_text())22assert email not in log.path.read_text() # the record keeps `?` where the email was, so nothing matches23print("the email, in DuckDB's query log:", any(email in query for query in engine))24assert any(email in query for query in engine) # DuckDB logs the SQL text it runs, literals included25print("the pseudonymization key, in DuckDB's query log:", any(platform.enforcer.key in query for query in engine))26assert not any(platform.enforcer.key in query for query in engine)27print("its placeholder, in DuckDB's query log:", any("$pseudonym_key" in query for query in engine))28assert any("$pseudonym_key" in query for query in engine)2930# Guessing which learner the agent looked up, from a list of the platform's emails: rebuild the text DuckDB ran with31# each email, digest it, and compare. First with a plain SHA-256 of the query, as a log might keep, then with the32# record's keyed digest, by someone without the layer's key and by someone who holds it.33ran = next(query for query in engine if email in query)34guesses = [ran.replace(email, row["email"]) for row in learners] # the same text, with each learner's email in its place35logged = log.records()[0]["rewritten_digest"] # record 1 is the agent's lookup by email36print("emails tried:", f"{len(guesses):,}")37print("emails a plain SHA-256 of the query confirms:", sum(audit.sha256(g) == audit.sha256(ran) for g in guesses))38print("emails the keyed digest confirms, without the layer's key:",39 sum(audit.digest(g, "a-guessed-key") == logged for g in guesses)) # a wrong key, the only kind a guesser has40print("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.pyquery 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
The records keep WHERE email = ? and WHERE learner_id = ?, and a search of the log for the email finds nothing. DuckDB's query log does hold the email, because it records the SQL text DuckDB runs, literals included. The pseudonymization key isn't there: chapter 6 passes the key as a parameter, so DuckDB's log shows the placeholder $pseudonym_key where the key would be.
A keyed digest of a query that held an email is a pseudonym of that email, and the key is what keeps it one, as chapter 6 showed for the platform's pseudonyms. The rewrite is code in the repository, so anyone with a list of emails, from an old export or a breach, can rebuild the text DuckDB ran for each and test it. Of 5,000 emails tried, a plain SHA-256 of that text confirms 1: the learner the agent looked up. The record's keyed digest confirms 0 without the layer's key, and 1 with it. The key stays with the layer, away from anyone who reads the log, and so does the cross-check, which needs it.
DuckDB takes a value in more ways than a quoted string, and a name can hold one too. A regression test for normalized types the same learner's email, name and ID into agent-a05's queries in each of those ways. Those requests leave these traces, one record each:
1"""Eleven places to type a learner's values into a query, and what each record keeps of it: the query's shape, with a2`?` for each value and each name the catalog doesn't hold, and a numbered alias for each alias or CTE the query defines.3The printed count of records that hold the email, the name or the ID4is the line the prose rests on."""5import json6import re78from lib import audit9from lib.catalog import open_catalog10from lib.enforce import Denied11from lib.platform import load, scratch1213log = audit.Log(scratch() / "audit.jsonl")14platform = audit.Audited(open_catalog(["crm.learners"]), log)1516# An EMEA learner's email, name and ID, typed into agent-a05's queries as values and as names. None is ever printed.17learner = next(row for row in load("crm.learners").to_pylist() if row["support_region"] == "emea")18email, name, learner_id = learner["email"], learner["full_name"], learner["learner_id"]19cases = { # each query puts the same values in one more way20 "a string": f"select learner_id from crm.learners where email = '{email}'",21 "a dollar-quoted string": f"select learner_id from crm.learners where email = $${email}$$",22 "an escape string": f"select learner_id from crm.learners where email = e'{email}'",23 "a list": f"select learner_id from crm.learners where email in ['{email}']",24 "a number": f"select email from crm.learners where learner_id = {learner_id}",25 "a comment": f"select email from crm.learners where learner_id = {learner_id} -- {name}",26 "a name in double quotes": f'select learner_id from crm.learners where full_name = "{name}"', # a column, to DuckDB27 "a column alias": f'select learner_id as "{email}" from crm.learners',28 "a table alias": f'select "{name}".email from crm.learners as "{name}"',29 "a CTE's name": f'with "{email}" as (select learner_id from crm.learners) select learner_id from "{email}"',30 "a table the catalog doesn't hold": f'select learner_id from "{email}"',31}3233for query in cases.values():34 try:35 platform.sql(query, "agent-a05").fetchall() # a denial is recorded, then raised: the script carries on36 except Denied:37 pass3839# What each record keeps of its query. The email or the name anywhere in its line, or the ID in what it keeps, is a40# value that got through, and is never printed.41held = []42for case, line in zip(cases, log.lines()): # one record per query, in the order they ran43 record = json.loads(line)44 kept = record["query"] + (f" (denied: {record['denial']})" if record["denial"] else "")45 held.append(email in line or name in line or bool(re.search(rf"\b{learner_id}\b", kept))) # the ID as a whole word46 print(f"{case}:", "(holds a value)" if held[-1] else kept)47print("records holding the email, the name or the ID:", sum(held), "of", len(cases))
python query-shape-hard-cases.pya 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
Each record keeps the query's shape and none of its values, allowed or denied alike. The last line counts the records that hold the email, the name or the ID: 0 of 11.
Two things are lost. The normalized text drops harmless values and names too: in the first record, ROUND(AVG(rating), 2) became ROUND(AVG(rating), ?), and in the second, the aliases r and l became alias_2 and alias_1. And the log can't answer "who looked up this learner?". When the platform needs that answer, for a learner who asks who has read their data, keep it in a separate access log, with learner IDs, a retention period and no chain, which chapter 7's erasure reaches like any other table.
Working rule: Record a query's shape, never its values: replace every literal, in every syntax the engine accepts, and every name the catalog doesn't hold, drop comments, take the same values out of denial reasons, and keep the executed SQL only as a keyed digest whose key the log's readers never get. Never use a plain hash of text that may hold personal data. When you need to know who read one person's data, keep that in a separate log that retention and erasure reach, not in the tamper-evident one.
此部分尚未翻譯,因此以英文呈現。
Replay an old decision under its own policy
A decision record holds the request, but a decision depends on three things: the request, the policy's rules, and the data the policy reads. The last two change all the time. The platform's policy reads governance/users.toml, governance/metadata.toml and learning.courses, as chapter 5 set up, so a support agent moved to another region, or a course added to learning.courses, changes decisions without anyone touching Rego. If a record says nothing about what the policy was, its decision can't be explained once either changes: replayed later, it gives a different answer, and the log looks wrong. OPA records the policy's version as bundles[_].revision. For policies that call non-deterministic built-ins, such as the time or an HTTP request, OPA can also log their results in nd_builtin_cache, which exists for debugging and decision replay (OPA, decision logs). The platform's policy calls none.
lib.audit.bundle puts together the policy's modules and its data document, the facts the policy reads, generated from those three sources, as OPA ships them in a bundle. lib.audit.revision is the SHA-256 of that bundle: a fingerprint that changes when any rule changes, or any fact the policy reads. Every record carries the revision that decided it. lib.audit.Archive keeps every bundle the layer has used, under its revision, so a decision can be made again under the policy that made it. A bundle holds rules and the attributes of the platform's users, not data about learners, so the archive can be kept as long as the log.
On the platform, analyst-1 reads learners' emails and gets pseudonyms. Suppose a later pull request adds it to support for EMEA, the change chapter 6 caught with its who-sees-what test. The old record is replayed under each revision:
1"""Replays a logged decision under the revision it was made with and under the current one, after a change to the users2file alone. The printed email and match lines are the point: only the archived revision explains the record."""3from lib import audit, policy4from lib.catalog import open_catalog5from lib.platform import scratch67folder = scratch()8archive = audit.Archive(folder / "bundles") # every policy revision that has decided anything, by revision9log = audit.Log(folder / "audit.jsonl")1011# analyst-1 reads learners' emails, which the policy pseudonymizes.12platform = audit.Audited(open_catalog(["crm.learners"]), log, archive=archive)13platform.sql("select learner_id, email from crm.learners", "analyst-1").fetchall()14record = log.records()[0]1516# Later, a pull request adds analyst-1 to support for EMEA, as in chapter 6. Only users.toml changes, which reaches17# the policy through its data document, and no Rego.18data = policy.data_document()19data["platform"]["users"]["analyst-1"] = {"groups": ["data-science", "support"], "region": "emea"}20current, logged = audit.bundle(data=data), archive.get(record["policy_revision"]) # the bundle the record was decided under21print("logged under revision:", record["policy_revision"][:12])22print("current revision:", audit.revision(current)[:12])23modules_changed = current["modules"] != logged["modules"]24assert not modules_changed # no Rego changed, so the policy modules are the same25print("policy modules changed:", modules_changed)26data_changed = current["data"] != logged["data"]27assert data_changed # the data document is the only part that changed28print("data document changed:", data_changed)2930# Explain the logged decision by making it again, from the request the record holds, under each revision.31print("email, as logged:", record["decisions"][0]["column_masks"].get("email", "shown as stored"))32for name, revision in [("the current revision", current), ("the logged revision", logged)]:33 again = audit.replay(record, revision)34 matches = again == record["decisions"]35 assert matches == (revision is logged) # only the revision the record was decided under explains it36 print(f"under {name}, email:", again[0]["column_masks"].get("email", "shown as stored"))37 print(f"under {name}, the decision matches the log:", matches)
python explain-old-decision.pylogged 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
The revision changed, from f761e4ad08d4 to 0350e789b34d, though no Rego did. The policy modules are the same, and the data document, which now lists the new membership, is not. A fingerprint of the policy files alone would have missed the change. Replayed under the current revision, the decision doesn't match the log: email is shown as stored, because support sees contact details. An investigator holding only the current policy would conclude the log, or the layer, was wrong. Replayed under the logged revision from the archive, email is pseudonymize again, and the decision matches the log.
Replay explains the decision, not the rows. A row filter is replayed as a rule, such as support's region or the consent filter learner_consented, and which rows passed it depended on that day's data, which may have changed since, or been erased. That split is by design: the log says what the rule was, and the tables, with their own snapshots and retention, say what it matched.
Working rule: Fingerprint every decision with a hash of everything it depended on besides the request: the policy's modules and its data document, together. Archive each revision for as long as you keep the log, and explain an old decision by replaying it under its own revision, never under the current one. If a policy reads the clock or calls another service, log those results too, as OPA's nd_builtin_cache does.
此部分尚未翻譯,因此以英文呈現。
Chain the records so an edit shows
The platform's decision records are evidence only if a change to them can be detected. SP 800-53 asks systems to "Protect audit information and audit logging tools from unauthorized access, modification, and deletion" (AU-9), and, in AU-9(3), to "Implement cryptographic mechanisms to protect the integrity of audit information and audit tools". Its AU-9(3) discussion offers, as an example, "signed hash functions using asymmetric cryptography" (NIST SP 800-53 Rev. 5), which only the holder of a private key can make. The platform's chain, below, is not that, and protects nothing on its own. NIST's guide to log management, SP 800-92, describes the plainest mechanism: a message digest for each log file, stored securely, so that a change to an archived log is detected (NIST SP 800-92). The guide dates from 2006. It names MD5 and SHA-1 as the common digests of its day, and its own footnote 25 advises SHA-256, which the platform uses.
A digest per file detects a change to a closed file. A hash chain detects a change to a log that's still growing: every record carries a sequence number, seq, and the SHA-256 of the line before it, prev_hash, so changing a record breaks the link to the next one, and removing one leaves a gap. lib.audit.check walks the chain and reports each break. A chain is tamper-evident, not tamper-proof. Schneier and Kelsey designed a log in which an attacker who takes over the logging machine can neither read nor falsify the entries written before. Even there, the attacker "can delete a block of entries (or the entire log file)", and the trusted party the log reports to notices only the next time the machine contacts it (Schneier and Kelsey, 1999). A plain chain like the platform's is weaker: anyone who can write the file can rebuild it, renumbering as they go. So what it proves depends on a chain's head, the number of records and the hash of the last one, written down where the log's writer can't change it. An edit to, or a removal of, any record up to that head shows, however it's hidden. After the head, a chain rebuilt or cut passes every check, and without a head, so does a chain rebuilt from the start. check takes the head as an anchor.
To test the chain, the example replays lib.audit.DAY, the 13 queries the platform received on Oct 1, and writes the head down at noon, at record 9. Then it tampers with copies of the log in five ways. Before noon, record 3, agent-a05's lookup of a learner's contact details, is handed to another agent, once as a plain edit and once with the chain rebuilt. After noon, record 12, the denied read by someone-else, the user the platform doesn't know, is removed, once plainly and once with the chain rebuilt; and the day's last two records are cut off. Before you run it, predict which of the five the chain finds on its own, and which against the noon head.
1"""Replays the day's queries into a chained log, writes the head down at noon, then tampers with copies of the log five2ways and checks each against the chain alone and against the noon head. The point rests on the two checks of each3change: the chain finds the plain edit and the plain removal on its own, and only the head finds the rebuilt chain."""4import json56from lib import audit7from lib.catalog import open_catalog8from lib.platform import scratch910log = audit.Log(scratch() / "audit.jsonl")11platform = audit.Audited(open_catalog(["crm.learners", "learning.reviews", "billing.payments", "support.tickets"]), log)12audit.run_day(platform, [query for query in audit.DAY if query[0] < "12:00"]) # the morning; times compare as strings13anchor = log.head() # the head, written down at noon somewhere the log's writer can't reach14audit.run_day(platform, [query for query in audit.DAY if query[0] >= "12:00"]) # the afternoon15lines = log.lines()16print("records:", len(lines))17print("head written down at noon, at record:", anchor["records"])18print("record 3:", json.loads(lines[2])["user"], "reads", json.loads(lines[2])["query"]) # record 3 is index 2192021def rechained(lines: list[str]) -> list[str]:22 """The chain built again, every record renumbered and every prev_hash recomputed: what anyone who can rewrite the23 log's file can do."""24 rebuilt, previous = [], audit.GENESIS # the first record points at 64 zeros, not at a record25 for seq, line in enumerate(lines, start=1):26 rebuilt.append(audit.canonical(json.loads(line) | {"seq": seq, "prev_hash": previous}))27 previous = audit.sha256(rebuilt[-1])28 return rebuilt293031# Before noon, record 3 handed to another agent. After noon, record 12 (someone-else's denied read) removed, and the32# day's last two records cut off.33edited = lines[:2] + [audit.canonical(json.loads(lines[2]) | {"user": "agent-a01"})] + lines[3:] # index 2 is record 334removed = lines[:11] + lines[12:] # index 11 is record 1235for name, version in [("untouched", lines), ("record 3 edited", edited), ("record 3 edited, chain rebuilt", rechained(edited)),36 ("record 12 removed", removed), ("record 12 removed, chain rebuilt", rechained(removed)),37 ("last 2 records removed", lines[:-2])]:38 print(f"{name}, checked alone:", "; ".join(audit.check(version)) or "no problem found")39 print(f"{name}, checked against the noon head:", "; ".join(audit.check(version, anchor)) or "no problem found")
python chain-catches-edit.pyrecords: 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
On its own, the chain catches the plain edit, record 3 was changed: record 4 doesn't follow it, and the plain removal, record 12 is missing: record 13 follows record 11, and nothing else. It misses both rebuilt chains, which are consistent from start to end, and the cut, since no record follows the last one to notice.
A head protects only the records written before it. Against the noon head, the edit made before noon still shows with the chain rebuilt, the chain no longer passes through the head written down at record 9. Everything hidden after noon passes: the removal with the chain rebuilt gives no problem found, and the cut gives no problem found.
This is why the rule about values isn't optional. Erasing a learner from a chained log means changing or removing a record, and wherever a check can see that, it can't tell it from tampering. A log that holds no data about learners never meets chapter 7's erasure. The IDs of the people who query are another matter: give the log a retention period, and start a new chain each period, with its last head written down, so a whole period can be deleted when it expires.
Checking a chain means re-reading every record. Crosby and Wallach describe tree-shaped logs that can prove "that individual logged events are still present, and that the log, as seen now, is consistent with how it was seen in the past", with proofs of logarithmic size, an improvement on earlier linear constructions (Crosby and Wallach, 2009). For one platform's own log we recommend a chain checked daily, and a tree when outside parties audit a log too large to re-read.
Working rule: Chain every record to the one before with SHA-256 and a sequence number, and count on the chain to protect only the records up to the last head written down outside the layer's control. Write the head down often, to an append-only store or to another system under other credentials, as often as the number of unprotected records you can accept; if that number is zero, send each record off the machine as it's written. Check the chain against the last head on a schedule. If you need what AU-9(3) describes, have each head signed by a system whose key the layer never holds. Never chain a record that might one day have to be erased for one person; let whole periods expire instead.
以 DuckDB 的查詢日誌交叉核對稽核日誌
平台的稽核日誌會記錄每一筆經過強制執行層的查詢,而且只記錄這些查詢。若有查詢直接在該層的 DuckDB 連線上執行,例如由筆記本的輔助函式或取得該連線的測試所執行,就不會有決策,也不會有紀錄。要察覺這種情況,需要由引擎保存的第二份紀錄。DuckDB 2.0.0 內建了一份,但預設為關閉:其日誌框架有 QueryLog 類型,用來記錄 DuckDB 所執行的查詢,以 CALL enable_logging('QueryLog') 開啟,並從 duckdb_logs 檢視讀取(DuckDB,logging)。另有一項設定 log_query_path,可指定記錄查詢的檔案(DuckDB,configuration),而該層的連線會拒絕這項設定,如談正式環境的一節所示。
DuckDB 的日誌並不是稽核日誌。每個項目包含 SQL 文字、連線、交易與查詢的 ID 以及時間戳記:沒有使用者,也沒有決策。它只看得到改寫後的查詢,而且屬於所記錄的那個行程。它能做的是核對稽核日誌:DuckDB 執行的每一筆查詢,都應該是某筆決策紀錄交給它的 SQL。Audited 會在啟動時開啟 DuckDB 的日誌,並鎖定連線的組態。Audited.check_engine_log 會將 DuckDB 日誌中的每筆查詢,以金鑰雜湊摘要比對自上次檢查以來所寫入紀錄的 rewritten_digest,然後清空 DuckDB 的日誌,因為其中含有查詢的值。關聯物件(relation)的查詢會在「Output 一節的方法讀取該關聯物件時執行,且每次讀取都會再執行一次」(DuckDB,relational API),而縮小範圍後的關聯物件會執行自己的 SQL,這些 SQL 並非任何紀錄交給 DuckDB 的。因此 Audited.sql 會自行取回每個結果一次,再將資料列以關聯物件的形式交回,該物件位於它自己的連線上:無論呼叫端如何處理它,DuckDB 的日誌中每筆允許的查詢都只會出現一次。
第一次檢查會重播當天 13 筆查詢並加以核對。接著有兩筆查詢經過該層,一筆其呼叫端從未取回結果,另一筆則縮小為五列,同時有一個筆記本輔助函式繞過該層,用該層的連線讀取了一位學習者的電子郵件。隨後進行第二次檢查:
1"""DuckDB's query log checked against the audit log: first on the day's queries, then after two more queries through the2layer and a read around it. The point rests on the last check's unrecorded query, the one read that bypassed the layer."""3from lib import audit4from lib.catalog import open_catalog5from lib.platform import scratch67log = audit.Log(scratch() / "audit.jsonl")8platform = audit.Audited(open_catalog(["crm.learners", "learning.reviews", "billing.payments", "support.tickets"]), log)9day = audit.run_day(platform)10print("queries allowed:", day["allowed"])11print("queries denied:", day["denied"])1213# DuckDB's own query log, kept on the layer's connection, against the records written since the last check. The check14# then empties DuckDB's log, which holds the queries' values.15found = platform.check_engine_log()16print("queries DuckDB ran:", found["engine queries"])17print("queries DuckDB ran with no decision record:", len(found["unrecorded"]))1819# Two more queries through the layer: one whose caller never fetches it, and one the caller narrows to five rows.20platform.sql("select learner_id, country from crm.learners", "analyst-1") # the layer fetches it, so DuckDB logs it once21platform.sql("select learner_id, country from crm.learners", "analyst-1").limit(5).fetchall() # narrowed after the layer's fetch22# Then a notebook helper that was handed the layer's connection reads a learner's email with it, around the layer.23platform.enforcer.connection.sql("select email from crm.learners where learner_id = 541").fetchall() # no record for this one24found = platform.check_engine_log()25print("next check, records checked:", " to ".join(map(str, found["records checked"])))26print("next check, queries with no decision record:", len(found["unrecorded"]))27for query in found["unrecorded"]:28 print("unrecorded:", query)29print("next check, decision records DuckDB has no query for:", len(found["not in the engine log"]))
python query-log-cross-check.pyqueries 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,它在該層的連線上讀取儲存的資料表,背後沒有任何使用者。請為這類查詢建立專屬的紀錄,並以該層作為使用者,而不是在檢查中設置例外。
實用原則: 凡是強制執行政策之處,都要開啟引擎自己的查詢日誌,在該層內取回每個結果,並依排程將日誌與自上次檢查以來所寫入的紀錄雙向核對:引擎執行的每一筆查詢都必須對應一筆紀錄,每一筆允許的紀錄也都必須對應一筆查詢。然後清空日誌,因為其中保存了查詢的值。將不一致視為需要說明的事件,而不是要濾掉的雜訊。這項檢查可用來抓出錯誤,而不是持有連線並蓄意隱藏的程式碼:對付後者,只有寫在行程之外的日誌才有用。切勿把引擎的日誌當作稽核日誌:它沒有使用者,也沒有決策。
從日誌產生存取審查
平台的稽核日誌記錄了每個人讀取了什麼,而政策則規定每個人可以讀取什麼;存取審查就是把兩者並排比對。SP 800-53 要求組織審查帳號是否符合帳號管理要求(AC-2 j),並以既定頻率審查指派給角色與類別的權限,以確認其必要性,必要時重新指派或移除(AC-6(7))(NIST SP 800-53 Rev. 5)。一份列出誰擁有哪個角色的清單,兩者都無法回答,因為它完全沒有說明需求。程式碼可以算出審查所需的事實:政策允許每個人做什麼、日誌顯示他們實際用了什麼、他們被拒絕了什麼,以及指向不存在對象的成員關係。是否移除存取權,仍然是資料擁有者的決定。
lib.audit.review 會針對 users.toml 中的每位使用者,依平台政策對每個資料表進行評估,並讀取日誌,為每位使用者列出:可讀取的資料表、實際讀取的資料表、未使用的授權、被拒絕的查詢及發出這些查詢的助理,以及任何需要有人查看的項目。為了讓它有過期的內容可找,範例在使用者中加入 analyst-2,該使用者仍列在 bi 中,而這個群組已不存在於政策裡,群組被撤除而沒有人修改使用者檔案時,就會發生這種情況:
1"""An access review of every user in the users file, and every user the log names, against the policy and the day's log,2with a stale group membership added to find. The printed `look at` lines and the grants-not-used count are what the prose3rests on."""4from lib import audit, policy5from lib.catalog import open_catalog6from lib.platform import TABLES, scratch78log = audit.Log(scratch() / "audit.jsonl")9platform = audit.Audited(open_catalog(["crm.learners", "learning.reviews", "billing.payments", "support.tickets"]), log)10audit.run_day(platform) # the day's queries, so that the review has a log to read1112# users.toml as the review finds it: analyst-2 is still listed in bi, a group the policy no longer has.13data = policy.data_document()14data["platform"]["users"]["analyst-2"] = {"groups": ["bi"]}15rows = audit.review(audit.bundle(data=data), log.records(), TABLES) # all eight of the platform's tables1617for row in rows:18 print(f"{row['user']}: may read {len(row['may read'])}, read {len(row['read'])}, denied {row['denied']}"19 + (f" (sent by {', '.join(row['denied via'])})" if row["denied via"] else ""))20 if row["unused"]: # a grant unused: a table the policy lets the user read, and the log never shows them reading21 print(" not used:", ", ".join(row["unused"]))22 for finding in row["findings"]:23 print(" look at:", finding)24print("users reviewed:", len(rows))25print("grants not used:", sum(len(row["unused"]) for row in rows))26print("users to look at:", sum(bool(row["findings"]) for row in rows)) # any finding at all needs a person27print("denied queries flagged at:", audit.settings()["review"]["denied_queries_flagged"]) # from governance/audit.toml
python access-review.pyagent-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
在 lib.audit.DAY 重播的 10 月 1 日當天,有 14 項授權未被使用,而 agent-a01 與 agent-a09 完全沒有讀取任何資料。一天的時間太短,不足以據以移除任何人的存取權,因為客服是輪班工作,財務部門也只在月結時才讀取某些資料表,所以這些數字只是線索,而不是決定。若某項授權在整個審查期間(例如一季)都沒有被使用,才應提交給該資料表的擁有者處理。有 3 位使用者需要有人查看:
analyst-2屬於一個沒有任何規則認得的群組。這項成員關係目前不授予任何權限,但日後只要出現針對bi的規則,就會授予該規則所允許的一切權限,因此應予移除。someone-else不在users.toml中,因此是一個平台不認識的主體試圖讀取學習者的電子郵件。instructor-17的那一行寫的是may read 3, read 1, denied 3 (sent by assistant-1)。在回答講師的第一個問題之後的兩分鐘內,助理先嘗試了學習者資料表,接著是學習者與評論的聯結,然後是客服工單。這是助理在逐一嘗試資料表,而若沒有via,審查就會錯怪講師。
標記的門檻,即 3 次被拒絕的查詢,是經驗法則,連同其理由一併保存在 governance/audit.toml 中,因此變更門檻就和變更政策一樣,是一項需經審查的變更。
實用原則: 存取審查應從政策與稽核日誌產生,而不是從角色成員清單產生:每個人可以讀取什麼對照他們實際讀取了什麼、他們被拒絕的紀錄,以及政策已不認得的成員關係。審查期間要長到涵蓋每項工作的節奏,例如一季,並把每項發現送交相關資料表或群組的擁有者。期間內沒有人使用的授權應予移除,除非其擁有者說明了需要它的理由。
以資料形式產出證據
鏈檢查、交叉比對與存取審查,當團隊以外的人也能查驗時,價值最大。歐盟的《一般資料保護規則》(GDPR)以問責原則將這項責任加諸於組織:「控管者應負責遵守第 1 項,並能證明其遵守(「問責」)」(GDPR, Article 5(2))。怎樣才算證明,是律師要回答的問題;本節說明的是程式碼能產出什麼。NIST 針對帳號管理的評估程序,列出評估人員要檢查的紀錄,其中包括存取授權紀錄、帳號管理合規審查,以及系統稽核紀錄(NIST SP 800-53A Rev. 5, in NIST's OSCAL catalog)。主控台的螢幕截圖不屬於其中任何一種:沒有人能重新執行它、拿它與上一季比較,也無法得知它顯示的是哪一版原則。
lib.audit.evidence 從紀錄與程式碼建立平台的報告,內容分為以下幾節:
- 原則,附上每個模組與資料文件的摘要值;
- 原則的單元測試,來自第 5 章;
- 誰能看到什麼的對照表,並與原則核對;
- 分類涵蓋率,來自第 2 章的中繼資料檢查;
- 稽核日誌的鏈檢查;
- 與 DuckDB 日誌的交叉比對;
- 日誌所記錄的刪除作業;
- 存取審查。
每一節都會註明它是在哪一版原則下計算的、其輸入的摘要值,以及任何人要重新計算所需的一切:封存的套件、測試檔案、metadata.toml 與各資料表的結構描述、稽核日誌。引擎的交叉比對只能部分重新計算,報告也會如此說明。它保存了 DuckDB 所執行每一筆查詢的金鑰摘要值,因此持有日誌的人都能重做其中的兩項計數,但只有該層曾能讀取 DuckDB 的日誌,並且是在清空之前讀取一次。請為 10 月 1 日產出報告,範圍涵蓋目錄中的每一張資料表與管線輸出、第 7 章中於 07:00 記入日誌的某位學習者的刪除作業,以及當天的查詢:
1"""The platform on Oct 1, after an erasure and the day's queries: the first evidence report, written to a file as data, with2each section's result printed. The printed checks, the erasure's lines and the head are what the prose rests on."""3import json4from datetime import timedelta56from lib import audit, retention7from lib.platform import scratch89# The platform on Oct 1: every table and pipeline output, chapter 7's erasure of learner 3590 at 07:00, then the day's10# queries through the audited layer.11catalog, graph = retention.open_platform(statistics_off=True) # statistics off for personal columns: each table rewritten under them, old files swept12log = audit.Log(scratch() / "audit.jsonl")13_, found, done = retention.erase_everywhere(catalog, graph, 3590, "erasure-0001", min_age=timedelta(0)) # zero: no file age guard14audit.record_erasure(log, "erasure-0001", done, retention.scan(catalog, found), at=audit.MIDNIGHT.replace(hour=7))15platform = audit.Audited(catalog, log)16audit.run_day(platform)1718# The first report, so there's no head from an earlier one to check the chain against.19report = audit.evidence(platform, catalog, anchor=None)20reports = scratch() # in production, another system, which the log's writer can't change21(reports / "evidence-2026-10-01.json").write_text(json.dumps(report, indent=2, sort_keys=True), encoding="utf-8")22result = {name: section["result"] for name, section in report.items()}23print("sections:", ", ".join(report))24print("policy tests passed:", f"{result['policy tests']['passed']} of {result['policy tests']['of']}")25print("who-sees-what cases the policy agrees with:", f"{result['who sees what']['agree']} of {result['who sees what']['of']}")26for problem in result["classification coverage"]["problems"]:27 print("classification problem:", problem)28print("audit log records:", result["audit log"]["records"])29print("chain problems, checked alone:", len(result["audit log"]["chain problems"]))30print("queries DuckDB ran with no decision record:", len(result["engine cross-check"]["unrecorded"]))31print("decision records DuckDB has no query for:", len(result["engine cross-check"]["not in the engine log"]))32for erasure in result["erasures"]:33 print(f"erasure {erasure['request']}: {erasure['rows_deleted']} rows deleted, "34 f"{erasure['files_holding_the_person']} of {erasure['files_read']} files hold the person")35 for kept in erasure["kept"]:36 print(f"erasure {erasure['request']}, kept under an exception: {kept['table']}, decided by {kept['decided_by']},",37 f"{', '.join(kept['columns_erased'])} erased")38print("chain head written down, at record:", report["audit log"]["inputs"]["head"]["records"]) # the next report's anchor39print("engine cross-check, computed again from:", "; ".join(report["engine cross-check"]["computed again from"]))40print("engine cross-check, not computed again:", report["engine cross-check"]["not computed again"])
python evidence-report.pysections: 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 或值,因此這筆紀錄可以安全地串入鏈中。歐洲資料保護委員會(European Data Protection Board)建議控管者「確認刪除已執行,並能證明此項刪除」(EDPB, 2026);這一行記錄的是平台自身的掃描所發現的結果。報告也寫下鏈頭,位於第 14 筆紀錄。把它保存在另一個系統中,它就成為下一份報告檢查鏈時所依據的錨點:對它之前任何一筆紀錄的修改或移除,屆時都會顯現出來,而較晚的紀錄要等到後續的鏈頭涵蓋它之後,才受到保護。
報告不對任何法律或框架作出主張。它只陳述檢查了什麼、依據哪一版原則、使用哪些輸入,以及結果如何。
實用原則: 以程式碼從紀錄產生證據,依排程執行,並在每次原則變更時執行,且為每一節蓋上原則版本、其輸入的摘要值,以及重新計算所需條件的戳記,同時註明哪些部分無人能重新計算。將報告保存在日誌寫入者無法更動之處,納入失敗的發現而不是將它們過濾掉,並讓每份報告依上一份所寫下的鏈頭來檢查鏈。能產生報告時,絕不要改送螢幕截圖;第 10 章會在每個 pull request 上執行這類檢查。
正式環境中會出什麼問題
稽核軌跡不再完整、不再真實或無人閱讀時,系統不會有任何故障。最嚴重的失敗,是被稽核的程式碼能自行關閉的記錄機制,而 DuckDB 的查詢日誌正屬此類:它隸屬於它所記錄的行程。兩個強制執行層面對同一種攻擊。第一個沒有鎖定其設定,就像任何未使用 lib.audit 建構的層;持有其連線的程式碼先嘗試把日誌寫到檔案,接著關閉日誌,然後繞過該層讀取學習者的電子郵件。第二個是 lib.audit 的層,已鎖定;這類程式碼嘗試關閉日誌,繞過該層讀取資料,然後清空日誌:
1"""Code holding a layer's connection tries to switch DuckDB's query log off, first on a layer that doesn't lock its2configuration and then on one that does. The printed lines show the switch working on the first, failing on the second,3and the gap that emptying the log leaves in the second layer's check."""4import duckdb56from lib import audit7from lib.catalog import open_catalog8from lib.platform import scratch910AROUND = "select email from crm.learners where learner_id = 541" # a read around the enforcement layer11folder = scratch()1213# A layer that doesn't lock its configuration: code holding its connection tries a file log, then switches logging off.14unlocked = audit.Audited(open_catalog(["crm.learners"]), audit.Log(folder / "unlocked.jsonl"), lock=False)15connection = unlocked.enforcer.connection16try:17 connection.execute("set log_query_path = 'queries.log'") # the layer's connection refuses to write a file18except duckdb.Error as error:19 print("a query log in a file, on the layer's connection:", type(error).__name__)20connection.execute("CALL disable_logging()") # nothing stops the switch here21connection.sql(AROUND).fetchall() # so this read leaves nothing in DuckDB's log22for query in unlocked.check_engine_log()["unrecorded"]: # only the statements made before the switch remain23 print("unlocked, unrecorded:", query)2425# lib.audit's layer locks its configuration: switching the log off is refused, but emptying it isn't.26log = audit.Log(folder / "locked.jsonl")27locked = audit.Audited(open_catalog(["crm.learners"]), log)28locked.sql("select learner_id, email from crm.learners", "analyst-1").fetchall() # its entry in DuckDB's log is emptied below29connection = locked.enforcer.connection30try:31 connection.execute("CALL disable_logging()")32except duckdb.Error as error:33 print("locked, disable_logging():", type(error).__name__)34connection.sql(AROUND).fetchall()35connection.execute("CALL truncate_duckdb_logs()") # emptying isn't locked, so the log loses its entries36found = locked.check_engine_log()37print("locked, after truncate_duckdb_logs(), queries with no decision record:", len(found["unrecorded"]))38print("locked, decision records DuckDB has no query for:", len(found["not in the engine log"]))
python query-log-switched-off.pya 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 source, v30.24.0),因此
lib.audit會在剖析使用者的查詢時讓 sqlglot 靜音。稽核日誌只保留 ID 與摘要值,並為其他每種日誌訂定刪除能涵蓋的保存期限。 - 不完整的決策日誌。 OPA 可以依規則透過
system.log.drop捨棄決策事件,而且「OPA will drop events if the rate limit is exceeded」(OPA, decision logs)。應像交叉比對那樣,把引擎的查詢與決策記錄對照計數,而不是假設每項決策都已記錄。 - 與日誌放在一起的頭端。 若錨點存放在日誌寫入者可以更改的地方,它能偵測到的不會比鏈本身更多。應把它放在另一個系統中,並使用不同的憑證。
本章的決定
- 每筆查詢一筆紀錄,由執行層在查詢執行前寫入。
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。