對查詢強制執行政策

閱讀約需 99 分鐘 分鐘,13 節 中共有 16 個範例。

版次 2026.11.31 範例於 2026年11 月 19 通過測試 108 個範例中有 108 個通過 變更內容

由 AI 翻譯。經人工以英文驗證。 我們如何製作書籍

譯自英文書,版次 2026.11.31。 閱讀原文

讀完本章,你將能對平台執行的每一個查詢強制執行存取決策。你會改寫每個查詢,把它讀取的每一張資料表換成一個子查詢,只保留使用者可以看到的資料列,並遮蔽他們不得查看的欄位;拒絕所有讀取其他內容的查詢;以實際執行的查詢來測試誰能取得什麼;並說明在 duckdb 前面做改寫,會在哪些情況下不再提供任何保護。

只有當政策對每一個送達倉儲的查詢都成立時,它才算保護了線上學習平台的資料倉儲,而這個倉儲裡存放著學習者的聯絡資料、付款紀錄,以及他們撰寫的文字。第 5 章以 Rego 寫出了平台的存取政策。給定使用者、資料表與他們想讀取的欄位,並可附上讀取的理由,政策會回傳一個決策:是否 allow 這次讀取、哪些列篩選條件(row filters)能保留使用者可以看到的資料列、哪些欄位遮罩(column masks)會改變欄位的呈現方式(redact、last4、pseudonymize 或 redact_contacts),以及 reasons(理由)。決策本身不會產生任何作用。客服人員的 SQL 以文字形式送到 duckdb,因此在兩者之間必須有某個元件,把決策套用到該文字所讀取的每一張資料表上,無論查詢是怎麼寫的。

本書的共用模組 lib.enforce 負責執行決策;我們稱之為強制執行層(enforcement layer)。它的 Enforcer 持有一個 duckdb 連線,其中為目錄中的每張資料表(lib.warehouse.connect)各建立一個檢視,並持有這些資料表的結構描述、政策,以及用於假名化的金鑰。使用者永遠拿不到這個連線。Enforcer.sql(query, user) 會改寫使用者的查詢並執行改寫後的結果。服務只公開 lib.enforce.answer,由它呼叫前述方法並回傳資料列:絕不公開關聯物件、連線的控制代碼,或 Enforcer.run,因為後者會執行由強制執行層撰寫、且附帶金鑰的 SQL。本書程式碼使用 sqlglot 30.33.0 與 duckdb 2.0.0。

找出查詢讀取的每一個資料表與欄位

最近一次變動於第 2026.11.27 版

政策只能針對被詢問的內容做出決定,因此這一層必須知道查詢讀取的每一個資料表,以及從各資料表讀取的欄位。從 SQL 文字中判讀這些資訊是個有陷阱的解析問題:*、別名、使用 USING 的連接(join)、共用資料表運算式(common table expression,CTE),以及大小寫不一的識別字。sqlglot 會把查詢解析成一棵樹,而它的 qualify 步驟會依據結構描述(schema)解析每一個欄位,並以其資料表加以限定;若有結構描述可供展開,也會把 * 展開成它所代表的欄位(qualify at v30.24.0)。這一層在啟動時建立該結構描述:列出目錄中的資料表,並從 lib.warehouse.connect 為每個資料表建立的檢視中讀取各自的欄位與型別,因此 DuckDB 為其他用途保存的檢視不會進入結構描述。

以下四個查詢各有一個陷阱,可看出 Enforcer.reads 回傳的內容:也就是政策被詢問的欄位,這些欄位必須與各查詢所指名的相符。

tables-a-query-reads.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 columns four queries read from each table, and how each dialect normalizes the quoted name "LEARNERS". Theprose's point rests on the query with a CTE and on the normalization lines at the end."""import sqlglotfrom sqlglot import expfrom sqlglot.optimizer.normalize_identifiers import normalize_identifiersfrom lib.catalog import open_catalogfrom lib.enforce import Enforcer# only these two tables are loaded, so the layer's schema holds nothing elseplatform = Enforcer(open_catalog(["crm.learners", "support.tickets"]))# What each query reads, found by sqlglot's qualify against the catalog's schema, with `*` expanded.queries = {    "every column": "select * from crm.learners",    "a join": """select l.country, count(*) as tickets                 from crm.learners as l join support.tickets as t using (learner_id) group by all""",    # exclude drops notes from the CTE, so the reads name every other column of crm.learners    "a CTE": """with emea as (select * exclude (notes) from crm.learners where support_region = 'emea')                select email from emea""",    "names in capitals": 'select EMAIL from "CRM"."LEARNERS"',}for name, sql in queries.items():    for table, columns in platform.reads(sql).items():        print(f"{name}, {table}: {', '.join(columns)}")# DuckDB resolves quoted capitals to the lowercase table. Whether sqlglot lowercases them depends on the dialect.capitals = 'select count(*) from "CRM"."LEARNERS"'print('DuckDB, "CRM"."LEARNERS":', f"{platform.connection.sql(capitals).fetchall()[0][0]:,} rows")for dialect in ["duckdb", "trino", "postgres"]:    # the name the dialect's parser resolves the quoted identifier to    table = normalize_identifiers(sqlglot.parse_one('select * from "LEARNERS"', read=dialect), dialect=dialect).find(exp.Table)    print(f'{dialect} normalizes "LEARNERS" to:', table.name)
輸出python tables-a-query-reads.py
every column, crm.learners: ai_training_consent, city, closed_at, country, date_of_birth, email, full_name, learner_id, marketing_consent, notes, phone, signup_at, status, street_address, support_region
a join, crm.learners: country, learner_id
a join, support.tickets: learner_id
a CTE, crm.learners: ai_training_consent, city, closed_at, country, date_of_birth, email, full_name, learner_id, marketing_consent, phone, signup_at, status, street_address, support_region
names in capitals, crm.learners: email
DuckDB, "CRM"."LEARNERS": 5,000 rows
duckdb normalizes "LEARNERS" to: learners
trino normalizes "LEARNERS" to: learners
postgres normalizes "LEARNERS" to: LEARNERS

讀取的內容取決於查詢所指名的對象,而非查詢所回傳的結果。SELECT * 會讀取 crm.learners 的每一個欄位;而連接會從兩個資料表讀取 learner_id,因為 USING 會比較它們,即使兩者都沒有出現在輸出中。CTE 只回傳 email,但它指名了 crm.learners 中除 notes 以外的每一個欄位,所以這一層會詢問所有這些欄位。查詢的讀取範圍可能多於查詢的回傳內容,但絕不會少於它。"CRM"."LEARNERS" 同樣會解析為 crm.learners,因為這一層依照 DuckDB 解析識別字的方式來正規化:DuckDB 對該名稱回傳 5,000 rows(來自 crm.learners)。方言(dialect)很重要。sqlglot 針對 DuckDB 與 Trino 會把 "LEARNERS" 轉為小寫,針對 PostgreSQL 則保留為 LEARNERS,因此這一層以實際執行查詢之引擎的方言進行正規化。

qualify 有一個決定這一層設計的缺口:遇到已知資料表中的未知欄位時它會失敗,但完全不在結構描述中的資料表它卻會略過,並且不展開該資料表的 *。請對一個與 marketing.audience 的連接執行它,該資料表代表在讀取結構描述之後由管線寫入的資料表;接著把該查詢與另外五個讀取查詢送進這一層:

unknown-tables-denied.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 通過
"""Runs sqlglot's qualify on a join with a table the schema lacks, then sends that join and five other reads throughthe enforcement layer. The prose's point rests on the qualify line, where the star stays, and on the denial lines."""import sqlglotfrom sqlglot.optimizer.qualify import qualifyfrom lib.catalog import open_catalogfrom lib.enforce import Denied, Enforcerplatform = Enforcer(open_catalog(["crm.learners"]))# marketing.audience stands for a table a pipeline wrote after the schema was read. qualify alone lets it through.query = "select * from crm.learners as l join marketing.audience as a on a.learner_id = l.learner_id"print("qualify on its own:", qualify(sqlglot.parse_one(query, read="duckdb"), schema=platform.schema, dialect="duckdb").sql("duckdb"))# The enforcement layer denies anything that isn't a catalog table, before it asks the policy.attempts = {    "a table missing from the catalog": query,    "a column the table doesn't have": "select shoe_size from crm.learners",    # scan_crm_learners is the scan lib.warehouse registers for crm.learners, and DuckDB resolves it like a table    "the scan behind a view": "select count(*) from scan_crm_learners",    "a Parquet file": "select count(*) from read_parquet('data/platform/learners.parquet')",    "a table function": "select count(*) from query_table('crm.learners')",    "a statement that isn't a query": "summarize crm.learners",}for name, sql in attempts.items():    try:        platform.rewrite(sql, "analyst-1")        print(f"{name}: allowed")    except Denied as reason:        print(f"{name}: denied, {reason}")# What those reads would do on the layer's own connection, without the rewrite in front of it.rows = lambda sql: f"{platform.connection.sql(sql).fetchall()[0][0]:,} rows"print("the scan, run directly:", rows("select count(*) from scan_crm_learners"))print("the table function, run directly:", rows("select count(*) from query_table('crm.learners')"))try:    rows("select count(*) from read_parquet('data/platform/learners.parquet')")except Exception as error:    print("the file, run directly:", str(error).split(":")[0])# the layer turned external access off, and DuckDB refuses to turn it back on while the database runstry:    platform.connection.execute("set enable_external_access = true")except Exception as error:    print("turning external access back on:", str(error).split(": ", 1)[1])
輸出python unknown-tables-denied.py
qualify on its own: SELECT * FROM "crm"."learners" AS "l" JOIN "marketing"."audience" AS "a" ON "a"."learner_id" = "l"."learner_id"
a table missing from the catalog: denied, not a table in the catalog: marketing.audience AS a
a column the table doesn't have: denied, Column 'shoe_size' could not be resolved. Line: 1, Col: 16
the scan behind a view: denied, not a table in the catalog: scan_crm_learners
a Parquet file: denied, not a table in the catalog: READ_PARQUET('data/platform/learners.parquet')
a table function: denied, not a table in the catalog: QUERY_TABLE('crm.learners')
a statement that isn't a query: denied, only a single query is allowed
the scan, run directly: 5,000 rows
the table function, run directly: 5,000 rows
the file, run directly: Permission Error
turning external access back on: Cannot enable external access while database is running

單獨使用 qualify 時,會原封不動地回傳帶有 SELECT * 的查詢且不報錯,因此信任它的這一層會就 crm.learners 詢問政策,而讓 marketing.audience 未經檢查就通過。這一層會在詢問政策之前,先拒絕目錄以外的每一個資料表,而 qualify 本身會拒絕資料表所沒有的欄位。其餘四個讀取查詢說明了這條規則為何要定得這麼廣。lib.warehouse 會以各自的名稱註冊每個資料表的掃描,例如 scan_crm_learners。若直接在這一層的連線上執行,該掃描會回傳 5,000 rows 的原始學習者資料。query_table 會讀取其字串所指名的任何資料表,直接執行時會回傳 5,000 rows。read_parquet 會讀取檔案,而 SUMMARIZE 不是改寫步驟能夠分析的查詢。前三者不是目錄中的資料表,最後一個則不是查詢,因此這四者都會被拒絕。這一層的連線也關閉了 DuckDB 的 enable_external_access,所以即使檔案讀取繞過了改寫,仍然會失敗,並出現 Permission Error(Securing DuckDB)。

實用原則: 針對查詢所指名的每一個資料表與欄位詢問政策,並依據目錄的結構描述展開 *,同時拒絕該結構描述未包含的一切:未知的資料表、檔案、資料表函式,以及單一查詢以外的陳述式。拒絕的代價是向平台團隊提出一個問題;放行的代價則是該資料表所含的一切內容。

此部分尚未翻譯,因此以英文呈現。

Allow only catalog tables, CTEs and subqueries

最近一次變動於第 2026.11.31 版

Denying names the catalog doesn't hold isn't enough on its own, because a query can read data through sources that aren't table names. A table function can sit under LATERAL, or in a subquery in the select list. Outside a CTE's scope, or before the CTE is defined, DuckDB resolves its name to whatever else the connection holds under that name. A check that inspects only table names, and accepts any name a CTE uses, passes both.

So the layer checks sources against an allowlist. Enforcer.check_sources walks the whole query and requires every FROM and JOIN source, at every level, to be a catalog table, a CTE in scope or a subquery, and a LATERAL to be over a subquery. Everything else is denied, including table functions wherever they appear, VALUES, UNNEST and joins in parentheses. A CTE is in scope in its own query, below it and in later CTEs, and a recursive one also in its UNION's recursive branch. An allowlist fails closed on a kind of source nobody thought of.

A query through the layer must change nothing. DuckDB's scalar write_log writes into the engine's query log. duckdb_functions() marks such functions: has_side_effects is "true if this function changes the database state" (DuckDB, duckdb_functions). At 2.0.0 it also marks functions that only vary, such as random and uuid. The layer denies every flagged function, and every macro that calls one, such as pg_sleep, reading the list from duckdb_functions() when it starts. A list sorted by hand into harmful and harmless can go stale on an upgrade without anyone noticing, and a governed read seldom needs a random number.

lib.enforce keeps the cases as a regression test, source_cases, which chapter 10's CI runs along with the order test below. Running them shows each denial and whether anything was logged:

catalog-sources-only.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 通過
"""Runs the allowlist's regression cases through the layer and prints each denial with its reason; a case passes when it'sdenied exactly when it must be and its parse logs nothing. The prose's points rest on the count of cases that pass, the functions-denied line and the allowed-query line at the end."""from lib.catalog import open_catalogfrom lib.enforce import MUST_ALLOW, MUST_DENY, Enforcer, source_casesplatform = Enforcer(open_catalog(["crm.learners", "support.tickets"]))# The allowlist's regression test, lib.enforce.source_cases, which chapter 10's CI runs too: queries that read data# other than through a catalog table, a call to each function DuckDB marks as having side effects, and a query the# layer must still allow. A case passes when the layer denies it exactly when it must, and its parse logs nothing.cases = source_cases(platform)for case in cases:    if case["case"] in MUST_DENY:        print(f"{case['case']}:", f"denied, {case['reason']}" if case["denied"] else "ALLOWED")functions = [case for case in cases if case["must"] == "deny" and case["case"] not in MUST_DENY]print("functions with side effects:", ", ".join(case["case"].removeprefix("the function ") for case in functions))print("functions with side effects, denied:", f"{sum(case['denied'] for case in functions)} of {len(functions)}")print("cases whose parse sqlglot logged, quoting the query:", f"{sum(case['logged'] for case in cases)} of {len(cases)}")passing = [case for case in cases if case["denied"] == (case["must"] == "deny") and not case["logged"]]print("cases that pass:", f"{len(passing)} of {len(cases)}")# What two of the cases do on the layer's own connection, without the layer in front of it.# the name is used before its CTE is defined, so DuckDB resolves it to the scan of the same namebefore_the_cte = platform.connection.sql(MUST_DENY["a CTE's name before the CTE"]).fetchall()[0][0]print("a CTE's name before the CTE, run directly:", f"{before_the_cte:,} rows")# turns on DuckDB's query log, the one write_log writes into, so the entries below are what the call leftplatform.connection.execute("CALL enable_logging('QueryLog')")platform.connection.sql(MUST_DENY["a function that writes DuckDB's log"]).fetchall()logged = [message for (message,) in platform.connection.sql("select message from duckdb_logs where type = 'QueryLog'").fetchall()]print("the log function, run directly, query-log entries it leaves:", logged)# the query the allowlist must admit: a CTE, a join and a scalar subquery over catalog tablesfor name, query in MUST_ALLOW.items():    print(f"{name}, allowed:", f"{platform.sql(query, 'agent-a05').fetchall()[0][0]:,} rows")
輸出python catalog-sources-only.py
a table function in FROM: denied, not a table in the catalog: QUERY_TABLE('crm.learners')
a table function in a scalar subquery: denied, not a table in the catalog: QUERY_TABLE('crm.learners')
a table function under LATERAL: denied, a LATERAL that isn't a subquery: LATERAL QUERY_TABLE('crm.learners')
a catalog-qualified name: denied, not a table in the catalog: memory.crm.learners
an information_schema view: denied, not a table in the catalog: information_schema.tables
a CTE's name outside its scope: denied, not a table in the catalog: other
a CTE's name before the CTE: denied, not a table in the catalog: scan_crm_learners
a function that writes DuckDB's log: denied, a function DuckDB marks as having side effects: write_log
a statement sqlglot keeps as text: denied, only a single query is allowed
functions with side effects: current_connection_id, current_query, current_query_id, current_transaction_id, currval, error, gen_random_uuid, nextval, pg_sleep, random, setseed, setval, sleep_ms, stats, uuid, uuidv4, uuidv7, vector_type, write_log
functions with side effects, denied: 19 of 19
cases whose parse sqlglot logged, quoting the query: 0 of 29
cases that pass: 29 of 29
a CTE's name before the CTE, run directly: 5,000 rows
the log function, run directly, query-log entries it leaves: ["select write_log('an entry', log_type := 'QueryLog')", 'an entry']
a CTE, a join and a scalar subquery, allowed: 61 rows

Each case the layer must deny is refused before any policy is asked, with a reason that names the source. The parse has to leave no trace of its own. sqlglot logs any statement it keeps as text, such as a CALL, values included (sqlglot source, v30.24.0), so the layer silences the parser's warnings, and every case checks that nothing was logged. 29 of 29 cases pass.

On the layer's own connection, two cases show what their denials prevent. The name used before its CTE reads the scan of the same name, 5,000 rows of raw learners, and write_log leaves ["select write_log('an entry', log_type := 'QueryLog')", 'an entry'] in the query log: its own statement, and an entry no query made. Every function with side effects is denied, 19 of 19.

The allowlist also has to let ordinary queries through. Run as agent-a05, the EMEA support agent, a CTE, a join and a scalar subquery over catalog tables return 61 rows.

Working rule: Check every source against an allowlist of catalog tables, CTEs in scope and subqueries, deny the rest, and deny every function the engine marks as having side effects, read from its own list. Keep the cases as a regression test for every layer change and engine upgrade, and add one for each new kind of source or function.

遮罩欄位參照會造成洩漏

最近一次變動於第 2026.11.27 版

在已知欄位之後,最直觀的強制執行方式是改寫這些欄位:只要查詢選取了 email,而決策要求遮罩它,就改為選取遮罩後的值。這種做法只需要第一節的欄位清單,而且在人們用來測試的查詢上看起來正確。它之所以失敗,是因為查詢可以讀取一個欄位而不選取它,而且 DuckDB 可以在不指名欄位的情況下回傳該欄位。

Enforcer.mask_select_list 負責進行欄位改寫,保留它只是為了展示它的失敗。我們用五個針對學習者 2 的電子郵件的讀取來測試它,並以 analyst-1 的身分執行;這是一位資料科學家,可以讀取 crm.learners,其中 email 與其他個人欄位會被假名化,而 notes 之類的自由文字則會被遮蔽。第一個輸出是對單純讀取的改寫結果,其中任何儲存的電子郵件都以佔位字串取代,因為這些範例從不印出真實的電子郵件。請預測這個改寫能擋下五個讀取中的幾個:

select-list-masks-leak.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 通過
"""Masks the email in the select list only, then runs five reads aimed at one learner's email through that rewrite andprints what each returns. The prose's point rests on the five attack lines, each of which returns learner 2's ID, thestored email, or an error that holds it."""import reimport duckdbfrom lib.catalog import open_catalogfrom lib.enforce import EMAIL, Enforcerplatform = Enforcer(open_catalog(["crm.learners"]))# analyst-1, a data scientist, may read crm.learners with `email` pseudonymized. The attacks aim at learner 2.email = platform.connection.sql("select email from crm.learners where learner_id = 2").fetchall()[0][0]print("the column rewrite:", platform.mask_select_list("select learner_id, email from crm.learners", "analyst-1"))attacks = {    "a WHERE on the raw column": f"select learner_id from crm.learners where email = '{email}'",    "the whole row as a struct": "select struct_extract(l, 'email') from crm.learners as l where learner_id = 2",    "COLUMNS(...)": "select columns('^email$') from crm.learners where learner_id = 2",    "to_json": "select to_json(l) ->> 'email' from crm.learners as l where learner_id = 2",    "a cast error": "select learner_id from crm.learners where cast(email as integer) = 0",}for name, sql in attacks.items():    try:        # the column rewrite changes only the select list, so every other clause reads the stored email        answer = str(platform.run(platform.mask_select_list(sql, "analyst-1")).fetchall())    except duckdb.Error as error:        answer = str(error).splitlines()[0]    print(f"{name}:", re.sub(EMAIL, "<a stored email>", answer))      # the book never prints a stored email
輸出python select-list-masks-leak.py
the column rewrite: SELECT "learners"."learner_id" AS "learner_id", SHA256($pseudonym_key || CAST("learners"."email" AS TEXT)) AS "email" FROM "crm"."learners" AS "learners"
a WHERE on the raw column: [(2,)]
the whole row as a struct: [('<a stored email>',)]
COLUMNS(...): [('<a stored email>',)]
to_json: [('<a stored email>',)]
a cast error: Conversion Error: Could not convert string '<a stored email>' to INT32 when casting from source column email

這個欄位改寫一個也擋不下。它對單純讀取的改寫是正確的:email 以雜湊值回傳。這五個讀取以三種方式繞過了它:

  • 對原始欄位的 WHERE 從不經過選取清單,因此它比對的是儲存的電子郵件。分析人員猜測一個電子郵件,就能取回學習者 2 的 ID,即 [(2,)],而這個 ID 能把此人連結到所有可透過 learner_id 連接的資料列。
  • 以結構(struct)形式取得整列、COLUMNS(...) 與 to_json 會回傳儲存的電子郵件,也就是 [('<a stored email>',)],而改寫程式找不到任何名為 email 的欄位參照:DuckDB 自己展開整列,或自行以樣式比對欄位名稱。
  • 轉型錯誤會在錯誤訊息中印出儲存的電子郵件。DuckDB 的轉型錯誤包含該值,而 PostgreSQL 的文件也警告「大多數轉型函式都會把其輸入值包含在它們產生的錯誤訊息中」(PostgreSQL 18, Rules and Privileges)。

若在查詢指名該欄位的每個位置都遮罩它,包括 WHERE 子句,就能擋下第一個與最後一個讀取。但它擋不下中間的三個,因為它們從未指名該欄位。

實用原則: 不要以改寫欄位參照的方式來強制執行遮罩。無論你涵蓋多少種參照形式,引擎讀取欄位的方式都多於改寫程式所能辨識的。應改在資料的來源處進行遮罩,讓每一次讀取看到的都是遮罩後的值,如下一節所做的那樣。

以過濾並遮罩的子查詢取代每個資料表

最近一次變動於第 2026.11.27 版

改寫欄位參照會造成洩漏,因為原始資料表仍留在查詢中。解決辦法是取代資料表本身。Enforcer.rewrite會將查詢中的每個目錄資料表,在查詢自己為它取的別名之下,換成依使用者的決策建立的子查詢:

  • 資料表的每個欄位,依資料表的順序排列,因此*、結構(struct)或COLUMNS(...)看到的是它預期的形狀;
  • 查詢讀取的每個欄位,依決策所述以原始儲存值或遮罩後的值呈現,並包在以列過濾條件為依據的CASE之中,列過濾條件將在後面的一節說明;
  • 查詢未讀取的每個欄位,以具型別的NULL呈現,因為政策並未被詢問這些欄位;
  • 一個WHERE子句,內含決策中的每一項列過濾條件。

使用者查詢所做的一切,從它的WHERE到結構讀取,都在這個子查詢上進行。這與 Trino 的 OPA 外掛程式所採用的模型相同,其中列過濾條件的行為基本上就像額外的WHERE子句,如引擎一節所示。

以instructor-17為例,他要讀取自己課程評論中的低評分。政策保留其自有課程的評論,並移除body中的聯絡資訊。印出的改寫結果顯示做法如下:

table-rewrite.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 通過
"""Rewrites instructor-17's query over the reviews, prints the rewrite and runs it. The prose's point rests on the tworesult lines: how many reviews come back, and how many of them still hold an email."""import sqlglotfrom lib.catalog import open_catalogfrom lib.enforce import Enforcerplatform = Enforcer(open_catalog(["learning.reviews"]))# instructor-17 reads the low ratings of their courses' reviews. The policy filters reviews to their six courses and# shows `body` with contact details removed.query = "select course_id, rating, body from learning.reviews where rating <= 2"# the query as text, with the reviews table replaced by a subquery built from the decisionrewritten = platform.rewrite(query, "instructor-17")print(sqlglot.transpile(rewritten, read="duckdb", write="duckdb", pretty=True)[0])print("reviews returned:", len(platform.run(rewritten).fetchall()))print("of them holding an email:", platform.run(f"select count(*) from ({rewritten}) where body like '%@%'").fetchall()[0][0])
輸出python table-rewrite.py
SELECT
  "reviews"."course_id" AS "course_id",
  "reviews"."rating" AS "rating",
  "reviews"."body" AS "body"
FROM (
  SELECT
    CAST(NULL AS BIGINT) AS review_id,
    CAST(NULL AS BIGINT) AS learner_id,
    CASE WHEN source.course_id IN (2, 75, 80, 87, 103, 134) THEN source.course_id END AS course_id,
    CASE WHEN source.course_id IN (2, 75, 80, 87, 103, 134) THEN source.rating END AS rating,
    CASE
      WHEN source.course_id IN (2, 75, 80, 87, 103, 134)
      THEN REGEXP_REPLACE(
        REGEXP_REPLACE(source.body, '[A-Za-z0-9._%+-]+@[A-Za-z0-9-]+(\.[A-Za-z0-9-]+)+', '[email]', 'g'),
        '\+?\(?[0-9][0-9 ().-]{6,}[0-9]',
        '[phone]',
        'g'
      )
    END AS body,
    CAST(NULL AS TIMESTAMPTZ) AS posted_at
  FROM learning.reviews AS source
  WHERE
    source.course_id IN (2, 75, 80, 87, 103, 134)
) AS "reviews"
WHERE
  "reviews"."rating" <= 2
reviews returned: 9
of them holding an email: 0

查詢回傳9則評論,其中0則含有電子郵件。講師的查詢留在外層,經過限定名稱處理,其餘不變。在內層,course_id與rating以儲存值呈現,body則以redact_contacts遮罩呈現,每一個都包在以列過濾條件為依據的CASE中,列過濾條件是講師課程的字面清單,而查詢未讀取的欄位則以具型別的NULL呈現。

使欄位改寫失效的五種讀取方式,由analyst-1透過資料表改寫來執行,並透過lib.enforce.answer執行,這是服務在其邊界上呼叫的函式:

table-rewrite-holds.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 通過
"""Sends the five reads from the column rewrite through the table rewrite, and through `answer`, the service's boundary.It then shows what the analyst receives and what the platform team's log holds. The prose's point rests on the five attacklines, the operator's log line and the relation line."""import loggingimport refrom lib.catalog import open_catalogfrom lib.enforce import EMAIL, Enforcer, QueryFailed, answerplatform = Enforcer(open_catalog(["crm.learners"]))operator_log = []                     # the platform team's log, kept in a list here so the example can print itlogging.getLogger("lib.enforce").addHandler(handler := logging.Handler())handler.emit = lambda record: operator_log.append(record.getMessage().splitlines()[0])# The five attacks on the column rewrite, run by analyst-1 again: through the table rewrite this time, and through# `answer`, the function a service calls at its boundary.email = platform.connection.sql("select email from crm.learners where learner_id = 2").fetchall()[0][0]attacks = {    "a WHERE on the raw column": f"select learner_id from crm.learners where email = '{email}'",    "the whole row as a struct": "select struct_extract(l, 'email') from crm.learners as l where learner_id = 2",    "COLUMNS(...)": "select columns('^email$') from crm.learners where learner_id = 2",    "to_json": "select to_json(l) ->> 'email' from crm.learners as l where learner_id = 2",    "a cast error": "select learner_id from crm.learners where cast(email as integer) = 0",}# a pseudonym is 64 hex digits, shortened here; the example never prints a stored emailshorten = lambda text: re.sub(r"\b([0-9a-f]{8})[0-9a-f]{56}\b", r"\1…", re.sub(EMAIL, "<a stored email>", text))for name, sql in attacks.items():    try:        # answer returns the rows, or raises QueryFailed with a generic message, as a service sees it        got = str(answer(platform, sql, "analyst-1"))    except QueryFailed as error:        got = f"{type(error).__name__}, {error}"    print(f"{name}:", shorten(got))for line in operator_log:    print("in the operator's log:", shorten(line))      # a pseudonym, 64 hex digits, shortened# Why `answer` returns rows: the relation Enforcer.sql returns is a handle on the layer's connection, and runs any SQL.# the relation is a handle on the layer's connection, so its .query runs SQL of its own thererelation = platform.sql("select learner_id from crm.learners", "analyst-1")print("the relation, running SQL of its own:", shorten(str(relation.query("r", "select email from crm.learners where learner_id = 2").fetchall())))print("what answer returns:", type(answer(platform, "select learner_id from crm.learners", "analyst-1")).__name__)
輸出python table-rewrite-holds.py
a WHERE on the raw column: []
the whole row as a struct: [(None,)]
COLUMNS(...): [(None,)]
to_json: [(None,)]
a cast error: QueryFailed, the query failed: the platform team can see why
in the operator's log: a query by analyst-1 failed in the engine: Conversion Error: Could not convert string 'e4f62f1a…' to INT32 when casting from source column email
the relation, running SQL of its own: [('<a stored email>',)]
what answer returns: list

改寫擋下了全部五種。WHERE回傳[],因為在子查詢內,email已經是假名。結構、COLUMNS(...)與to_json的讀取回傳NULL,而不是假名,因為這些查詢除了learner_id之外沒有指名任何欄位。政策沒有被詢問其他欄位,具型別的NULL不給其餘欄位任何值。改寫看不到的讀取會以封閉方式失敗。轉型錯誤現在包含的是假名而非電子郵件,而answer連這一點也隱藏了:它在try之中取得資料列,將任何引擎錯誤轉為QueryFailed,並把 DuckDB 的訊息寫入平台團隊的日誌,範例在五種讀取之後會印出該日誌。分析師得到的是QueryFailed, the query failed: the platform team can see why。

answer回傳的是資料列而非關聯(relation),因為 DuckDB 的關聯可以在其連線上執行任何 SQL。分析師的關聯在被要求取得電子郵件時,回傳[('<a stored email>',)]。

針對每個查詢產生子查詢,而不是為每個角色撰寫一個檢視表,才能讓它與政策保持一致。平台的決策取決於地區、課程、同意與目的,而為每種組合手工建立檢視表,就是 NIST 所描述的「角色爆炸」,其中條件會變成「數量眾多、臨時拼湊且成員有限的角色」(NIST SP 800-162)。只要有人只編輯其中一個,這類檢視表就會與政策產生偏差。

實用原則: 以依決策產生的子查詢,在查詢自己的別名之下取代每個受保護的資料表,其餘部分不更動使用者的查詢。為未讀取的欄位提供具型別的NULL。如果使用者需要讀取整列才能顯示值,則改為就每個資料表的每個欄位詢問政策,讓遮罩涵蓋所有欄位,代價是請求與稽核紀錄會列出查詢從未用到的欄位。

此部分尚未翻譯,因此以英文呈現。

Write each mask as SQL in one place

最近一次變動於第 2026.11.31 版

The policy names a mask; the layer decides what SQL it becomes. Keeping that mapping in one place, the MASKS table in lib.enforce and the typed NULL beside it, means a fix to a mask reaches every table that uses it, and the policy never carries SQL, which would be code arriving as data. The table rewrite calls lib.enforce.masked for every masked column, and prints the SQL for the four masks the policy names:

masks-as-sql.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 通過
"""Prints the SQL the enforcement layer writes for each of the four masks the policy names, and counts DuckDB's functionsnamed like HMAC. The prose's points rest on the pseudonymize line and on the HMAC count."""import duckdbfrom sqlglot import expfrom lib.enforce import masked# The four masks the policy names, as the SQL the enforcement layer writes for them.# the column's type, which the redact mask casts its NULL tofor kind, column, type_ in [("redact", "date_of_birth", "DATE"), ("last4", "card_number", "VARCHAR"),                            ("pseudonymize", "email", "VARCHAR"), ("redact_contacts", "body", "VARCHAR")]:    # redact is a typed NULL; the other masks' SQL comes from lib.enforce's MASKS table    print(f"{kind}:", masked(kind, exp.column(column), type_).sql("duckdb"))# The pseudonymize mask hashes the key followed by the value, because DuckDB has no HMAC function to call.# DuckDB's function catalog, searched for any name containing hmac, whatever its casehmac = duckdb.sql("select count(*) from duckdb_functions() where function_name ilike '%hmac%'").fetchall()[0][0]print("DuckDB functions named like HMAC:", hmac)
輸出python masks-as-sql.py
redact: CAST(NULL AS DATE)
last4: RIGHT(REGEXP_REPLACE(card_number, '[^0-9]', '', 'g'), 4)
pseudonymize: SHA256($pseudonym_key || CAST(email AS TEXT))
redact_contacts: REGEXP_REPLACE(REGEXP_REPLACE(body, '[A-Za-z0-9._%+-]+@[A-Za-z0-9-]+(\.[A-Za-z0-9-]+)+', '[email]', 'g'), '\+?\(?[0-9][0-9 ().-]{6,}[0-9]', '[phone]', 'g')
DuckDB functions named like HMAC: 0

Each mask does one job:

  • redact is a NULL cast to the column's type, so the column keeps its type for anything the query does with it, such as a union or a date function.
  • last4 keeps the last four digits of a card number, after removing anything that isn't a digit.
  • pseudonymize is a keyed hash, SHA-256 over the key followed by the value, because DuckDB 2.0.0 has no HMAC function: duckdb_functions() lists 0 named like it. Where your engine has one, prefer HMAC (RFC 2104).
  • redact_contacts replaces emails and phone numbers in free text with [email] and [phone].

Picture someone holding a list of the platform's emails, who hashes each one and looks for it among the pseudonyms analyst-1 sees in crm.learners:

pseudonym-key.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 通過
"""Hashes a list of emails the way the layer hashes pseudonyms, with and without its key, then rotates the key and countsthe pseudonyms that change. The prose's points rest on the three count lines: the matches without the key and with it, andthe pseudonyms that change on rotation."""from lib.catalog import open_catalogfrom lib.enforce import Enforcer# The pseudonyms analyst-1, the platform's data scientist, sees in crm.learners. Someone holding a list of the# platform's emails hashes each one and looks for it among them, first without the key and then with it.# an example key, not a secret: production keeps the real one where only the layer can read itplatform = Enforcer(open_catalog(["crm.learners"]), key="example-key-not-a-secret")seen = platform.sql("select learner_id, email from crm.learners", "analyst-1").to_arrow_table()platform.connection.register("seen", seen)# a guess is an email's hash; the second also puts the key first, as the layer hashes a pseudonymfor name, guess in [("without the key", "sha256(email)"), ("with the key", "sha256($pseudonym_key || email)")]:    matched = platform.run(f"select count(*) from crm.learners where {guess} in (select email from seen)").fetchall()[0][0]    print(f"pseudonyms matched from a list of emails, {name}: {matched:,} of {seen.num_rows:,}")# A new key gives every learner a new pseudonym, so data pseudonymized under the old key no longer joins.platform.key = "example-key-rotated"rotated = platform.sql("select learner_id, email from crm.learners", "analyst-1").to_arrow_table()# pairs each learner's old and new pseudonym, row by rowchanged = sum(a != b for a, b in zip(seen["email"].to_pylist(), rotated["email"].to_pylist()))print("pseudonyms that change when the key is rotated:", f"{changed:,} of {seen.num_rows:,}")
輸出python pseudonym-key.py
pseudonyms matched from a list of emails, without the key: 0 of 5,000
pseudonyms matched from a list of emails, with the key: 5,000 of 5,000
pseudonyms that change when the key is rotated: 5,000 of 5,000

The key is what makes a pseudonym a pseudonym. Without it, the hashes match 0 of 5,000 pseudonyms; with it, they match 5,000 of 5,000, so whoever holds the key and a list of emails can name every pseudonym on it. The key belongs to the layer alone. It stays out of the policy, which is code read in pull requests, and out of the query, where a user who chose the key could compute pseudonyms of guessed emails. The layer passes the key to DuckDB as a query parameter, so it stays out of the query text that DuckDB's log records, as chapter 8 shows, and the layer denies any user query that carries a parameter, since one named $pseudonym_key would receive the key. In production, keep the key in a secret manager that only the layer can read. Rotate it when it may have leaked, and plan for what rotation does: 5,000 of 5,000 pseudonyms change, so data pseudonymized under the old key no longer joins with data under the new one.

redact_contacts is two regular expressions, and it's worth measuring what they remove. The platform's tickets and reviews come with every piece of personal data the generator placed in them, labelled by construction, which chapter 3 calls the truth. Each text goes through the mask here, and the labels count what survives:

redact-contacts-recall.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 通過
"""Applies the redact_contacts mask to every ticket and review, then counts the labelled personal data still left in themasked text, and the phone replacements made in the tickets. The prose's points rest on the 'left in the text' lines, thereplacement count, and the assert on the tickets' card, IBAN and date lines."""import duckdbfrom sqlglot import expfrom lib.enforce import maskedfrom lib.platform import load# The redact_contacts mask, run over every ticket and review, against the personal data the generator placed in them# (pii_spans, labelled by construction).con = duckdb.connect()# each table registers under its short name, which the queries below usefor name in ["support.tickets", "learning.reviews", "pii_spans"]:    con.register(name.split(".")[-1], load(name))mask = masked("redact_contacts", exp.column("body"), "VARCHAR").sql("duckdb")# a span counts as left when its exact text is still in the masked body, which the row_id join findscounts = con.sql(f"""    with masked as (select 'tickets' as source, ticket_id as row_id, {mask} as body from tickets                    union all select 'reviews', review_id, {mask} from reviews)    select source, entity, count(*), count(*) filter (where contains(masked.body, pii_spans.text))    from pii_spans join masked using (source, row_id) group by all order by all""").fetchall()# the phone pattern takes every card number, IBAN and date in the tickets, so none is left in the masked textleft_in_tickets = {entity: left for source, entity, _, left in counts if source == "tickets"}assert all(left_in_tickets[entity] == 0 for entity in ("CREDIT_CARD", "IBAN_CODE", "DATE_TIME"))for source, entity, placed, left in counts:    print(f"{source}, {entity}, left in the text: {left:,} of {placed:,}")# The phone pattern also takes card numbers, IBANs and dates, which are digits too.replaced = con.sql(f"select sum(len(regexp_extract_all({mask}, '\\[phone\\]'))) from tickets").fetchall()[0][0]print("[phone] replacements in tickets:", f"{replaced:,}")
輸出python redact-contacts-recall.py
reviews, EMAIL_ADDRESS, left in the text: 0 of 120
reviews, PERSON, left in the text: 798 of 798
tickets, CREDIT_CARD, left in the text: 0 of 213
tickets, DATE_TIME, left in the text: 0 of 196
tickets, EMAIL_ADDRESS, left in the text: 0 of 423
tickets, IBAN_CODE, left in the text: 0 of 236
tickets, LOCATION, left in the text: 210 of 210
tickets, PERSON, left in the text: 1,657 of 1,657
tickets, PHONE_NUMBER, left in the text: 0 of 439
[phone] replacements in tickets: 1,520

The mask removes contact details and leaves names alone. In the reviews, which instructors and the assistant read through this mask, 0 of 120 emails are left, and 798 of 798 names are.

In the tickets, 0 of 439 phone numbers are left, but the phone pattern makes 1,520 replacements. Card numbers, IBANs and dates are digits too, so the pattern takes them as well, and none of them is left. Names and street addresses stay, so the mask doesn't make text anonymous.

Working rule: Keep every mask's SQL in one table in the enforcement layer, and let the policy name masks, never write them. Key every pseudonym with a secret only the layer holds, passed as a parameter. Measure each text mask against labelled data before a policy relies on it, and name masks for what they remove, so nobody reads redact_contacts as "anonymized".

從決策的資料建立資料列篩選條件

最近一次變動於第 2026.11.27 版

遮罩改變的是值的呈現方式;資料列篩選條件則決定使用者能取得哪些資料列。它們以資料的形式出現在決策中,共有三種形態:欄位等於某個值、欄位位於一串值之中,以及針對資料列所屬學習者的同意檢查。lib.enforce.row_filter 會把每一種轉成 SQL 條件,其中的值由 sqlglot 寫成 SQL 字面值,絕不貼進 SQL 文字裡。遇到不認得的形態,或資料表沒有的欄位,它會拒絕該查詢;而在建立同意檢查之前,這一層會先對照 crm.learners 檢查同意篩選條件所用的欄位。第 5 章讓政策以資料的形式回傳篩選條件,正是為了這個原因:請求所攜帶的任何內容,都無法以程式碼的身分進入查詢。

同意這種形態需要特別小心。課程助理 assistant-1 只能讀取那些同意自己的文字用於訓練平台 AI 的學習者所寫的評論,而這項同意記錄在 crm.learners 中,助理本身不得讀取該資料表。這一層將它轉成半連接(semi-join),也就是檢驗評論的 learner_id 是否屬於已同意的學習者之一,並以這一層自己對 crm.learners 的存取權限來執行:

row-filters-from-data.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 通過
"""Prints the condition the layer writes for each row filter in three decisions, then the reviews the assistant readsthrough its consent filter, and the denial of its read of the learners table. The prose's points rest on the read countsand the consent counts, and on the denied read at the end."""from lib.catalog import open_catalogfrom lib.enforce import Enforcer, row_filterplatform = Enforcer(open_catalog(["crm.learners", "learning.reviews", "support.tickets"]))# Each row filter in a decision, as the condition the enforcement layer writes for it.for user, table in [("agent-a05", "support.tickets"), ("instructor-17", "learning.reviews"), ("assistant-1", "learning.reviews")]:    # the decision for this read; its row filters are data, written as SQL in the loop below    decision = platform.decide(user, {table: set()})[table]    for rule in decision["row_filters"]:        print(f"{user}, {table}: {row_filter(rule, platform.columns(table)).sql('duckdb')}")# A filter's values come from data, so a value that looks like SQL stays a value.odd = {"column": "support_region", "equals": "emea' or '1' = '1"}print("a region that looks like SQL:", row_filter(odd, platform.columns("support.tickets")).sql("duckdb"))# The assistant's consent filter reads crm.learners, which the assistant itself may not read.total = platform.connection.sql("select count(*) from learning.reviews").fetchall()[0][0]read = platform.sql("select count(*) from learning.reviews", "assistant-1").fetchall()[0][0]print("reviews the assistant reads:", f"{read:,} of {total:,}")# a null consent means no answer was recorded, so the label reads 'never asked'by_consent = platform.connection.sql("""select coalesce(l.ai_training_consent::varchar, 'never asked'), count(*)    from learning.reviews r join crm.learners l using (learner_id) group by all order by all""").fetchall()for consent, reviews in by_consent:    print(f"reviews whose author's AI-training consent is {consent}: {reviews:,}")try:    platform.sql("select count(*) from crm.learners", "assistant-1")except Exception as reason:    print("the assistant reading crm.learners:", reason)
輸出python row-filters-from-data.py
agent-a05, support.tickets: source.support_region = 'emea'
instructor-17, learning.reviews: source.course_id IN (2, 75, 80, 87, 103, 134)
assistant-1, learning.reviews: source.learner_id IN (SELECT consent.learner_id FROM crm.learners AS consent WHERE consent.ai_training_consent IS TRUE)
a region that looks like SQL: source.support_region = 'emea'' or ''1'' = ''1'
reviews the assistant reads: 436 of 3,000
reviews whose author's AI-training consent is false: 955
reviews whose author's AI-training consent is never asked: 1,609
reviews whose author's AI-training consent is true: 436
the assistant reading crm.learners: assistant-1 may not read crm.learners: no group of this user may read this table

每個篩選條件的值都會成為 SQL 字面值,因此值始終只是值,而尚未回答的同意則視為不同意。客服人員的地區與講師的課程,會變成一個等式條件和一份 IN 清單;寫得像 SQL 的地區仍然只是一個字串,其中的引號會被加倍。同意篩選條件測試的是 IS TRUE,因此那些1,609的評論,其作者是在平台詢問此問題之前就註冊、沒有留下任何回答記錄的人,會和回答否的人一樣,被視為不同意。

助理讀取的是436 of 3,000的評論,也就是作者回答同意的那些。助理自己對 crm.learners 提出的請求會被拒絕,並附上政策給出的理由:半連接對該資料表的讀取屬於這一層,而不屬於使用者。

**實用原則:**每一個資料列篩選條件,都要用 SQL 建構器從決策的值建立,並拒絕這一層不認得的任何篩選形態或欄位。把未知的同意視為不同意。當篩選條件需要使用者不得讀取的資料表時,在子查詢內以這一層自己的存取權限讀取,絕不以擴大使用者的存取權限來達成。

讓每個欄位都取決於資料列篩選條件

最近一次變動於第 2026.11.27 版

資料列篩選條件位於子查詢的 WHERE 中,而使用者的查詢包住該子查詢,因此很容易以為 DuckDB 會先套用這些篩選條件。然而 SQL 並未承諾任何順序,最佳化器會把條件移到成本最低的位置。使用者所寫、可能失敗的條件(例如型別轉換),便可能在被篩選條件排除的資料列上執行,而即使只是一般的錯誤訊息也有問題:查詢失敗這件事,本身就透露了使用者不該看到的某一列的資訊。

PostgreSQL 的文件指出了這種洩漏:若函式可能因引數而失敗,且在檢視表的資料列篩選條件之前執行,就可能洩漏關於看不見的資料列的重要資訊。在 PostgreSQL 中,標記為 security_barrier 的檢視表能避免「惡意挑選的函式與運算子,在檢視表完成其工作之前,就被傳入資料列的值」,而只有標記為 LEAKPROOF 的函式仍可先執行(PostgreSQL 18, Rules and Privileges)。DuckDB 兩者皆無。

因此,這一層讓執行順序變得無關緊要。查詢讀取的每個欄位,都以 CASE WHEN <row filters> THEN <value> END 的形式離開子查詢,如講師的改寫版本所示。使用者的條件便取決於篩選條件的結果:無論 DuckDB 先評估什麼,對於被篩選條件排除的資料列,每個欄位看到的都是 NULL。

lib.enforce.order_cases 會測試這一層所寫出的每種篩選條件形式:客服人員的等號條件、講師的 IN 清單,以及課程助理的同意半連接。每個測試案例都會複製被篩選的資料表,其中的 body 在篩選條件保留的資料列中是數字,在被排除的資料列中是文字,然後執行一個把 body 轉成數字的查詢。只有當轉換看到被排除資料列的值時,才會失敗:

row-filter-order.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 通過
"""Runs the evaluation-order test for each filter shape the layer writes, with the filter in a WHERE alone and as the layerwrites it. The prose's point rests on the consent semi-join's two lines, and on the count at the end."""from lib.enforce import order_cases# The evaluation-order regression test, lib.enforce.order_cases, which chapter 10's CI runs on every DuckDB upgrade.# For each shape of row filter the layer writes, it takes a real user's filter and a copy of the filtered table whose# `body` holds digits in the rows the filter keeps and text in the rows it drops. A query that casts `body` to a number# fails only if the cast sees a dropped row's value. It runs over the filter in a WHERE alone, and over the layer's# subquery.cases = order_cases()for case in cases:    for form, safe in [("with the filter in WHERE only", case["safe_with_where_only"]),                       ("as the layer writes it", case["safe_as_written"])]:        print(f"{case['shape']}, {form}: the cast {'never saw' if safe else 'saw'} a dropped row's value")print("filter shapes the layer's subquery keeps safe:", f"{sum(case['safe_as_written'] for case in cases)} of {len(cases)}")
輸出python row-filter-order.py
equality, with the filter in WHERE only: the cast never saw a dropped row's value
equality, as the layer writes it: the cast never saw a dropped row's value
IN list, with the filter in WHERE only: the cast never saw a dropped row's value
IN list, as the layer writes it: the cast never saw a dropped row's value
consent semi-join, with the filter in WHERE only: the cast saw a dropped row's value
consent semi-join, as the layer writes it: the cast never saw a dropped row's value
filter shapes the layer's subquery keeps safe: 3 of 3

讓每個欄位都受閘控,正是同意半連接得以安全的原因。若篩選條件只放在 WHERE 中,等號條件與 IN 清單是安全的,同意半連接則不然:DuckDB 會在作者未同意的評論上執行轉換。依這一層的寫法,3 of 3種篩選條件形式都能讓轉換遠離被排除的資料列。前兩種在 2.0.0 中是出於最佳化器的選擇才安全,並非來自任何保證,所以這項測試在每次升級時都要執行。這道閘控的代價,是在查詢讀取的每個欄位中再次評估篩選條件。

**實用原則:**切勿仰賴引擎的評估順序,來讓使用者的條件不碰到被篩選條件排除的資料列。讓使用者查詢能讀取的每個值都取決於資料列篩選條件,並在每次引擎升級時測試每種篩選條件形式。若引擎本身有屏障機制,例如 PostgreSQL 的 security_barrier 檢視表,也應一併使用。

以執行查詢來測試誰能看到什麼

最近一次變動於第 2026.11.27 版

第 5 章以一份「誰能看到什麼」的表格 lib.policy.WHO_SEES_WHAT 來檢查政策:這是一份 requests 清單,每筆都附有審查者預期的決策。這測試的是決策,而不是使用者實際拿到的內容,後者還取決於改寫、遮罩的 SQL 與資料本身。lib.enforce.observe 會讓每個案例以該案例的使用者身分通過這一層,並將回傳的結果與儲存的資料比較:有多少列,以及哪些欄位不同。當使用者被拒絕的情形與案例所述完全一致、取得的列數少於儲存的列數,且僅在案例預期有列過濾時才如此,且被更動的值恰好出現在案例預期遮罩的欄位時,這個案例就與其決策相符:

who-sees-what-in-queries.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 通過
"""Runs chapter 5's who-sees-what cases as queries and compares what each user gets with what is stored, then readsmarketing.audience, which has no metadata. The prose's points rest on the agreement count and on the two marketing lines."""from lib import pipelines, policyfrom lib.catalog import open_catalogfrom lib.enforce import Enforcer, agrees, observecatalog = open_catalog(["crm.learners", "learning.reviews", "billing.payments", "learning.enrollments", "learning.courses"])pipelines.run(catalog, "marketing_audience")          # a copy of learners' names and emails, with no metadata yetplatform = Enforcer(catalog)# Chapter 5's who-sees-what table, run as queries: what each user gets, against what's stored. A column is "changed"# when at least one value the user gets differs from the stored one.agreeing = 0for case in policy.WHO_SEES_WHAT:    got = observe(platform, case.user, case.table, case.columns, case.purpose)    who = case.user + (f" for {case.purpose}" if case.purpose else "")    if got is None:        print(f"{who}, {case.table}: denied")    else:        print(f"{who}, {case.table}: {got['rows']:,} of {got['stored']:,} rows, changed: {', '.join(got['changed']) or 'none'}")    agreeing += agrees(case, got)print("cases where what the user gets agrees with the decision:", f"{agreeing} of {len(policy.WHO_SEES_WHAT)}")# marketing.audience is a table marketing may read, but nobody has classified its columns, so the policy treats each# one as sensitive: marketing gets every row, and no value in any column.rows = platform.sql("select * from marketing.audience", "marketing-1").fetchall()# a column counts as shown when one of its values isn't None, which is what a redact mask returnsshown = sum(any(value is not None for value in column) for column in zip(*rows))print("marketing-1, marketing.audience, which has no metadata, rows:", f"{len(rows):,}")print("marketing-1, marketing.audience, which has no metadata, columns with a value:", f"{shown} of {len(rows[0])}")
輸出python who-sees-what-in-queries.py
agent-a05, crm.learners: 1,581 of 5,000 rows, changed: none
agent-a05, billing.payments: denied
instructor-17, learning.reviews: 69 of 3,000 rows, changed: body
instructor-17, crm.learners: denied
marketing-1, crm.learners: 1,861 of 5,000 rows, changed: phone
finance-1, billing.payments: 17,562 of 17,562 rows, changed: card_number
analyst-1, billing.payments: 17,562 of 17,562 rows, changed: card_number
analyst-1, crm.learners: 5,000 of 5,000 rows, changed: email, notes
analyst-1, learning.reviews: 3,000 of 3,000 rows, changed: body
analyst-1 for ai-training, learning.reviews: 436 of 3,000 rows, changed: body
assistant-1, learning.reviews: 436 of 3,000 rows, changed: body
someone-else, crm.learners: denied
cases where what the user gets agrees with the decision: 12 of 12
marketing-1, marketing.audience, which has no metadata, rows: 1,829
marketing-1, marketing.audience, which has no metadata, columns with a value: 0 of 5

在 12 of 12 個案例中,使用者拿到的內容與決策相符。EMEA 的客服人員取得其所屬區域的學習者,資料與儲存的一致;行銷人員取得同意接收且活躍的學習者,其中 phone 已被更動;分析師取得所有學習者,其中 email 與 notes 已被更動。最後兩行涵蓋 marketing.audience,這是由管線寫入、尚無人分類的資料。行銷人員可以讀取它,並取得 1,829 列,其中有值的欄位有 0 of 5 個,因為政策將未分類的欄位視為敏感欄位。

這項測試在變更時才顯出價值。假設某個 pull request 將 analyst-1 加入 EMEA 的客服群組,讓他們能協助處理工單。這是對 governance/users.toml 的兩行變更,內容是一個群組,以及客服的列過濾所需的區域。下方的程式碼在記憶體中進行這項變更,並對它執行相同的案例:

group-change-caught.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 通過
"""Makes one change to the users file in memory, runs the who-sees-what cases again, and runs chapter 5's decision checkon the same change. The prose's points rest on the lines that say which cases fail, and on the decision count."""from lib import policyfrom lib.catalog import open_catalogfrom lib.enforce import Enforcer, agrees, observe# A pull request changes one user in governance/users.toml, in two lines: analyst-1 joins support, with the region# support's row filter needs, EMEA, to help with tickets.# the users file as a document, changed here in memory and not on diskdata = policy.data_document()data["platform"]["users"]["analyst-1"] = {"groups": ["data-science", "support"], "region": "emea"}platform = Enforcer(open_catalog(["crm.learners", "learning.reviews", "billing.payments"]),                    rego=policy.interpreter(data=data))# The who-sees-what cases, run as queries with the change in place. The cases are unchanged: they say what each# user should get, and the change was meant to touch support tickets only.failing = 0for case in policy.WHO_SEES_WHAT:    got = observe(platform, case.user, case.table, case.columns, case.purpose)    # got is None when the user is denied, which agrees only with a case that expects a denial    if not agrees(case, got):        failing += 1        who = case.user + (f" for {case.purpose}" if case.purpose else "")        print(f"fails, {who}, {case.table}, rows: {got['rows']:,} of {got['stored']:,}")        print(f"fails, {who}, {case.table}, columns changed: {', '.join(got['changed']) or 'none'}, "              f"expected: {', '.join(case.masked)}")print("cases that fail:", f"{failing} of {len(policy.WHO_SEES_WHAT)}")# Chapter 5's table, which checks the decisions themselves, on the same change.decisions = [policy.decide(policy.request(c.user, c.table, c.columns, c.purpose), platform.rego) for c in policy.WHO_SEES_WHAT]print("cases that fail as decisions:", f"{sum(not policy.matches(c, d) for c, d in zip(policy.WHO_SEES_WHAT, decisions))} of {len(decisions)}")
輸出python group-change-caught.py
fails, analyst-1, crm.learners, rows: 1,581 of 5,000
fails, analyst-1, crm.learners, columns changed: none, expected: email, notes
fails, analyst-1, learning.reviews, rows: 3,000 of 3,000
fails, analyst-1, learning.reviews, columns changed: none, expected: body
fails, analyst-1 for ai-training, learning.reviews, rows: 436 of 3,000
fails, analyst-1 for ai-training, learning.reviews, columns changed: none, expected: body
cases that fail: 3 of 12
cases that fail as decisions: 3 of 12

這項變更使 3 of 12 個案例失敗,而且沒有一個與工單有關。三者全是分析師對學習者與評論的讀取。在 crm.learners 上,分析師現在取得 1,581 of 5,000 列,且沒有任何欄位被更動:客服的區域過濾套用在他們身上,而政策中針對個人欄位的遮罩只套用於客服以外的使用者,因此每個電子郵件與備註都原樣回傳。評論文字原本資料科學家完全無法讀取,或在為 AI 訓練而移除聯絡資訊後才能讀取,現在卻完整回傳。第 5 章的決策層級表格在相同的案例上失敗,即 3 of 12;而這項測試顯示的是分析師實際拿到的內容。

實用原則: 每當政策、使用者檔案、中繼資料或這一層有變更時,都要將「誰能看到什麼」的案例當作查詢,通過這一層執行,並在任一案例不一致時讓建置失敗。要修正失敗的案例,應修改政策或使用者檔案。只有在資料擁有者同意使用者應取得的內容已經改變時,才修改案例。

此部分尚未翻譯,因此以英文呈現。

Hold back small groups in reports

最近一次變動於第 2026.11.31 版

Row filters and masks decide which rows and values a user sees. Aggregates are a second channel: a count or an average over a few people describes those people. The usual defence is a minimum group size, below which a reported group is held back. The number is a rule of thumb, not a measurement. One public data-use agreement from the US Centers for Medicare & Medicaid Services sets 11, and adds that "no use of percentages or other mathematical formulas may be used if they result in the display of a cell less than 11" (CMS-R-0235L). The platform uses the same minimum, 11, and keeps it in governance/enforcement.toml, where a change is reviewed like a policy change, and lib.enforce.minimum_group_size reads it.

Instructors get a daily course report of learners and average progress per course and learner country, built by a job that reads countries instructors may not read. Instructors may read their own courses' enrollments with each one's progress. Combining those two gives instructor-17's report:

small-groups-difference.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 通過
"""Builds instructor-17's daily course report, counts its cells under the minimum and its cells of one learner, and matcheseach cell of one against the enrollments the instructor may read. The prose's points rest on the cell and lookup counts, andon the last line, which shows the differencing."""from lib.catalog import open_catalogfrom lib.enforce import Enforcer, minimum_group_sizeplatform = Enforcer(open_catalog(["crm.learners", "learning.courses", "learning.enrollments"]))minimum = minimum_group_size()print("minimum group size:", minimum)count = lambda sql: platform.connection.sql(f"select count(*) from ({sql})").fetchall()[0][0]# The platform's daily course report for instructor-17: learners and average progress, per course and learner country.# The report job reads learners' countries, which instructors may not read.platform.connection.execute("""create view mine as    select e.course_id, e.progress_pct, e.enrolled_at::date as day, l.country    from learning.enrollments e join crm.learners l using (learner_id)    where e.course_id in (select course_id from learning.courses where instructor_id = 17)""")platform.connection.execute("""create view report as    select course_id, country, count(*) as learners, avg(progress_pct) as progress from mine group by all""")print("cells in the report:", count("from report"))# the minimum is the threshold: a cell with fewer learners than it is held backprint("cells under the minimum:", count(f"from report where learners < {minimum}"))# instructor-17 may read their courses' enrollments, with each one's progress. A cell of one learner, matched on its# progress, names that learner's country.# what instructor-17 may read: enrollments with each one's progress, and no countryseen = platform.sql("select course_id, progress_pct from learning.enrollments", "instructor-17").to_arrow_table()platform.connection.register("seen", seen)print("cells of one learner:", count("from report where learners = 1"))print("learners a cell of one names:", count("""from report as cell where learners = 1 and (select count(*) from seen    where seen.course_id = cell.course_id and seen.progress_pct = cell.progress) = 1"""))print("cells shown with the minimum:", count(f"from report where learners >= {minimum}"))# The minimum doesn't stop differencing. An enrollment that arrives alone in its course on its day, in a cell already# shown, raises that cell by one in the next day's report, and the instructor sees which enrollment arrived.# an enrollment alone in its course that day, whose country already has the minimum on earlier daysrevealed = count(f"""from mine as m    where (select count(*) from mine as o where o.course_id = m.course_id and o.day = m.day) = 1      and (select count(*) from mine as o where o.course_id = m.course_id and o.country = m.country and o.day < m.day) >= {minimum}""")print("learners whose country the daily report reveals anyway:", f"{revealed} of {count('from mine')}")
輸出python small-groups-difference.py
minimum group size: 11
cells in the report: 63
cells under the minimum: 51
cells of one learner: 6
learners a cell of one names: 3
cells shown with the minimum: 12
learners whose country the daily report reveals anyway: 92 of 482

A cell of one names its learner's country to anyone who can find that learner, and the instructor can: matching the cell's average progress against their own enrollments names 3 learners exactly. The report has 63 cells, of which 51 are under the minimum and 6 hold one learner each.

With the minimum, 12 cells are shown, and none of those lookups works. The minimum doesn't stop differencing. An enrollment that arrives alone in its course on its day, into a cell that's already shown, raises that cell by one in the next day's report, and the instructor sees which enrollment arrived. That reveals the country of 92 of 482 learners.

Differencing, comparing consecutive reports, is the attack Denning, Denning and Schwartz called a tracker. They showed that "the compromise of small query sets can in fact almost always be accomplished with the help of characteristic formulas called trackers" (Denning et al., 1979).

Snowflake, which enforces a minimum group size in its engine, says its policies "do not guarantee a malicious actor could not use deliberate queries to obtain potentially sensitive data" (Snowflake aggregation policies). Against differencing, we recommend noise, as in differential privacy (Dwork and Roth, 2014), rather than higher thresholds, and Privacy Engineering for Data Teams covers it.

Working rule: Hold back every reported group below the minimum in governance/enforcement.toml, and treat the minimum as protection against looking one person up, never against differencing. Don't break a report down by an attribute its reader may not read, sent to a reader who can see the rows behind it: the instructor's report by country is a lookup table for countries. Drop the breakdown, or report it only after it has been made noisy.

此部分尚未翻譯,因此以英文呈現。

Hand the same policies to an engine that enforces them

最近一次變動於第 2026.11.27 版

In production, enforcement belongs in the engine every tool queries through, because Saltzer and Schroeder's principle of complete mediation holds that "every access to every object must be checked for authority" (Saltzer and Schroeder, 1975), and a policy enforced in one tool and not another fails it. Trino, an open-source distributed SQL engine, can take its access control from Open Policy Agent (OPA) through a plugin, and chapter 5 shaped the platform's requests like the plugin's so the same policy could serve both. Trino 484 offers file-based, OPA and Ranger access control, and none for Cedar (Trino 484, Security).

The plugin asks OPA separate questions at separate URIs (Trino 484, OPA access control):

  • Whether to allow an operation, such as SelectFromColumns, answered with a boolean allow.
  • Row filters, GetRowFilters, for a table. OPA answers with a list of {"expression": ...}, and each expression "essentially behaves like an additional WHERE clause". The URI is optional, and Trino applies no row filtering when it isn't set.
  • Column masks, GetColumnMask, for one column, or a batch of columns at opa.policy.batch-column-masking-uri. "Only a single column mask may be returned for a given column."

The operation names and where each request puts its table or column are in the plugin's source at tag 484 (OpaAccessControl.java, OpaHighLevelClient.java). The documentation's batch-mask policy reads filterResources[i].catalogName where its own request example nests those fields under .column, so as printed it wouldn't match. Requests built in those shapes go to its policy:

trino-requests.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 通過
"""Builds requests in the shape Trino 484's OPA plugin sends, and runs them through the platform's policy. The prose'spoints rest on the allow lines, which the example asserts are False, and on the GetRowFilters and GetColumnMask lines."""import jsonfrom lib import metadata, policyfrom lib.enforce import row_filter# The platform's policy, with a support agent added before anyone set their region (chapter 5's missing-region case).data = policy.data_document()data["platform"]["users"]["agent-new"] = {"groups": ["support"]}rego = policy.interpreter(data=data)described = metadata.load()def trino_request(user: str, operation: str, resource: dict | None = None) -> dict:    """A request in the shape Trino 484's OPA plugin sends (OpaAccessControl, OpaHighLevelClient)."""    action = {"operation": operation} | ({"resource": resource} if resource else {})    return {"context": {"identity": {"user": user, "groups": []}, "softwareStack": {"trinoVersion": "484"}}, "action": action}# Trino asks `allow` about every operation: ExecuteQuery before any query runs, AccessCatalog for each catalog it names.for operation, resource in [("ExecuteQuery", None), ("AccessCatalog", {"catalog": {"name": "platform"}})]:    allowed = policy.decide(trino_request("agent-a05", operation, resource), rego)["allow"]    # allow admits only SelectFromColumns, so Trino's checks before any query are denied    assert allowed is False    print(f"allow, {operation}, agent-a05:", allowed)# GetRowFilters names a table. The policy's row_filters rule on its own skips `allow` and its missing-attribute guard,# so for agent-new it returns no filter, which Trino reads as no filtering. The answer comes from the decision instead,# for the same read asked as SelectFromColumns: its filters in Trino's SQL, or a filter that keeps no rows.for user, schema, table in [("agent-a05", "support", "tickets"), ("instructor-17", "learning", "reviews"),                            ("agent-new", "support", "tickets")]:    request = trino_request(user, "GetRowFilters", {"table": {"catalogName": "platform", "schemaName": schema, "tableName": table}})    print(f"GetRowFilters, {user}, {schema}.{table}, the rule alone:",          json.dumps(policy.evaluate(rego, "data.platform.access.row_filters", request)))    decision = policy.decide(policy.request(user, f"{schema}.{table}", []), rego)    columns = {c.name: "" for c in described[f"{schema}.{table}"].columns.values()}    # no table alias: Trino applies the filter to the table itself, as a WHERE clause on it    filters = [{"expression": row_filter(rule, columns, table=None).sql("trino")} for rule in decision["row_filters"]]    # a denied read gets a filter that keeps no rows    print(f"GetRowFilters, {user}, {schema}.{table}, answered:", json.dumps(filters if decision["allow"] else [{"expression": "false"}]))# GetColumnMask names one column, under action.resource.column. The policy reads the columns of a SelectFromColumns# request instead, so it masks nothing here until the request is translated.column = {"catalogName": "platform", "schemaName": "crm", "tableName": "learners", "columnName": "email", "columnType": "varchar"}decision = policy.decide(trino_request("analyst-1", "GetColumnMask", {"column": column}), rego)print("GetColumnMask as sent, analyst-1, crm.learners.email:", json.dumps(decision["column_masks"]), decision["reasons"][0])translated = policy.request("analyst-1", "crm.learners", [column["columnName"]])print("GetColumnMask translated, analyst-1, crm.learners.email:", json.dumps(policy.decide(translated, rego)["column_masks"]))
輸出python trino-requests.py
allow, ExecuteQuery, agent-a05: False
allow, AccessCatalog, agent-a05: False
GetRowFilters, agent-a05, support.tickets, the rule alone: [{"column": "support_region", "equals": "emea"}]
GetRowFilters, agent-a05, support.tickets, answered: [{"expression": "support_region = 'emea'"}]
GetRowFilters, instructor-17, learning.reviews, the rule alone: [{"column": "course_id", "in": [2, 75, 80, 87, 103, 134]}]
GetRowFilters, instructor-17, learning.reviews, answered: [{"expression": "course_id IN (2, 75, 80, 87, 103, 134)"}]
GetRowFilters, agent-new, support.tickets, the rule alone: []
GetRowFilters, agent-new, support.tickets, answered: [{"expression": "false"}]
GetColumnMask as sent, analyst-1, crm.learners.email: {} GetColumnMask is not an operation this policy allows
GetColumnMask translated, analyst-1, crm.learners.email: {"email": "pseudonymize"}

Pointed at Trino unchanged, the policy would deny the checks Trino makes first, because its allow admits only SelectFromColumns. allow is false for ExecuteQuery, which Trino asks before any query runs, and for AccessCatalog.

The row_filters rule doesn't test the operation, so it answers GetRowFilters, but on its own it skips allow and chapter 5's missing-attribute guard. For agent-new, a support agent with no region yet, it returns [], which Trino reads as no filtering. So the answer has to come from the decision for the same read, asked as SelectFromColumns: its filters, written in Trino's SQL by the same row_filter the rewrite uses, or, when it denies the read, [{"expression": "false"}], which keeps no rows.

GetColumnMask names the column under action.resource.column, while the policy reads a SelectFromColumns request's columns, so as sent it masks nothing and the policy rejects the operation; translated, it gets pseudonymize. In production, those translations, the allow check and the Trino SQL belong in a Rego module beside the policy, so OPA can answer Trino itself. No OPA server runs here, so the code shows only what that module must return. One more piece maps onto Trino directly: the consent filter reads crm.learners, which the assistant may not, and Trino lets a filter carry an identity to run as, "such that a filter can target a column the requesting user cannot see".

Since 1.9.0, OPA's Compile API can also write SQL WHERE clauses from a policy by partial evaluation (OPA v1.9.0, Data filtering with OPA), but it needs an OPA server, which the examples don't run.

Working rule: Put enforcement in the engine every tool queries through, with one policy for it and for any rewrite like this chapter's. With Trino, answer every operation the plugin asks about, set both the row-filter and column-mask URIs (without them it applies no filters or masks), and return row filters only from a decision that allows the read. Build tests from the request shapes in the plugin's source, not from its documentation's example policies.

此部分尚未翻譯,因此以英文呈現。

Query on someone's behalf

最近一次變動於第 2026.11.31 版

An assistant that answers questions from the warehouse is one more user of the layer, with a choice to make: whose identity does its query carry? The course assistant, assistant-1, has its own access, for reviews by consenting learners. When it answers a person, it must query as that person, so the answer holds exactly what they could read themselves. OWASP's guidance for language-model applications says the same: "Implement authorization in downstream systems rather than relying on an LLM to decide if an action is allowed or not" (OWASP LLM06:2025). The assistant itself belongs to Data Assistants in Practice; this section is about its queries.

instructor-17 asks the assistant how their courses' reviews look, and the assistant writes a query. The tool takes the user from the signed-in session, never from the model or the conversation, so no prompt can change who the query runs as:

assistant-on-behalf.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 通過
"""Runs the course assistant's review query as instructor-17 and as assistant-1, through a tool that takes the user fromthe signed-in session. The prose's point rests on the reviews-behind-it lines for each user."""from lib import policyfrom lib.catalog import open_catalogfrom lib.enforce import Enforcerplatform = Enforcer(open_catalog(["crm.learners", "learning.reviews"]))courses = set(policy.data_document()["platform"]["instructor_courses"]["17"])     # instructor-17's own coursesdef query_tool(session_user: str):    """The tool the course assistant calls to query the warehouse. The model writes the SQL; the user it runs as comes    from the signed-in session, never from the model or the conversation."""    # the user is the session's; the SQL is all the model contributes    return lambda sql: platform.sql(sql, user=session_user).fetchall()# instructor-17 asks the assistant how their courses' reviews look. The assistant writes this query.question = "select course_id, count(*) as reviews, round(avg(rating), 2) as rating from learning.reviews group by all"for runs_as in ["instructor-17", "assistant-1"]:    answer = query_tool(runs_as)(question)    print(f"run as {runs_as}, courses in the answer:", len(answer))    print(f"run as {runs_as}, reviews behind it:", sum(reviews for _, reviews, _ in answer))    # each answer row is (course, reviews, rating), so row[0] is the course    mine = [row for row in answer if row[0] in courses]    print(f"run as {runs_as}, instructor-17's reviews behind it:", sum(reviews for _, reviews, _ in mine))
輸出python assistant-on-behalf.py
run as instructor-17, courses in the answer: 6
run as instructor-17, reviews behind it: 69
run as instructor-17, instructor-17's reviews behind it: 69
run as assistant-1, courses in the answer: 130
run as assistant-1, reviews behind it: 436
run as assistant-1, instructor-17's reviews behind it: 7

Whose identity the query carries decides what the answer covers. The assistant's own identity is wrong both ways: it shows the instructor what they may not see, and hides what they may. Run as instructor-17, the answer covers their 6 courses and 69 reviews. Run as the assistant, it covers 130 courses, most of them other instructors', and only 7 of the instructor's own reviews, the ones whose authors consented to AI training. The same holds for retrieval: an assistant grounded on governed text needs "permission-aware vector and embedding stores" (OWASP LLM08:2025).

Working rule: Run an assistant's queries as the person it answers, with that identity taken from their authenticated session, and let the layer apply that person's decision. Use the assistant's own identity only for work it does for no one in particular, such as a nightly summary of consented reviews for the course pages, and record both identities in the audit log. Whoever builds the assistant's training set queries as themselves, with the purpose ai-training, as chapter 9 does.

正式環境中會出什麼問題

最近一次變動於第 2026.11.27 版

強制執行層的失敗方式,是放行了一個沒有人測試過的讀取,而且這些失敗大多看起來像正確的答案:

  • SELECT * 從結構描述中缺少的資料表漏了過去。qualify 不會展開星號,也不會引發任何錯誤。凡是目錄中沒有的資料表一律拒絕,並且在目錄變動時重建結構描述。
  • **聚合結果洩漏小型群組。**只有一人的儲存格會指出該人是誰,而不同報表之間的差異,連通過任何最小值門檻的儲存格也能指出其中的人,如報表範例所示。請設定最小值,並移除依讀取者無權讀取的屬性所做的細分。
  • **連接與複製會讓被遮罩的欄位重新出現。**擁有完整存取權的管線,例如寫入 marketing.audience 的那一條,會把 email 複製到只受自身分類保護的資料表中。若未分類,該複本對所有人隱藏,如誰看得到什麼的測試所示;若被誤分類為 internal,則任何可讀取該資料表的人都看得到。第 4 章沿著血緣傳遞分類,以攔截這種情況。未遮罩的欄位同樣會透過連接重新識別出身分:Sweeney 發現,在 1990 年人口普查資料中,美國人口有 87% 可能僅憑郵遞區號、性別與出生日期就成為唯一(Sweeney, 2002);Golle 重新計算後,在同一份 1990 年資料上得到 61%,在 2000 年資料上得到 63%(Golle, 2006)。Rocher 等人根據以人口普查資料擬合的模型估計,15 項人口統計屬性即可讓麻薩諸塞州 99.98% 的人成為唯一(Rocher et al., 2019)。
  • 直接讀取檔案會繞過改寫。read_parquet、FROM 中的路徑,以及資料表函式,都能在不指名資料表的情況下讀取資料。請在改寫中拒絕它們,並關閉 enable_external_access,之後 DuckDB 不會再讓它重新開啟:「Cannot enable external access while database is running」。lock_configuration 會讓其他所有設定維持在該層設定的狀態;請在該層的連線完全設定好之後再設定它(Securing DuckDB),而不是在建立連線時就設定,因為第 8 章是在那之後才啟動 DuckDB 的查詢日誌。能在儲存空間中讀取倉儲檔案的人無須使用 SQL,因此儲存空間的權限也是強制執行的一部分。
  • **識別字的大小寫決定名稱會比對到什麼。**若某一層在比對名稱前沒有先正規化,"LEARNERS" 就能觸及原始資料表,因為 DuckDB 會將它解析為 learners,如第一節所示。在比對任何名稱之前,請先以執行查詢的引擎所用的方言進行正規化。
  • **錯誤訊息會印出值。**DuckDB 的轉型錯誤會包含該值。資料表改寫使它成為被遮罩的值,而每個欄位上的閘門讓被丟棄的資料列無法被觸及,但除非有東西加以阻擋,訊息仍會傳到使用者手中。請在服務邊界回傳通用錯誤,如 lib.enforce.answer 所做的那樣,並將引擎的訊息記錄在只有平台團隊讀得到的地方,與稽核日誌分開;第 8 章讓稽核日誌不含任何值。PostgreSQL 警告,即使有安全屏障,執行計畫與時間差仍然是隱蔽通道(PostgreSQL 18, Rules and Privileges)。
  • DuckDB 不是安全邊界。「DuckDB 以執行它的使用者之完整權限來執行 SQL,就像 shell 或指令碼直譯器一樣」(Securing DuckDB)。同一個行程內的改寫,只有在使用者無法透過任何其他途徑觸及 DuckDB 時,才能保護資料。請把本章的這一層視為 Trino 等引擎所強制執行內容的受測模型,而在正式環境中則在那裡強制執行。

本章的決定

最近一次變動於第 2026.11.27 版
  • 強制執行取代的是資料表,而不是欄位。 每個目錄資料表都會成為由決策產生的子查詢;改寫欄位參照只用來說明這種做法會洩漏資訊。
  • 這一層採取失效即拒絕(fail closed)的原則。 它的來源只允許目錄資料表的允許清單、作用範圍內的 CTE 與子查詢。它會拒絕具有副作用的函式、參數,以及單一查詢以外的任何內容。查詢未讀取的欄位會被定型為 NULL,而查詢讀取的欄位則受列篩選條件控管。
  • 使用者只會從 answer 收到一則通用的錯誤訊息,DuckDB 的訊息則交給平台團隊。
  • 遮罩集中在同一處以 SQL 表達。 政策只指定遮罩的名稱,由 lib.enforce 撰寫對應的 SQL。假名以只有這一層持有的密鑰產生,並以參數傳入。
  • 列篩選條件由資料建構而成, 其中的值以常值表示。同意狀態是一個半連接(semi-join),由這一層以自己的存取權限執行,未知的同意狀態視為不同意。
  • 誰能看到什麼,以查詢的方式測試: 第 5 章的案例在每次變更時都會透過這一層執行。
  • 報表會保留人數低於最小群組大小的群組不顯示, 該值為 11,設定於 governance/enforcement.toml,而且不會依讀者無權讀取的屬性進行細分。
  • 助理以其回答對象的身分進行查詢, 身分取自工作階段。
  • 在正式環境中,由引擎負責強制執行: 使用 Trino 搭配 OPA,並採用同一份政策,兩個 URI 都要設定,列篩選條件只依據被允許的決策來回應。一個防護層的安全程度,取決於它的測試。

本章,於本版次中

版次 2026.11.31範例於 2026年11 月 19 通過測試本章 16 個範例 個範例中有 16 個通過

譯自英文書,版次 2026.11.31。

引用本節

格式

各種格式皆引用英文原書,本譯本即依循該原書。

每種格式都會標示本版。其連結會開啟目前版次,節標題會說明該節內文最後一次變動的時間。存取日期與擷取日期即為你開啟此頁的當日。

本頁內容

第 6 章,共 10 章: 對查詢強制執行政策

  1. 找出查詢讀取的每一個資料表與欄位
  2. Allow only catalog tables, CTEs and subqueries
  3. 遮罩欄位參照會造成洩漏
  4. 以過濾並遮罩的子查詢取代每個資料表
  5. Write each mask as SQL in one place
  6. 從決策的資料建立資料列篩選條件
  7. 讓每個欄位都取決於資料列篩選條件
  8. 以執行查詢來測試誰能看到什麼
  9. Hold back small groups in reports
  10. Hand the same policies to an engine that enforces them
  11. Query on someone's behalf
  12. 正式環境中會出什麼問題
  13. 本章的決定

返回頂端所有章節