MySQLのViewを活用した安全なマルチテナントの実現方法

2024/7/11 db tech showcaseで登壇した資料
https://www.db-tech-showcase.com/2024/

Transcript

1. © Findy Inc. 1 MySQLのViewを活⽤した 安全なマルチテナントの実現⽅法 2024.07.11 db tech showcase 2024 Tokyo 浜⽥ 直⼈ Naoto Hamada (ham)
2. © Findy Inc. 2 開発⽣産性が向上する⽅法を探求しているエンジニア︕ Ruby / Rails / React / TypeScript / AWS Agile / DevOps / Developer Productivity / DevEx Stock Investment 浜⽥ 直⼈ Naoto Hamada (ham) @hamchance0215
3. 挑戦するエンジニアの プラットフォームをつくる。 テクノロジーによる社会変⾰の時代に最も必要なことは、エンジニアの可能性を拡げることです。 Findyは、アルゴリズムとヒューマニティの融合によって、 すべてのエンジニアが不安なく挑戦できる世界共通のプラットフォームをつくります。 個⼈のチャンスを⽣み出し、組織の⽣産性を向上させ、社会の⼈材資産を好循環させる。 エンジニアプラットフォームが、デジタル社会の発展を加速していきます。 ビジョン © Findy Inc. 3
4. © Findy Inc. ファインディが展開するエンジニアプラットフォーム サービス紹介 ToC / ToB 正社員エンジニアの採⽤ 約12万⼈のエンジニアと880社以上のテッ ク企業をマッチング。 マッチングサービス フリーランスエンジニアの採⽤ 5万⼈以上のフリーランスエンジニアの成 功報酬型の⼈材紹介サービス。 外国籍エンジニアの採⽤ インドを中⼼とした海外のハイスキルエン ジニアと⽇本企業をマッチング。 SaaS / ToB エンジニア組織の⾒える化 GitHubやJiraを解析し、エンジニア組織の ⾒える化と⽣産性向上をサポート。 組織分析SaaS ToC / ToB 開発ツールのレビューサイト 実際に利⽤している企業の声を元に、開発 ツールの導⼊や検討に必要な情報を集約。 企業の技術選定をサポート。 開発ツールメディア ˞
   ֤छ਺஋͸ɺ೥݄࣌఺ͷ'JOEZస৬ɺ'JOEZ
   'SFFMBODFɺ'JOEZ
   5FBN ɺ'JOEZ
   (MPCBMͷαʔϏεͷྦྷܭͰͷࣾ਺ٴͼొ࿥ऀ਺Ͱ͢ɻ ͳ͓ɺࣾຢ͸໊ͷํ͕ෳ਺ͷαʔϏεʹొ࿥͍ͯ͠Δ৔߹͸ɺͦͷαʔϏεͷ਺ʹԠͯ͡ෳ਺ͷΧ΢ϯτΛ͍ͯ͠·͢ɻ β 版 4
5. © Findy Inc. 5 Findy Team+(チームプラス)とは︖ 開発⽣産性の可視化、開発プロセスの伸びしろの発⾒、継続的 な改善をサポート 5 ⽣産性可視化 ⽣産性向上 事業開発スピード加速 (開発スピードの向上により、仮説検証スピードも加速) 開発プロセス改善 (開発フロー･配置･ツールの伸びしろを可視化・最適化) ⽂化づくり・⾃⼰組織化 (メンバーの⾃発的な改善促進、改善を称賛する⽂化作り) データ 連携 Engineer Engineer 開発組織ブランディング (エンジニアは、開発⽣産性が⾼い組織で働きたい) Recruit Biz
6. © Findy Inc. 6 Agenda - マルチテナントについて - これまでのマルチテナント - 移⾏先アーキテクチャの検討
7. © Findy Inc. マルチテナントについて 7
8. © Findy Inc. 8 マルチテナントについて - 単⼀のソフトウェアインスタンスが複数の独⽴したテナン トによって共有される仕組みを指します。 アプリケーション テナントA テナントC テナントB
9. © Findy Inc. 9 データの分離⽅法 - データの分離⽅法には様々な⽅法があります。 - 単⼀データベース、単⼀スキーマ - 単⼀データベース、複数スキーマ - 複数データベース
10. © Findy Inc. 10 単⼀データベース、単⼀スキーマ - テナント間で同⼀テーブルを利⽤するが、tenant_idなどで データを区別する スキーマ テナントA テナントC テナントB テーブル id tenant_id …
11. © Findy Inc. 11 単⼀データベース、単⼀スキーマ メリット - テナントの数に関わらず1つのDBやスキーマで運⽤でき、 各テーブルも1つずつ準備すれば良いので低コスト デメリット - 任意のテナントの利⽤率が⾼まった場合、他のテナントの DBリソースも影響を受ける - テナント分離の制御をアプリケーションで⾏う必要がある - SQLで常にtenant_idの条件を付与する必要がある
12. © Findy Inc. 12 単⼀データベース、複数スキーマ テナントA スキーマ - テナントごとに専⽤スキーマを持つ テナントB スキーマ テナントC スキーマ
13. © Findy Inc. 13 単⼀データベース、複数スキーマ メリット - テナントの数に関わらず1つのDBで運⽤でき低コスト - テナント分離の制御を接続先スキーマの制御のみで⾏うこ とができる デメリット - スキーマごとに同⼀テーブルを準備する必要がある - 任意のテナントの利⽤率が⾼まった場合、他のテナントの DBリソースも影響を受ける
14. © Findy Inc. 14 複数データベース テナントA DB - テナントごとに専⽤データベースを持つ テナントC DB テナントB DB
15. © Findy Inc. 15 複数データベース メリット - テナント分離の制御を接続先データベースの制御のみで⾏ うことができる - 任意のテナントの利⽤率が⾼まった場合、他のテナントの DBリソースは影響を受けない デメリット - テナントごとにデータベースを準備する必要があるため運 ⽤コストや費⽤が⾼い - データベースごとに同⼀テーブルを準備する必要がある
16. © Findy Inc. 16 まとめ データ 分離 運⽤ リソース 独⽴ 費⽤ なし × ◯ × ◯ 単⼀DB 単⼀スキーマ △ ◯ × ◯ 単⼀DB 複数スキーマ ◯ × × ◯ 複数DB ◯ × ◯ × データ分離: データ分離できていると◯ 運⽤: 運⽤コストが低いと◯ リソース独⽴: DBリソースが独⽴と◯ 費⽤: DBのランニングコストが安いと◯
17. © Findy Inc. 17 まとめ データ 分離 運⽤ リソース 独⽴ 費⽤ なし × ◯ × ◯ 単⼀DB 単⼀スキーマ △ ◯ × ◯ 単⼀DB 複数スキーマ ◯ × × ◯ 複数DB ◯ × ◯ ×
18. © Findy Inc. 18 まとめ データ 分離 運⽤ リソース 独⽴ 費⽤ なし × ◯ × ◯ 単⼀DB 単⼀スキーマ △ ◯ × ◯ 単⼀DB 複数スキーマ ◯ × × ◯ 複数DB ◯ × ◯ ×
19. © Findy Inc. 19 まとめ データ 分離 運⽤ リソース 独⽴ 費⽤ なし × ◯ × ◯ 単⼀DB 単⼀スキーマ △ ◯ × ◯ 単⼀DB 複数スキーマ ◯ × × ◯ 複数DB ◯ × ◯ ×
20. © Findy Inc. 20 まとめ データ 分離 運⽤ リソース 独⽴ 費⽤ なし × ◯ × ◯ 単⼀DB 単⼀スキーマ △ ◯ × ◯ 単⼀DB 複数スキーマ ◯ × × ◯ 複数DB ◯ × ◯ ×
21. © Findy Inc. 21 まとめ データ 分離 運⽤ リソース 独⽴ 費⽤ なし × ◯ × ◯ 単⼀DB 単⼀スキーマ △ ◯ × ◯ 単⼀DB 複数スキーマ ◯ × × ◯ 複数DB ◯ × ◯ × 現状はこちら
22. © Findy Inc. これまでのマルチテナント 22
23. © Findy Inc. 23 これまでのマルチテナント - Findy Team+では、テナント間のデータ漏洩のセキュリテ ィリスクを考慮しつつ、運⽤コストや費⽤とのバランスを 加味して、アプリケーションレイヤーでマルチテナントの 制御をしています。 テーブル id tenant_id … ・全テーブルにtenant_idを付与 ・SQLでtenant_idの条件を必ず付与 することで、他のテナント情報が混ざ ることを防止
24. © Findy Inc. 24 アプリケーションでの制御⽅法 - SQLにtenant_idを付与する制御はライブラリを利⽤ # グローバル変数にテナントを指定 # それ以降に発行されるSQLは `tenant_id=1`が付与される current_tenant_id = 1 User.where(id: 10) # SELECT * FROM users WHERE tenant_id = 1 AND id = 10 # 例えば、id=10がtenant_id=1のユーザーではない場合、取得 できない # 他テナントの情報漏洩を防ぐことができる
25. © Findy Inc. 25 課題 - ライブラリを使って⾃動的にテナント分離を⾏っているの で、テナントの指定漏れが発⽣することはほぼないが、稀 にテナントを超えたデータリードが発⽣することがある
26. © Findy Inc. 26 課題 - 指定漏れが発⽣する実装パターン - 複雑なSQLの場合、tenant_idの条件が適切に効かない - ORなどでtenant_idの条件が無効化される - tenant_id = 1 AND name=ʻʼ OR name= ʻwordʼ
27. © Findy Inc. 27 課題 - 指定漏れが発⽣する実装パターン - ⾮同期処理にグローバル変数に指定したtenant_idが引 き継がれず指定漏れ
28. © Findy Inc. 28 課題 - 指定漏れが発⽣する実装パターン - ⾮同期処理にグローバル変数に指定したtenant_idが引 き継がれず指定漏れ より強固なアプリケーションにするため 別のアーキテクチャを検討
29. © Findy Inc. 移⾏先アーキテクチャの検討 29
30. © Findy Inc. 30 移⾏先アーキテクチャの候補 - 複数スキーマ / 複数データベースへ移⾏ - Row Level Securityの適⽤ - Viewの活⽤
31. © Findy Inc. 31 複数スキーマ / 複数データベースへ移⾏ データ 分離 運⽤ リソース 独⽴ 費⽤ なし × ◯ × ◯ 単⼀DB 単⼀スキーマ △ ◯ × ◯ 単⼀DB 複数スキーマ ◯ × × ◯ 複数DB ◯ × ◯ ×
32. © Findy Inc. 32 複数スキーマ / 複数データベースへ移⾏ - DBの接続先で制御できるので、設定ミスなどを除きアプリ ケーションから他テナントにアクセスできない データ 分離 運⽤ リソース 独⽴ 費⽤ 単⼀DB 複数スキーマ ◯ × × ◯ 複数DB ◯ × ◯ ×
33. © Findy Inc. 33 複数スキーマ / 複数データベースへ移⾏ - テナントごとにテーブルを準備する必要があり、スキーマ マイグレーションの運⽤コストが増⼤ データ 分離 運⽤ リソース 独⽴ 費⽤ 単⼀DB 複数スキーマ ◯ × × ◯ 複数DB ◯ × ◯ ×
34. © Findy Inc. 34 複数スキーマ / 複数データベースへ移⾏ - 複数DBの場合、DBリソースが独⽴するため、付随効果と してテナントごとにリソースが独⽴するメリットがある - その分、費⽤が⼤幅に⾼まる データ 分離 運⽤ リソース 独⽴ 費⽤ 単⼀DB 複数スキーマ ◯ × × ◯ 複数DB ◯ × ◯ ×
35. © Findy Inc. 35 複数スキーマ / 複数データベースへ移⾏ まとめ - テナント間のデータの独⽴と運⽤コスト・費⽤のトレード オフ - 複数データベースの場合、リソースが独⽴するメリット があるので、リソース共有に課題が発⽣したら採⽤する メリットが⼤きくなる - テナントごとにデータ分離するためデータ移⾏が必要
36. © Findy Inc. 36 複数スキーマ / 複数データベースへ移⾏ まとめ - テナント間のデータの独⽴と運⽤コスト・費⽤のトレード オフ - 複数データベースの場合、リソースが独⽴するメリット があるので、リソース共有に課題が発⽣したら採⽤する メリットが⼤きくなる - テナントごとにデータ分離するためデータ移⾏が必要 現時点では データ分離のメリットより 運⽤コスト・費⽤の増加が重いので 不採⽤
37. © Findy Inc. 37 Row Level Securityの適⽤ - データベーステーブルの⾏単位のアクセス制御をデータベ ースレイヤーで⾏うことができる機能 - 特定のユーザーやロールに対して、どの⾏にアクセスでき るのかを制御できる
38. © Findy Inc. 38 Row Level Securityの適⽤ - 適⽤したポリシーに合致したデータのみ取得できる # ポリシーを指定 CREATE POLICY user_policy ON users USING (tenant_id=current_setting(’current_tenant_id’)); User.where(id: 10) # SELECT * FROM users WHERE id = 10 # idしか指定していないが、tenant_idがcurrent_tenant_idと一致 しているレコードしか取得できない
39. © Findy Inc. 39 Row Level Securityの適⽤ - MySQLにはRow Level Securityがない - Row Level Securityに対応したDBへ移⾏が必要 - 例えばPostgreSQL - PostgreSQLへの移⾏するか︖ - Ruby on RailsのActiveRecordがDB特有の制御は隠蔽 しているが、それでもDB移⾏はハードルが⾼い - パフォーマンス観点 - SQLの細かな違い
40. © Findy Inc. 40 Row Level Securityの適⽤ - MySQLにはRow Level Securityがない - Row Level Securityに対応したDBへ移⾏が必要 - 例えばPostgreSQL - PostgreSQLへの移⾏するか︖ - Ruby on RailsのActiveRecordがDB特有の制御は隠蔽 しているが、それでもDB移⾏はハードルが⾼い - パフォーマンス観点 - SQLの細かな違い PostgreSQLへの移⾏が重いので 不採⽤
41. © Findy Inc. 41 Viewの活⽤ - MySQLでRow Level Securityのような仕組みを実現するた めにViewを活⽤ - テナントで絞り込んだViewを作り、View経由でアクセスす ることで別テナントのデータが混ざることを防ぐ
42. © Findy Inc. 42 Viewの活⽤ - Viewを活⽤する⽅法を検討するにあたり、こちらの記事を 参考にさせていただきました🙏 Row-Level Security in MariaDB 10 Protect Your Data https://mariadb.com/ja/resources/blog/protect-your-data-row-level-security-in-mariadb-10-0/
43. © Findy Inc. 43 Viewの活⽤ テーブル ア プ リ ケ % シ ' ン View tenant_idで 絞ったViewを ⽣成 View 参照 - 活⽤イメージ
44. © Findy Inc. 44 Viewの活⽤ - 既存テーブルからtenant_idで絞り込んだViewを作成 - current_tenant_id():現在のtenant_idを返却する関数 CREATE SQL SECURITY DEFINER VIEW viewdb.users AS SELECT * FROM tabledb.users WHERE tenant_id = tabledb.current_tenant_id() WITH CHECK OPTION;
45. © Findy Inc. 45 Viewの活⽤ - 論理的なデータベーススキーマ(viewdb/tabledb)を分離す ることで、同名でViewを作成 - Viewを同名で作ることで既存のSQLをそのまま使える CREATE SQL SECURITY DEFINER VIEW viewdb.users AS SELECT * FROM tabledb.users WHERE tenant_id = tabledb.current_tenant_id() WITH CHECK OPTION;
46. © Findy Inc. 46 Viewの活⽤ - View経由でCUDも可能 - ViewというのでRしかできないと思っていた... - CUDもできるのでアプリケーションからはViewのデー タベーススキーマに接続するだけで良い CREATE SQL SECURITY DEFINER VIEW viewdb.users AS SELECT * FROM tabledb.users WHERE tenant_id = tabledb.current_tenant_id() WITH CHECK OPTION;
47. © Findy Inc. 47 Viewの活⽤ - CHECK OPTIONをつけることでViewのWHEREに合致しな いデータのWriteを禁⽌ - より安全にWriteを制御することができる CREATE SQL SECURITY DEFINER VIEW viewdb.users AS SELECT * FROM tabledb.users WHERE tenant_id = tabledb.current_tenant_id() WITH CHECK OPTION;
48. © Findy Inc. 48 Viewの活⽤ - Viewを作成する必要はあるが、既存テーブルはそのまま使 えるのでデータ移⾏が不要 - アプリケーションコードをほぼ変える必要がない - テーブルスキーマ変更時にViewへ反映 - 接続先をViewに向ける - テーブル名とView名を揃えることで、テーブルに発 ⾏していたSQLがそのまま使える - Viewは実体がないのでDB費⽤がほぼ変わらない
49. © Findy Inc. 49 Viewの活⽤ データ 分離 運⽤ リソース 独⽴ 費⽤ なし × ◯ × ◯ 単⼀DB 単⼀スキーマ △ ◯ × ◯ 単⼀DB 複数スキーマ ◯ × × ◯ 複数DB ◯ × ◯ × View活⽤ ◯ △ × ◯
50. © Findy Inc. 50 Viewの活⽤ データ 分離 運⽤ リソース 独⽴ 費⽤ なし × ◯ × ◯ 単⼀DB 単⼀スキーマ △ ◯ × ◯ 単⼀DB 複数スキーマ ◯ × × ◯ 複数DB ◯ × ◯ × View活⽤ ◯ △ × ◯ Findy Team+では Viewを活⽤する⽅法を採⽤︕ 現在、移⾏中︕︕ いくつか問題は発⽣したものの パフォーマンス劣化など 致命的な問題は発⽣していません︕︕
51. © Findy Inc. 51 Viewの活⽤ - View移⾏中に発⽣した課題 - View経由でテーブルスキーマが正しく取得できない ※テーブルスキーマ情報はRailsで利⽤するために必要 - auto_incrementの情報が失われる - Primary Keyが正しく取得できない
52. © Findy Inc. 52 Viewの活⽤ - View移⾏中に発⽣した課題 - View経由でテーブルスキーマが正しく取得できない ※テーブルスキーマ情報はRailsで利⽤するために必要 - auto_incrementの情報が失われる - Primary Keyが正しく取得できない Railsにパッチをあて 正しいスキーマ情報を 取得できるようにすることで回避
53. © Findy Inc. 53 Viewの活⽤ テーブル ア プ リ ケ % シ ' ン View tenant_idで 絞ったViewを ⽣成 View 参照 - 活⽤イメージ(再掲)
54. © Findy Inc. 54 プロダクトエンジニア ・エンジニアをターゲットとした プロダクト開発 ・技術はモダンが当たり前 ・⾼い開発⽣産性 ・開発者体験の追求 ・CI/CDや⾃動テストが 整備された開発環境 We are hiring！