前置き
こんにちは、日本で一番 SQL が書ける俳優 @yebihara です。
ええ、俳優です。
最近、マルチテナント SaaS におけるセキュアなデータ管理というコンテキストで Row Level Security （RLS） が注目されてきているような印象を受けています。
自分はただの売れない俳優なので勘違いかもしれませんが。
直近で見かけたのはたしかこれ。
RLS が何なのかはこの記事では説明しないので、上記スライドなどを参照してください。
さて、RLS が利用できる RDBMS は限られています。
自分が知る限り、最初に RLS を実装したメジャーな RDBMS は Oracle で、２０００年頃に Virtual Private Database (VPD) という機能名でリリースされました。もしかしたらそれ以前からあった Oracle Label Security というやつも RLS っぽいところがあったのかもしれないけど。
その後、いつかは知りませんが PostgreSQL も RLS を実装し、現在公開されている RLS の事例はほとんどが PostgreSQL のもののようです。
Web サービスでよく使われるもうひとつの RDBMS と言えば MySQL ですが、残念ながら RLS は備えていません。
が、いくつかの機能をうまく組み合わせると MySQL でも RLS 相当のことができます。
このアイディアは７、８年前に思いついて、当時勤めていた会社内では共有していましたが、今さらながら紹介します。今まで公開していなかったのは単に忙しかったからです。
本当は先月、さっきのスライドを見てすぐにこの記事を書こうかとも思いましたが、そうしなかったのは舞台の稽古で忙しかったからです。３日前にようやく千秋楽を迎えて時間ができました。
なお着想当時は MySQL 5.7 とかを使っていたと思いますが、今回 MySQL 8.3 で改めて検証しても特に違いはありませんでした。
テストスキーマ & テストデータ
人事データを管理するマルチテナント SaaS でも作っていると仮定して、こんな感じの従業員テーブルを作ってみましょう。
CREATE TABLE employees (
id INT PRIMARY KEY, -- ID (自動生成の連番)
tenant_id INT NOT NULL, -- テナントID
employee_no VARCHAR(10), -- 社員番号
name VARCHAR(100) NOT NULL, -- 氏名
name_kana VARCHAR(100) NOT NULL, -- 氏名（カナ）
date_of_birth DATE -- 生年月日
);
そしてテストデータを用意する。
| id | tenant_id | employee_no | name | name_kana | date_of_birth |
|---|---|---|---|---|---|
| 1 | 100 | A001 | 弱竹 かぐや | ナヨタケ カグヤ | 1996-07-09 |
| 2 | 100 | A002 | 石塚 陽 | イシヅカ アキラ | 1995-09-26 |
| 3 | 100 | A003 | 倉持 康太 | クラモチ コウタ | 1997-03-07 |
| 4 | 100 | A004 | 井上 智基 | イノウエ トモキ | 1998-05-13 |
| 5 | 100 | A005 | 安部 修 | アベ オサム | 1997-01-06 |
| 6 | 100 | A006 | 大野 一郎 | オオノ イチロウ | 1998-09-15 |
| 7 | 200 | 雨谷 美沙子 | アメヤ ミサコ | 2000-11-18 | |
| 8 | 200 | 倉持 このみ | クラモチ コノミ | 2003-10-22 | |
| 9 | 200 | 忠野 勇次 | タダノ ユウジ | 1973-11-18 | |
| 10 | 200 | 門田 直樹 | カドタ ナオキ | 2006-10-12 | |
| 11 | 200 | 天井 かぐすけ | テンジョウ カグスケ | 2000-10-03 | |
| 12 | 200 | 宮迫 詩乃 | ミヤサコ シノ | 1990-09-09 |
このテーブルには２つのテナント企業(tenant_id が 100 と 200) についてそれぞれ６名ずつ、計１２名の従業員情報が保存されています。
なお人名は僕が先月出演した舞台「バンブー・サマー2024」の登場人物から拝借しました。
試したい人のために INSERT 文も載せておきます。
INSERT INTO employees VALUES
(1, 100, 'A001', '弱竹 かぐや', 'ナヨタケ カグヤ', '1996-07-09'),
(2, 100, 'A002', '石塚 陽', 'イシヅカ アキラ', '1995-09-26'),
(3, 100, 'A003', '倉持 康太', 'クラモチ コウタ', '1997-03-07'),
(4, 100, 'A004', '井上 智基', 'イノウエ トモキ', '1998-05-13'),
(5, 100, 'A005', '安部 修', 'アベ オサム', '1997-01-06'),
(6, 100, 'A006', '大野 一郎', 'オオノ イチロウ', '1998-09-15'),
(7, 200, NULL, '雨谷 美沙子', 'アメヤ ミサコ', '2000-11-18'),
(8, 200, NULL, '倉持 このみ', 'クラモチ コノミ', '2003-10-22'),
(9, 200, NULL, '忠野 勇次', 'タダノ ユウジ', '1973-11-18'),
(10, 200, NULL, '門田 直樹', 'カドタ ナオキ', '2006-10-12'),
(11, 200, NULL, '天井 かぐすけ', 'テンジョウ カグスケ', '2000-10-03'),
(12, 200, NULL, '宮迫 詩乃', 'ミヤサコ シノ', '1990-09-09')
;
実現したいこと
さて、ここで Web アプリケーションの挙動を考えると、ログイン中のユーザーの所属テナントID が 100 なら最初の６名のみ、テナントIDが 200 なら次の６名のみしか検索できないようにしたいわけですね。
RLS がない場合、これはアプリケーションコードから実行する SELECT 文に WHERE tenant_id = 100 のような条件を付加するのが普通です。でもこれがすべてのクエリーで漏れなく行われることを保証するのが大変だし、万が一どこかで漏れると他社のデータが見えてしまう大事故につながるのが怖い、という話ですね。
ビューとユーザー定義変数の利用
Oracle の VPD の場合は、事前に RLS ポリシーを定義しておくと、SELECT 文の実行時にコンテキストに応じた適切な WHERE 条件を自動的に付加してくれるという機能でした（少なくとも昔は）。
似たようなことを MySQL でも実現するためにビューを利用してみることにしましょう。
次のようなビューを作成することを考えてみます。
CREATE OR REPLACE VIEW employees_rls AS
SELECT * FROM employees WHERE tenant_id = @current_tenant_id;
ここで @current_tenant_id というのは MySQL のユーザー定義変数というやつです。
事前に SET @current_tenant_id = 100 のような SQL 文を実行しておくと、@current_tenant_id でその値を参照できるというものです。
なお変数名には任意のものを指定できます。
そしてこのビューに対してアプリケーションからこんな感じの SQL を実行することを想像してみてください。
SET @current_tenant_id = 100;
SELECT * FROM employees_rls;
tenant_id が 100 の従業員レコードのみが返ってきそうですね！
・・・が、残念ながらそううまくはいきません。
先ほどのビューを作成しようとした時点で、
View's SELECT contains a variable or parameter
というエラーが発生してしまいます。
どうやらビュー定義の SELECT 文からはユーザー定義変数を参照できないようです。
しかし諦めるのはまだ早い。
まずはユーザー定義変数の値を返すだけのファンクションを作成し、
CREATE FUNCTION current_tenant_id() RETURNS INT NO SQL RETURN @current_tenant_id;
ビュー定義ではユーザー定義変数の代わりにそのファンクションを参照する。
CREATE OR REPLACE VIEW employees_rls AS
SELECT * FROM employees WHERE tenant_id = current_tenant_id();
なんとエラーが発生しない！
動作確認
では動作を確認してみましょう
まずはテナントID を 100 にセット。
mysql> SET @current_tenant_id = 100;
Query OK, 0 rows affected (0.00 sec)
そして SELECT 文を WHERE 句なしで実行。
mysql> select * from employees_rls;
+----+-----------+-------------+-----------+-------------+---------------+
| id | tenant_id | employee_no | name | name_kana | date_of_birth |
+----+-----------+-------------+-----------+-------------+---------------+
| 1 | 100 | A001 | 弱竹 かぐや | ナヨタケ カグヤ | 1996-07-09 |
| 2 | 100 | A002 | 石塚 陽 | イシヅカ アキラ | 1995-09-26 |
| 3 | 100 | A003 | 倉持 康太 | クラモチ コウタ | 1997-03-07 |
| 4 | 100 | A004 | 井上 智基 | イノウエ トモキ | 1998-05-13 |
| 5 | 100 | A005 | 安部 修 | アベ オサム | 1997-01-06 |
| 6 | 100 | A006 | 大野 一郎 | オオノ イチロウ | 1998-09-15 |
+----+-----------+-------------+------------------+------------------------+---------------+
6 rows in set (0.00 sec)
期待通り tenant_id = 100 の従業員レコードのみが検索されました。
次にテナントID を ２00 にセット。
mysql> SET @current_tenant_id = 200;
Query OK, 0 rows affected (0.00 sec)
そして先ほどと全く同じ SELECT 文を実行。
mysql> select * from employees_rls;
+----+-----------+-------------+------------+----------------+---------------+
| id | tenant_id | employee_no | name | name_kana | date_of_birth |
+----+-----------+-------------+------------+----------------+---------------+
| 7 | 200 | NULL | 雨谷 美沙子 | アメヤ ミサコ | 2000-11-18 |
| 8 | 200 | NULL | 倉持 このみ | クラモチ コノミ | 2003-10-22 |
| 9 | 200 | NULL | 忠野 勇次 | タダノ ユウジ | 1973-11-18 |
| 10 | 200 | NULL | 門田 直樹 | カドタ ナオキ | 2006-10-12 |
| 11 | 200 | NULL | 天井 かぐすけ | テンジョウ カグスケ | 2000-10-03 |
| 12 | 200 | NULL | 宮迫 詩乃 | ミヤサコ シノ | 1990-09-09 |
+----+-----------+-------------+---------------------+------------------------------+---------------+
6 rows in set (0.01 sec)
今度は tenant_id = 200 の従業員レコードのみが検索された！
ちなみに事前に @current_tenant_id へのテナントIDの設定を忘れるとどうなるか？
mysql> select * from employees_rls;
Empty set (0.00 sec)
１件も返されません。安全でいいですね！
これって実用的なの？
ただの俳優がそんなことを知っているはずがありません。
ビュー定義で直接的にユーザー定義変数を参照できないのには何か理由があるのかもしれないし、ストアドファンクションを利用して実質的に同じことをすることで、予期せぬ不都合が発生する可能性がないとは言えないでしょう。
SET コマンドを実行する方法は利用するWebアプリケーションフレームワーク・DBアクセスライブラリーによって異なるだろうし、ストアドファンクションの利用がクエリーパフォーマンスにどの程度の影響を与えるのかも調べていません。
そういうのはエンジニアの皆さんのほうでどうにかしてくださいませ。
おまけ
舞台「バンブー・サマー2024」が、4月14日(日)までアーカイブ配信されるらしいです。
6月くらいには DVD も発売されるらしいです。
自分は「竹チーム」のほうにいます。どれが @yebihara かは想像にお任せします。
ちなみにこれが売れても自分には何も還元されないので、収益目的の宣伝ではありません。