Tweet
Tweet

Slide 1

Slide 1 text

© Findy Inc. 1 MySQLのViewを活⽤した 安全なマルチテナントの実現⽅法 2024.07.11 db tech showcase 2024 Tokyo 浜⽥ 直⼈ Naoto Hamada (ham)

Slide 2

Slide 2 text

© Findy Inc. 2 開発⽣産性が向上する⽅法を探求しているエンジニア︕ Ruby / Rails / React / TypeScript / AWS Agile / DevOps / Developer Productivity / DevEx Stock Investment 浜⽥ 直⼈ Naoto Hamada (ham) @hamchance0215

Slide 3

Slide 3 text

挑戦するエンジニアの プラットフォームをつくる。 テクノロジーによる社会変⾰の時代に最も必要なことは、エンジニアの可能性を拡げることです。 Findyは、アルゴリズムとヒューマニティの融合によって、 すべてのエンジニアが不安なく挑戦できる世界共通のプラットフォームをつくります。 個⼈のチャンスを⽣み出し、組織の⽣産性を向上させ、社会の⼈材資産を好循環させる。 エンジニアプラットフォームが、デジタル社会の発展を加速していきます。 ビジョン © Findy Inc. 3

Slide 4

Slide 4 text

© Findy Inc. ファインディが展開するエンジニアプラットフォーム サービス紹介 ToC / ToB 正社員エンジニアの採⽤ 約12万⼈のエンジニアと880社以上のテッ ク企業をマッチング。 マッチングサービス フリーランスエンジニアの採⽤ 5万⼈以上のフリーランスエンジニアの成 功報酬型の⼈材紹介サービス。 外国籍エンジニアの採⽤ インドを中⼼とした海外のハイスキルエン ジニアと⽇本企業をマッチング。 SaaS / ToB エンジニア組織の⾒える化 GitHubやJiraを解析し、エンジニア組織の ⾒える化と⽣産性向上をサポート。 組織分析SaaS ToC / ToB 開発ツールのレビューサイト 実際に利⽤している企業の声を元に、開発 ツールの導⼊や検討に必要な情報を集約。 企業の技術選定をサポート。 開発ツールメディア ˞֤छ਺஋͸ɺ೥݄࣌఺ͷ'JOEZస৬ɺ'JOEZ'SFFMBODFɺ'JOEZ5FBNɺ'JOEZ(MPCBMͷαʔϏεͷྦྷܭͰͷࣾ਺ٴͼొ࿥ऀ਺Ͱ͢ɻ ͳ͓ɺࣾຢ͸໊ͷํ͕ෳ਺ͷαʔϏεʹొ࿥͍ͯ͠Δ৔߹͸ɺͦͷαʔϏεͷ਺ʹԠͯ͡ෳ਺ͷΧ΢ϯτΛ͍ͯ͠·͢ɻ β 版 4

Slide 5

Slide 5 text

© Findy Inc. 5 Findy Team+(チームプラス)とは︖ 開発⽣産性の可視化、開発プロセスの伸びしろの発⾒、継続的 な改善をサポート 5 ⽣産性可視化 ⽣産性向上 事業開発スピード加速 (開発スピードの向上により、仮説検証スピードも加速) 開発プロセス改善 (開発フロー･配置･ツールの伸びしろを可視化・最適化) ⽂化づくり・⾃⼰組織化 (メンバーの⾃発的な改善促進、改善を称賛する⽂化作り) データ 連携 Engineer Engineer 開発組織ブランディング (エンジニアは、開発⽣産性が⾼い組織で働きたい) Recruit Biz

Slide 6

Slide 6 text

© Findy Inc. 6 Agenda - マルチテナントについて - これまでのマルチテナント - 移⾏先アーキテクチャの検討

Slide 7

Slide 7 text

© Findy Inc. マルチテナントについて 7

Slide 8

Slide 8 text

© Findy Inc. 8 マルチテナントについて - 単⼀のソフトウェアインスタンスが複数の独⽴したテナン トによって共有される仕組みを指します。 アプリケーション テナントA テナントC テナントB

Slide 9

Slide 9 text

© Findy Inc. 9 データの分離⽅法 - データの分離⽅法には様々な⽅法があります。 - 単⼀データベース、単⼀スキーマ - 単⼀データベース、複数スキーマ - 複数データベース

Slide 10

Slide 10 text

© Findy Inc. 10 単⼀データベース、単⼀スキーマ - テナント間で同⼀テーブルを利⽤するが、tenant_idなどで データを区別する スキーマ テナントA テナントC テナントB テーブル id tenant_id …

Slide 11

Slide 11 text

© Findy Inc. 11 単⼀データベース、単⼀スキーマ メリット - テナントの数に関わらず1つのDBやスキーマで運⽤でき、 各テーブルも1つずつ準備すれば良いので低コスト デメリット - 任意のテナントの利⽤率が⾼まった場合、他のテナントの DBリソースも影響を受ける - テナント分離の制御をアプリケーションで⾏う必要がある - SQLで常にtenant_idの条件を付与する必要がある

Slide 12

Slide 12 text

© Findy Inc. 12 単⼀データベース、複数スキーマ テナントA スキーマ - テナントごとに専⽤スキーマを持つ テナントB スキーマ テナントC スキーマ

Slide 13

Slide 13 text

© Findy Inc. 13 単⼀データベース、複数スキーマ メリット - テナントの数に関わらず1つのDBで運⽤でき低コスト - テナント分離の制御を接続先スキーマの制御のみで⾏うこ とができる デメリット - スキーマごとに同⼀テーブルを準備する必要がある - 任意のテナントの利⽤率が⾼まった場合、他のテナントの DBリソースも影響を受ける

Slide 14

Slide 14 text

© Findy Inc. 14 複数データベース テナントA DB - テナントごとに専⽤データベースを持つ テナントC DB テナントB DB

Slide 15

Slide 15 text

© Findy Inc. 15 複数データベース メリット - テナント分離の制御を接続先データベースの制御のみで⾏ うことができる - 任意のテナントの利⽤率が⾼まった場合、他のテナントの DBリソースは影響を受けない デメリット - テナントごとにデータベースを準備する必要があるため運 ⽤コストや費⽤が⾼い - データベースごとに同⼀テーブルを準備する必要がある

Slide 16

Slide 16 text

© Findy Inc. 16 まとめ データ 分離 運⽤ リソース 独⽴ 費⽤ なし × ◯ × ◯ 単⼀DB 単⼀スキーマ △ ◯ × ◯ 単⼀DB 複数スキーマ ◯ × × ◯ 複数DB ◯ × ◯ × データ分離: データ分離できていると◯ 運⽤: 運⽤コストが低いと◯ リソース独⽴: DBリソースが独⽴と◯ 費⽤: DBのランニングコストが安いと◯

Slide 17

Slide 17 text

© Findy Inc. 17 まとめ データ 分離 運⽤ リソース 独⽴ 費⽤ なし × ◯ × ◯ 単⼀DB 単⼀スキーマ △ ◯ × ◯ 単⼀DB 複数スキーマ ◯ × × ◯ 複数DB ◯ × ◯ ×

Slide 18

Slide 18 text

© Findy Inc. 18 まとめ データ 分離 運⽤ リソース 独⽴ 費⽤ なし × ◯ × ◯ 単⼀DB 単⼀スキーマ △ ◯ × ◯ 単⼀DB 複数スキーマ ◯ × × ◯ 複数DB ◯ × ◯ ×

Slide 19

Slide 19 text

© Findy Inc. 19 まとめ データ 分離 運⽤ リソース 独⽴ 費⽤ なし × ◯ × ◯ 単⼀DB 単⼀スキーマ △ ◯ × ◯ 単⼀DB 複数スキーマ ◯ × × ◯ 複数DB ◯ × ◯ ×

Slide 20

Slide 20 text

© Findy Inc. 20 まとめ データ 分離 運⽤ リソース 独⽴ 費⽤ なし × ◯ × ◯ 単⼀DB 単⼀スキーマ △ ◯ × ◯ 単⼀DB 複数スキーマ ◯ × × ◯ 複数DB ◯ × ◯ ×

Slide 21

Slide 21 text

© Findy Inc. 21 まとめ データ 分離 運⽤ リソース 独⽴ 費⽤ なし × ◯ × ◯ 単⼀DB 単⼀スキーマ △ ◯ × ◯ 単⼀DB 複数スキーマ ◯ × × ◯ 複数DB ◯ × ◯ × 現状はこちら

Slide 22

Slide 22 text

© Findy Inc. これまでのマルチテナント 22

Slide 23

Slide 23 text

© Findy Inc. 23 これまでのマルチテナント - Findy Team+では、テナント間のデータ漏洩のセキュリテ ィリスクを考慮しつつ、運⽤コストや費⽤とのバランスを 加味して、アプリケーションレイヤーでマルチテナントの 制御をしています。 テーブル id tenant_id … ・全テーブルにtenant_idを付与 ・SQLでtenant_idの条件を必ず付与 することで、他のテナント情報が混ざ ることを防止

Slide 24

Slide 24 text

© 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のユーザーではない場合、取得 できない # 他テナントの情報漏洩を防ぐことができる

Slide 25

Slide 25 text

© Findy Inc. 25 課題 - ライブラリを使って⾃動的にテナント分離を⾏っているの で、テナントの指定漏れが発⽣することはほぼないが、稀 にテナントを超えたデータリードが発⽣することがある

Slide 26

Slide 26 text

© Findy Inc. 26 課題 - 指定漏れが発⽣する実装パターン - 複雑なSQLの場合、tenant_idの条件が適切に効かない - ORなどでtenant_idの条件が無効化される - tenant_id = 1 AND name=ʻʼ OR name= ʻwordʼ

Slide 27

Slide 27 text

© Findy Inc. 27 課題 - 指定漏れが発⽣する実装パターン - ⾮同期処理にグローバル変数に指定したtenant_idが引 き継がれず指定漏れ

Slide 28

Slide 28 text

© Findy Inc. 28 課題 - 指定漏れが発⽣する実装パターン - ⾮同期処理にグローバル変数に指定したtenant_idが引 き継がれず指定漏れ より強固なアプリケーションにするため 別のアーキテクチャを検討

Slide 29

Slide 29 text

© Findy Inc. 移⾏先アーキテクチャの検討 29

Slide 30

Slide 30 text

© Findy Inc. 30 移⾏先アーキテクチャの候補 - 複数スキーマ / 複数データベースへ移⾏ - Row Level Securityの適⽤ - Viewの活⽤

Slide 31

Slide 31 text

© Findy Inc. 31 複数スキーマ / 複数データベースへ移⾏ データ 分離 運⽤ リソース 独⽴ 費⽤ なし × ◯ × ◯ 単⼀DB 単⼀スキーマ △ ◯ × ◯ 単⼀DB 複数スキーマ ◯ × × ◯ 複数DB ◯ × ◯ ×

Slide 32

Slide 32 text

© Findy Inc. 32 複数スキーマ / 複数データベースへ移⾏ - DBの接続先で制御できるので、設定ミスなどを除きアプリ ケーションから他テナントにアクセスできない データ 分離 運⽤ リソース 独⽴ 費⽤ 単⼀DB 複数スキーマ ◯ × × ◯ 複数DB ◯ × ◯ ×

Slide 33

Slide 33 text

© Findy Inc. 33 複数スキーマ / 複数データベースへ移⾏ - テナントごとにテーブルを準備する必要があり、スキーマ マイグレーションの運⽤コストが増⼤ データ 分離 運⽤ リソース 独⽴ 費⽤ 単⼀DB 複数スキーマ ◯ × × ◯ 複数DB ◯ × ◯ ×

Slide 34

Slide 34 text

© Findy Inc. 34 複数スキーマ / 複数データベースへ移⾏ - 複数DBの場合、DBリソースが独⽴するため、付随効果と してテナントごとにリソースが独⽴するメリットがある - その分、費⽤が⼤幅に⾼まる データ 分離 運⽤ リソース 独⽴ 費⽤ 単⼀DB 複数スキーマ ◯ × × ◯ 複数DB ◯ × ◯ ×

Slide 35

Slide 35 text

© Findy Inc. 35 複数スキーマ / 複数データベースへ移⾏ まとめ - テナント間のデータの独⽴と運⽤コスト・費⽤のトレード オフ - 複数データベースの場合、リソースが独⽴するメリット があるので、リソース共有に課題が発⽣したら採⽤する メリットが⼤きくなる - テナントごとにデータ分離するためデータ移⾏が必要

Slide 36

Slide 36 text

© Findy Inc. 36 複数スキーマ / 複数データベースへ移⾏ まとめ - テナント間のデータの独⽴と運⽤コスト・費⽤のトレード オフ - 複数データベースの場合、リソースが独⽴するメリット があるので、リソース共有に課題が発⽣したら採⽤する メリットが⼤きくなる - テナントごとにデータ分離するためデータ移⾏が必要 現時点では データ分離のメリットより 運⽤コスト・費⽤の増加が重いので 不採⽤

Slide 37

Slide 37 text

© Findy Inc. 37 Row Level Securityの適⽤ - データベーステーブルの⾏単位のアクセス制御をデータベ ースレイヤーで⾏うことができる機能 - 特定のユーザーやロールに対して、どの⾏にアクセスでき るのかを制御できる

Slide 38

Slide 38 text

© 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と一致 しているレコードしか取得できない

Slide 39

Slide 39 text

© Findy Inc. 39 Row Level Securityの適⽤ - MySQLにはRow Level Securityがない - Row Level Securityに対応したDBへ移⾏が必要 - 例えばPostgreSQL - PostgreSQLへの移⾏するか︖ - Ruby on RailsのActiveRecordがDB特有の制御は隠蔽 しているが、それでもDB移⾏はハードルが⾼い - パフォーマンス観点 - SQLの細かな違い

Slide 40

Slide 40 text

© Findy Inc. 40 Row Level Securityの適⽤ - MySQLにはRow Level Securityがない - Row Level Securityに対応したDBへ移⾏が必要 - 例えばPostgreSQL - PostgreSQLへの移⾏するか︖ - Ruby on RailsのActiveRecordがDB特有の制御は隠蔽 しているが、それでもDB移⾏はハードルが⾼い - パフォーマンス観点 - SQLの細かな違い PostgreSQLへの移⾏が重いので 不採⽤

Slide 41

Slide 41 text

© Findy Inc. 41 Viewの活⽤ - MySQLでRow Level Securityのような仕組みを実現するた めにViewを活⽤ - テナントで絞り込んだViewを作り、View経由でアクセスす ることで別テナントのデータが混ざることを防ぐ

Slide 42

Slide 42 text

© 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/

Slide 43

Slide 43 text

© Findy Inc. 43 Viewの活⽤ テーブル ア プ リ ケ % シ ' ン View tenant_idで 絞ったViewを ⽣成 View 参照 - 活⽤イメージ

Slide 44

Slide 44 text

© 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;

Slide 45

Slide 45 text

© 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;

Slide 46

Slide 46 text

© 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;

Slide 47

Slide 47 text

© 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;

Slide 48

Slide 48 text

© Findy Inc. 48 Viewの活⽤ - Viewを作成する必要はあるが、既存テーブルはそのまま使 えるのでデータ移⾏が不要 - アプリケーションコードをほぼ変える必要がない - テーブルスキーマ変更時にViewへ反映 - 接続先をViewに向ける - テーブル名とView名を揃えることで、テーブルに発 ⾏していたSQLがそのまま使える - Viewは実体がないのでDB費⽤がほぼ変わらない

Slide 49

Slide 49 text

© Findy Inc. 49 Viewの活⽤ データ 分離 運⽤ リソース 独⽴ 費⽤ なし × ◯ × ◯ 単⼀DB 単⼀スキーマ △ ◯ × ◯ 単⼀DB 複数スキーマ ◯ × × ◯ 複数DB ◯ × ◯ × View活⽤ ◯ △ × ◯

Slide 50

Slide 50 text

© Findy Inc. 50 Viewの活⽤ データ 分離 運⽤ リソース 独⽴ 費⽤ なし × ◯ × ◯ 単⼀DB 単⼀スキーマ △ ◯ × ◯ 単⼀DB 複数スキーマ ◯ × × ◯ 複数DB ◯ × ◯ × View活⽤ ◯ △ × ◯ Findy Team+では Viewを活⽤する⽅法を採⽤︕ 現在、移⾏中︕︕ いくつか問題は発⽣したものの パフォーマンス劣化など 致命的な問題は発⽣していません︕︕

Slide 51

Slide 51 text

© Findy Inc. 51 Viewの活⽤ - View移⾏中に発⽣した課題 - View経由でテーブルスキーマが正しく取得できない ※テーブルスキーマ情報はRailsで利⽤するために必要 - auto_incrementの情報が失われる - Primary Keyが正しく取得できない

Slide 52

Slide 52 text

© Findy Inc. 52 Viewの活⽤ - View移⾏中に発⽣した課題 - View経由でテーブルスキーマが正しく取得できない ※テーブルスキーマ情報はRailsで利⽤するために必要 - auto_incrementの情報が失われる - Primary Keyが正しく取得できない Railsにパッチをあて 正しいスキーマ情報を 取得できるようにすることで回避

Slide 53

Slide 53 text

© Findy Inc. 53 Viewの活⽤ テーブル ア プ リ ケ % シ ' ン View tenant_idで 絞ったViewを ⽣成 View 参照 - 活⽤イメージ(再掲)

Slide 54

Slide 54 text

© Findy Inc. 54 プロダクトエンジニア ・エンジニアをターゲットとした プロダクト開発 ・技術はモダンが当たり前 ・⾼い開発⽣産性 ・開発者体験の追求 ・CI/CDや⾃動テストが 整備された開発環境 We are hiring！