はじめに
MySQL8.0から権限管理の仕組みにロールが導入されました。
他のDBMSと異なり定義済みロールが存在しないので、どのようなロールを作成すべきか考えてみました。
DBMSの定義済みロールの比較
次のDBMSを比較してみます。
- SQL Server
- PostgreSQL
- RDS Aurora MySQL
SQL Serverの定義済みロール
SQL Serverには定義済みロール（固定ロール）があり、ユーザーがロールを作らなくてもベストプラクティスに則ったロールの利用ができます。
ロールの種類は非常に多くてこのでは紹介しきれませんが、SQL Serverの権限分割の仕方はけっこう参考になると思われます。
サーバーレベルのロール
サーバー上の権限を管理するためのロール。
代表的なロールは次の通り。
| ロール名 | 可能なアクセス |
|---|---|
| sysadmin | サーバーに対するすべての操作を実行できます。 |
| serveradmin | サーバー全体の構成オプションを変更したり、サーバーをシャットダウンしたりできます。 |
| securityadmin | ログインとログインのプロパティを管理します。 |
| processadmin | SQL Server のインスタンス内で実行中のプロセスを終了できます。 |
データベースレベルのロール
データベース上の権限を管理するためのロール。
代表的なロールは次の通り。
| ロール名 | 可能なアクセス |
|---|---|
| db_owner | データベースでのすべての構成とメンテナンス作業を実行できます。 |
| db_ddladmin | すべての DDL (データ定義言語) コマンドをデータベースで実行できます。 |
| db_datawriter | すべてのユーザーテーブルのデータを追加、削除、または変更できます。 |
| db_datareader | すべてのユーザーテーブルとビューからすべてのデータを読み取ることができます。 |
その他
うちでは個人情報保護の必要性から、個人情報が見られないユーザー定義ロールを作成しています。
| ロール名 | 可能なアクセス |
|---|---|
| privacy_protection | すべてのユーザーテーブルとビューから個人情報以外のデータを読み取ることができます。 ※ 個人情報が存在するテーブルの個人情報列に対して拒否(DENY)設定をしています。 |
アプリケーションロール
使ったことがないのですが、特定のアプリケーションから接続する場合のみ使えるロールが作成できるとのこと。
公式ドキュメント
- サーバー レベルのロール - SQL Server | Microsoft Learn
- データベース レベルのロール - SQL Server | Microsoft Learn
- アプリケーション ロール - SQL Server | Microsoft Learn
PostgreSQLの定義済みロール
レベル分けはないですが、いくつかの定義済みロールが存在します。
- 読み込み権限
- 書き込み権限
- 管理系の権限
| ロール | 可能なアクセス |
|---|---|
| pg_read_all_data | それらのオブジェクトに対するSELECT権限を持っていて、明示的に持っていなかったとしてもすべてのスキーマに対してUSAGE権限を持っているかのように、すべてのデータ(テーブル、ビュー、シーケンス)を読み取る。 |
| pg_write_all_data | それらのオブジェクトに対するINSERT、UPDATEおよびDELETE権限を持っていて、明示的に持っていなかったとしてもすべてのスキーマに対してUSAGE権限を持っているかのように、すべてのデータ(テーブル、ビュー、シーケンス)に書き込む。 |
| pg_read_all_settings | 通常スーパーユーザのみが読み取れる、全ての設定変数を読み取る。 |
| pg_read_all_stats | 通常スーパーユーザのみが読み取れる、すべてのpg_stat_*ビューを読み取り、各種の統計関連のエクステンションを使用する。 |
| pg_stat_scan_tables | 潜在的に長時間、テーブルのACCESS SHAREロックを取得する可能性がある監視機能を実行する。 |
| pg_monitor | 各種の監視ビューや機能を読み取り/実行する。 このロールは、pg_read_all_settings、pg_read_all_statsおよびpg_stat_scan_tablesのメンバです。 |
| pg_database_owner | なし。メンバ資格は暗黙に現在のデータベースの所有者から構成されます。 |
| pg_signal_backend | 他のバックエンドに問い合わせのキャンセルやセッションの終了のシグナルを送信する。 |
| pg_read_server_files | COPYやその他のファイルアクセス関数で、データベースがサーバ上でアクセスできる任意の場所からファイルを読み取ることを許可する。 |
| pg_write_server_files | COPYやその他のファイルアクセス関数で、データベースがサーバ上でアクセスできる任意の場所にファイルを書き込むことを許可する。 |
| pg_execute_server_program | COPYやサーバ側のプログラムを実行できるその他の関数で、データベースを実行しているユーザとしてデータベースサーバ上でのプログラムの実行を許可する。 |
公式ドキュメント
RDS Aurora MySQLの定義済みロール
MySQLには存在しない定義済みロールが用意されています。
- 管理権限
- AWSサービスへのアクセス権限
| ロール | 用途 |
|---|---|
| rds_superuser_role | root 権限の代替 |
| AWS_COMPREHEND_ACCESS | INVOKE COMPREHEND 権限の代替 |
| AWS_LAMBDA_ACCESS | INVOKE LAMBDA 権限の代替 |
| AWS_LOAD_S3_ACCESS | LOAD FROM S3 権限の代替 |
| AWS_SAGEMAKER_ACCESS | INVOKE SAGEMAKER 権限の代替 |
| AWS_SELECT_S3_ACCESS | SELECT INTO S3 権限の代替 |
公式ドキュメント
Aurora MySQLでのロール設計
SQL Serverを参考に設計してみました。
必要なロールは、次の通りと考えました。
- 管理用権限
- DBMSの状態表示権限
- 読み書き権限
- 読み取り権限
- 個人情報以外の読み取り権限
- DDLの実行、表示権限
これを、グローバルレベルとデータベースレベルで作成します。
ロールはmysql.userテーブルに格納されるため、ユーザーとロールの区別が付くよう、suffixにroleを付けることにしました。
グローバル権限
Grantは*.*でロールを定義します。
| ロール名 | 用途 | 権限 | 付与先 |
|---|---|---|---|
| rds_superuser_role | 管理用 | 定義済みロール | DBA |
| statereader_role | 状態表示用 | PROCESS | 開発者 |
| datareader_role | 参照用 | SELECT, CREATE TEMPORARY TABLES | データ抽出用 |
| public_datareader_role | 公開情報参照用 | SELECT, CREATE TEMPORARY TABLES REVOKE SELECT ON 参照不可テーブル FROM 'public_ro_role'@'%'; ※ 部分取消しを活用します |
個人情報以外のデータ抽出用 |
データベース権限
GrantはDB名.*でロールを定義します。
| ロール名 | 用途 | 権限 | 付与先 |
|---|---|---|---|
| DB名_owner_role | 管理用 | ALL | アプリケーション（R/W） |
| DB名_datareader_role | 参照用 | SELECT, CREATE TEMPORARY TABLES | アプリケーション（R/O） 開発者 |
| DB名_datawriter_role | 書き込み用 | INSERT, UPDATE, DELETE | 開発者 |
| DB名_ddladmin_role | DDL実行、表示用 | CREATE, DROP, INDEX, ALTER, REFERENCES, CREATE VIEW, SHOW VIEW, CREATE ROUTINE, ALTER ROUTINE, |
開発者 |
部分取消しとは
部分取消し（Partial Revoke）という機能が追加されました。
GRANTで付与した権限の一部を取り消す機能です。
この設定を行うと、全DB・全テーブルの読み取り権限を付与して、一部のDB、テーブル、列の読み取り権限を剥奪することができます。
SQL ServerのDENY (オブジェクトの権限の拒否)にあたる機能で、けっこう待ち望んでいた機能でした。
公式ドキュメント
- MySQL :: MySQL 8.0 リファレンスマニュアル :: 6.2.10 ロールの使用
- MySQL :: MySQL 8.0 リファレンスマニュアル :: 13.7.1.6 GRANT ステートメント
- MySQL :: MySQL 8.0 リファレンスマニュアル :: 13.7.1.8 REVOKE ステートメント
- MySQL :: MySQL 8.0 リファレンスマニュアル :: 6.2.12 部分取消しを使用した権限の制限
まとめ
OLTP系データベースの開発、運用に必要なロールをまとめてみました。
こちらは一例ですので、皆さんも自分たちなりのロール設計を考えて、権限付与業務を楽にしましょう！
データ分析系データベースは、ユーザーも増えるし、もっと複雑なロール設計が必要なると思うんで、大変ですよね。