はじめに
こんにちは、ZOZOMO部SREブロックの蔭山です。普段はFulfillment by ZOZOやZOZOMOのSREを担当しています。
今回ZOZOMOで提供しているサービスの1つである「ブランド実店舗の在庫確認・在庫取り置き」のマイクロサービス（通称realshop-api）にてMySQLにアクセスできる運用ユーザーの権限管理の最適化を行いました。本記事でその取り組みについてご紹介いたします。
目次
なぜ権限管理を最適化したのか
realshop-apiではDBにAmazon DynamoDBとAmazon Aurora MySQLの2つを採用しています。どのように2つのDBを使っているのかについては過去テックブログでご紹介していますので、興味のある方は以下のリンクからご覧ください。
運用作業や調査の一環でAurora MySQLへアクセスする必要があり、リリースから暫くの間は以下のような状態で運用していました。
- 運用メンバーごとにMySQLユーザーを発行
- 権限は必要となったタイミングで個別にMySQLユーザーに付与
しかし、サービスやチームの成長に伴って以下のような問題が出てきました。
権限管理が複雑化してきた
チームメンバーの増加や様々な運用作業や調査をおこなっていくにつれてどのMySQLユーザーにどの権限が付与したのかが把握しづらくなってきました。実際に各メンバーが作業する上でもメンバーAは特定のクエリが実行できてメンバーBは実行できずに権限の付与依頼を行うなどのタスクが発生し、効率の悪化が目に見えてわかるような状態となってきました。
また権限の棚卸しを実施するにも、どのユーザーにどのような権限が付与されているのか、どの権限を付与・剥奪すべきかが定まっておらず、権限の棚卸し作業自体が困難になってきました。
秘密情報を閲覧できるメンバーを制限する必要がでてきた
サービスの拡張に伴い、Aurora MySQL上に秘密情報を保持する必要が出てきました。社内の開発ルールでは秘密情報を閲覧できるメンバーをごく少数に絞る必要がありましたが、上記のように複雑化した権限の状態で更にカラムごとでの権限を制御するのは難しい状態でした。
どのように最適化したか
上記の問題もあり、このタイミングで権限管理を1から見直すこととしました。今回どのように最適化していったのか実例をご紹介します。
ロール機能を使った権限の標準化
MySQL 8.0よりロール機能が実装されています。MySQL 8系をベースとしているAurora MySQL 3系でも利用できる機能です。ロールに対して権限を付与し、各MySQLユーザーにロールを割り当てることによりロールに紐づいた権限をユーザーにも継承できる機能です。
今回、まずはこのロール機能を使って付与権限の標準化を行うことにしました。権限の標準化にあたっては、運用メンバーのユースケースに合わせて以下のように定義しました。
| 権限名 | ロール名 | 付与想定ユーザー | 付与する権限 |
|---|---|---|---|
| 参照権限 | read_only_developer_role | 更新操作をする必要がないユーザー。特殊な権限を保つ必要がない運用メンバーにのみ付与。 | 秘密情報を含まないテーブルへのSELECT |
| 更新権限 | power_user_developer_role | 更新操作をする必要があるユーザー。一部の運用メンバーにのみ付与。 | 秘密情報を含まないテーブルへのINSERT、SELECT、UPDATE、DELETE |
| 管理者権限 | admin_developer_role | 秘密情報を含むテーブルへもアクセスする必要があるユーザー。チームマネージャーのみに付与。 | すべてのテーブルへのINSERT、SELECT、UPDATE、DELETE |
定義後はロールを作成し、ユーザーに今まで付与した権限をすべて剥奪後ロールを付与することでユーザーごとの権限の差異がなくなり、標準化が実現できました。
秘密情報の保護自動化
次に秘密情報を全運用ユーザーが閲覧できないような状態を作るために秘密情報の保護の自動化に取り組みました。ここからはどのように秘密情報の保護を自動化していったのか順を追ってご紹介します。
秘密情報カラムの管理
自動化するために、まずは秘密情報を保持するカラムの管理をシステムがわかりやすいようにしました。今回は秘密情報を保持したテーブル・カラムを管理するテーブルを作成しデータとして保持する方針としました。
実際には以下のような情報を保持するテーブルを作成しました。今回は sensitive_columns という名前でテーブルを作成しました。
| カラム名 | 保存する内容 | サンプル |
|---|---|---|
| table_name | 秘密情報を持つテーブル名 | secret_tables |
| column_name | 秘密情報を持つカラム名 | secret_column |
| type | どのような秘密情報を持っているか（会員名・住所・メールアドレスなど） |
また開発ルールとして秘密情報を持つカラムが追加された場合、DBマイグレーションツールを使って上記テーブルへINSERTを行うようなルールとしました。
秘密情報カラムへの権限剥奪を自動化
次に秘密情報カラムへの権限剥奪の自動化を行いました。
realshop-apiではDBマイグレーションにFlywayを利用しています。新規でバッチなどは準備せず、開発コストを最小化して実現するためDBマイグレーション実行後にトリガーされるafterMigrateを使って権限の付け替えを実施することにしました。
しかしFlywayのafterMigrateではSQLで記載されている必要があるため、権限の付け替えロジックをストアドプロシージャで定義し実行することにしました。実際には以下のようなストアドプロシージャを定義しました。
DELIMITER // CREATE PROCEDURE sp_operation_set_sensitive_roles() BEGIN DECLARE table_name TEXT; DECLARE column_names TEXT; DECLARE done BOOL DEFAULT FALSE; -- テーブルごとにアクセス可能なカラム一覧を取得 DECLARE tablesCursor CURSOR FOR SELECT t.TABLE_NAME AS table_name, GROUP_CONCAT(c.COLUMN_NAME ORDER BY c.ORDINAL_POSITION) AS column_names FROM INFORMATION_SCHEMA.TABLES t INNER JOIN INFORMATION_SCHEMA.COLUMNS c ON t.TABLE_NAME = c.TABLE_NAME WHERE t.TABLE_SCHEMA = 'DB名' AND t.TABLE_TYPE = 'BASE TABLE' AND NOT EXISTS(SELECT 1 FROM sensitive_columns WHERE sensitive_columns.table_name = t.TABLE_NAME AND sensitive_columns.column_name = c.COLUMN_NAME) GROUP BY t.TABLE_NAME; DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE; OPEN tablesCursor; read_loop: LOOP FETCH tablesCursor INTO table_name, column_names; IF done THEN LEAVE read_loop; END IF; -- Power Userロールへの権限付与 IF EXISTS(SELECT 1 FROM mysql.user WHERE user = 'power_user_developer_role') THEN SET @power_user_grant_sql = CONCAT('GRANT SELECT (', column_names, '), UPDATE (', column_names, ') ON DB名.', table_name, ' TO ''power_user_developer_role'''); PREPARE power_user_grant_stmt FROM @power_user_grant_sql; EXECUTE power_user_grant_stmt; DEALLOCATE PREPARE power_user_grant_stmt; END IF; -- Read Onlyロールへの権限付与 IF EXISTS(SELECT 1 FROM mysql.user WHERE user = 'read_only_developer_role') THEN SET @read_only_table_grant_sql = CONCAT('GRANT SELECT (', column_names, ') ON DB名.', table_name, ' TO ''read_only_developer_role'''); PREPARE read_only_table_grant_stmt FROM @read_only_table_grant_sql; EXECUTE read_only_table_grant_stmt; DEALLOCATE PREPARE read_only_table_grant_stmt; END IF; END LOOP; CLOSE tablesCursor; END// DELIMITER ;
内容は以下の通りです。
- MySQLのテーブル・カラム情報を保持している
INFORMATION_SCHEMA.TABLESとINFORMATION_SCHEMA.COLUMNS、秘密情報を保持しているsensitive_columnsを使って通常通り閲覧できるカラムを抽出 - 抽出した結果をもとに権限ごとでGRANT文を生成
- 生成したGRANT文を実行
上記で定義したストアドプロシージャをafterMigrateで実行することにより、権限の付け替えを自動で実施できるようにしました。
秘密情報カラムを除いたVIEWの自動作成
秘密情報カラムへの権限剥奪は実現できました。しかしこの対応によって秘密情報へ参照するクエリがすべてエラーとなるため、運用体験が悪化してしまう懸念がありました。
そこで社内でも実績があった秘密情報カラムを除いたVIEWをAurora MySQL上でも実現することにしました。テーブル名の先頭に v_ を付与することで既存クエリのエラーを少しでも防ぎ、運用体験の悪化を防ぐことを目的としました。
今回参考にしたSQL Serverでの秘密情報の保護に関しても過去テックブログでご紹介しています。こちらも興味のある方はぜひご覧ください。
秘密情報カラムを除いたVIEWに関しても前章と同じく、FlywayのafterMigrateで特定のストアドプロシージャーを実行することにしました。実際には以下のようなストアドプロシージャを定義し実行する形としました。
DELIMITER // CREATE PROCEDURE sp_operation_create_views() BEGIN DECLARE upsert_view_sql TEXT; DECLARE done BOOL DEFAULT FALSE; -- テーブルごとにCREATE VIEW文を生成 DECLARE upsertSQLCursor CURSOR FOR SELECT CONCAT( 'CREATE OR REPLACE VIEW v_', t.TABLE_NAME, ' AS SELECT ', GROUP_CONCAT(CASE WHEN EXISTS(SELECT 1 FROM sensitive_columns WHERE sensitive_columns.table_name = t.TABLE_NAME AND sensitive_columns.column_name = c.COLUMN_NAME) THEN CONCAT('''********'' AS ', c.COLUMN_NAME) ELSE c.COLUMN_NAME END ORDER BY c.ORDINAL_POSITION), ' FROM ', t.TABLE_NAME, ';' ) as upsert_view_sql FROM INFORMATION_SCHEMA.TABLES AS t INNER JOIN INFORMATION_SCHEMA.COLUMNS AS c ON t.TABLE_NAME = c.TABLE_NAME WHERE t.TABLE_SCHEMA = 'DB名' AND t.TABLE_TYPE = 'BASE TABLE' GROUP BY t.TABLE_NAME; DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE; OPEN upsertSQLCursor; read_loop: LOOP FETCH upsertSQLCursor INTO upsert_view_sql; IF done THEN LEAVE read_loop; END IF; SET @upsert_view_sql = upsert_view_sql; PREPARE stmt FROM @upsert_view_sql; EXECUTE stmt; DEALLOCATE PREPARE stmt; END LOOP; CLOSE upsertSQLCursor; END// DELIMITER ;
内容は以下の通りです。
- MySQLのテーブル・カラム情報を保持している
INFORMATION_SCHEMA.TABLESとINFORMATION_SCHEMA.COLUMNS、秘密情報を保持しているsensitive_columnsを使ってCREATE VIEW文を生成 - 生成したGRANT文を実行
上記の対応によって権限の付け替えと同様に自動化できました。
実施した結果
このように権限管理の最適化を実施した結果、問題としていた権限管理の複雑さはロールによる標準化で解消されました。またどのユーザーにどのロールが付与されていたかもわかりやすくなったため、棚卸しも実施しやすい状態にできました。
また秘密情報カラムに関しても必要最低限のメンバーしかアクセスできない状態にできました。秘密情報カラムを除いたVIEWも準備したことで運用メンバーの運用体験に大きな影響を与えることなく秘密情報の保護が実現できました。
今回ここまででご紹介してきた形で実現できたものの以下のような改善点が見えてきており、こちらに関しては今後解消していく予定です。
- 秘密情報の区分に合わせたマスクされる形式の変更
- 一時的な秘密情報カラムの権限付与
まとめ
本記事ではrealshop-apiで実施したMySQLでの権限管理の最適化についてご紹介しました。権限管理にお困りの方はぜひ参考にしてみてください。
ZOZOでは、一緒にサービスを作り上げてくれる方を募集中です。ご興味のある方は、以下のリンクからぜひご応募ください！
