変更系 SQL 操作と許可リストの整理
SQL を書いて集計を回すツールが業務のなかで使われている場面はよくある。前回実行で作られた中間テーブルを落としてから新しい中間テーブルを作る、といった動きが、SQL アセットの中に自然に含まれている運用も多い。DROP と CREATE の組み合わせは中間テーブルの扱いとして標準的なパターンの一つで、日々の集計のなかでふつうに回っている。
一方で、DROP は対象を取り違えれば取り返しがつかない操作でもあり、ツール経由で通す対象を無条件に広げられない。今回、ツールの運用を見直す機会があり、ツール経由の DROP をどこまで通すかについて、設計の整理を行った。デフォルトを「通さない」に寄せ、通してよい対象だけを設定面で限定列挙する許可リスト方式に落とし込んだ過程を、以下に記録しておく。
どこまで安全側に倒すかの方針
許可リストの形を考える前に、そもそもどこまで安全側に倒すかという方針の検討があった。
このツールは業務内のオペレーションで使われ、利用者は限られたメンバーに限定される。仮にツールの想定外の使われ方が起きても、ログから当人を特定するところまでは確保されており、事後に追えること自体が抑止として働く。エンジニア側のリテラシーとしても、その仕組みを理解したうえで業務を進める前提が共有されており、無自覚な逸脱も起きにくい。意図的・無自覚を問わず、ツールの隙を突く方向の行為は、通常の運用で想定すべき範囲の外として扱える。
この前提が成り立たない環境、たとえば不特定多数からアクセスできる外部公開のサービスであれば、判断は変わる。安全側を最大化することの優先度がそのまま上がり、オペレーションの自由度を相応に削る選択も妥当になる。
業務内で使うツールの場合は、安全側に倒し過ぎるとオペレーションの手順が増えたり、運用そのものを組み替える必要が出たりする。日々の集計に対しての副作用が大きい。安全側を担保しつつ、業務の柔軟性を残す塩梅が、検討の起点になった。
検討した方式
具体的な方式としては、いくつかの案を比較した。
最初に検討したのは、テーブル名のパターン(たとえば tmp_ で始まる名前)で通す対象を決める案だった。命名規則が安定していれば追加作業は最小で済む。ただし、命名規則の揺れや、規則の対象外で実在する中間テーブルが、そのまま穴になる懸念があった。
DROP を通さない対象だけを列挙する拒否リスト方式は、明示されていない対象がすべて通る形になり、デフォルトの安全側が逆転する点で採らなかった。
DB 側の権限で操作を縛る案は、ツール経由かどうかに関わらず一貫して効く強みがあるが、許可範囲の運用が DB 管理者権限の側に分かれ、ツール側で事前に止める層も別途必要になる。単独の解にはならず、別レイヤーとして残しておく扱いになった。
最終的に採ったのは、DROP を通してよいテーブル名を明示的に列挙する許可リストの方式である。デフォルトを「通さない」に置き、通す対象だけを設定面で限定列挙する。新しいテーブルが追加されてもデフォルトの安全側が変わらず、許可範囲の現在地が設定ファイルに集約される点を優先した。
許可リストを設定として持つ
許可リストはコード内に埋め込まず、外部ファイルに設定として持っている。許可対象を増やす変更は設定差分として残る。減らす変更も同様に差分として残る。許可範囲の現在地と変更履歴が、コードの動きとは独立に追える。
許可リストを当初の想定より広げる必要が生じた場合も、一括で範囲を緩める変更は採らず、追加対象は1件ずつ設定に加えている。追加理由は、設定変更の差分と周辺記録に残している。
許可リストが照合する単位
ツール側の基本方針は、変更系の SQL 操作はすべて通さないとしたうえで、業務上必要な操作だけを必要な対象に限って通す。この方針のもとで、DROP に対する許可リストも、限定的な開け口のひとつとして位置づけられる。
許可リストの照合対象は DROP TABLE とした。照合は、スキーマ修飾名(schema.table)のレベルで行う。schema を省略した table 単独の表記、および許可リストに登録のないスキーマ配下の表記は、いずれも通さない。許可リストの側も、スキーマ修飾名で記載する運用にした。
非クォート識別子は、DB の識別子畳み込み規則に合わせて小文字に正規化したうえで照合する。ダブルクォートで括られた識別子は別扱いとし、表記を保持したまま許可リストと照合する。
スキーマ修飾名への展開と、クォート有無に応じた正規化規則の切り替えは、ツール側でまとめて適用している。許可リストのエントリも同じ正規化を通したうえで保持し、照合は許可リストへの所属判定で済ませている。
この方式が成り立つ前提
この方式は、SQL の実行経路がツールに集約されていることを前提にしている。同じデータに対して、ツールを経由しない経路が並行して存在する場合、許可リストは DROP 全体を覆う層にはならず、ツール経路の内側だけを覆う。ツール経路以外の DROP をどう扱うかは、ツール側の設定ではなく、権限の組み立てや実行経路の整理の側の論点になる。
川上 弘kawakamisekkei / 株式会社川上設計