MySQL 8.0から追加されたロール機能について
MySQL 8.0 からロール機能が追加されました。
すでにOracle DatabaseやPostgreSQLには存在しているため、MySQLではまだかと待ち望んでいた方も多いのではないでしょうか。
今回の記事では、MySQLのロールについての使用方法と、その他のデータベースのロールとの差異について見ていこうと思います。
環境
今回比較した環境は以下の通りです。
| プロダクト | バージョン |
|---|---|
| MySQL | 8.0.11 |
| Oracle | 12.1.2 |
| PostgreSQL | 10.4 |
ロールの概念
A MySQL role is a named collection of privileges.
Like user accounts, roles can have privileges GRANTed to and revoked from them.
PostgreSQL manages database access permissions USING the concept of roles. A role can be thought of as either a database user, or a group of database users, depending on how the role is set up.
ユーザー・ロールは、作成したり他のユーザーに割り当てることができる権限の名前付きコレクションです。
MySQLでは、権限のコレクションでありユーザーアカウントのようなものと言っています。
PostgreSQLではそもそもロールによりデータベースへのアクセス権限を制御していて、ロールはユーザーであり、グループであると言っています。
Oracleの場合は、権限の名前付きコレクションであると言っています。
Oracleの場合はあくまでユーザーに適用するためのオブジェクトですが、MySQLやPostgreSQLではロールはほぼユーザーと同様です(ロールでログインすることも可能です)。
データベースによって、このように内部的な差異はありますが、いずれの製品でも事前定義済みの権限のコレクションを、ユーザーに適用して使う事に変わりありません。
ロールの作成
全てのデータベースにおいて、 CREATE ROLEコマンドで作成します。
MySQL、PostgreSQLではCREATE ROLEを実行すると実際にユーザーが作成されます。
CREATE USERと異なるのは、デフォルトではログイン不可である点です。
|
1 2 3 4 5 6 7 |
mysql> CREATE ROLE MYROLE; mysql> SELECT user,host,account_locked FROM mysql.user WHERE user = 'MYROLE'; +--------+------+----------------+ | user | host | account_locked | +--------+------+----------------+ | MYROLE | % | Y | +--------+------+----------------+ |
Oracleではロールは別のオブジェクトです。
|
1 2 3 4 5 6 7 8 9 10 11 12 13 |
SQL> CREATE ROLE MYROLE; Role created. SQL> SELECT role FROM dba_roles WHERE role = 'MYROLE'; ROLE ------------- MYROLE SQL> SELECT username FROM dba_users WHERE username = 'MYROLE'; no rows selected. |
OracleではCREATE ROLEの際にパスワードを設定する事が可能です。
パスワード付きロールは、SET ROLE .. IDENTIFIED BY で有効化します。
ロールへの権限付与、ユーザーへのロールの付与、ロールへのロールの付与
いずれのデータベースも、GRANT を使用します。
MySQL/GRANT
PostgreSQL/GRANT
Oracle/GRANT
通常の権限付与と同じように、ロールへ権限を付与します。
|
1 |
mysql> GRANT SELECT ON <schema>.<table> TO <role>; |
ユーザーへのロールの付与も、同じように実施します。
|
1 |
mysql> GRANT <role> TO <user>; |
ロールへのロールの付与も可能です。
|
1 |
mysql> GRANT <role> TO <role>; |
ロールはユーザーと同じですので、他のユーザーと同じ権限を持つユーザーを作成する事も可能です。
MySQLでデフォルトで作成されるroot@localhostユーザーの全権限を付与したい場合は、以下のようにできます。
|
1 |
mysql> GRANT `root`@`localhost` TO user1; |
PostgreSQLも同様に、管理ユーザーであるpostgresの権限を付与できます。
|
1 |
postgres=# GRANT posrgres TO user1; |
例えば、別のアプリ用のユーザーが持つ権限を使う必要がある場合には、予め当該ユーザーをロールとして適用しておき、後述するSET ROLEで切り替えるような使い方が可能ですので、管理するユーザーを削減できるかもしれません。
Oracleの場合は、ユーザーにはロールのみを付与するという運用ルールを決めた上で、あるユーザーと同じロールを付与して同じ権限とする形になります。
ロールの有効化
MySQLでは、GRANTでロールを付与しただけではユーザーセッションでロールが有効になりません。
SET DEFAULT ROLEで設定したデフォルトのロールのみが、ログイン直後に有効化されます。
|
1 |
mysql> SET DEFAULT ROLE <role> TO <user>; |
または、activate_all_roles_on_loginシステム変数を有効化している場合、付与されたロールが全て有効化されます。
|
1 |
mysql> SET GLOBAL activate_all_roles_on_login = on; |
現在接続しているユーザーで、使用可能なロールを有効化したい場合は、SET ROLEを実行します。
|
1 |
mysql> SET ROLE <role>; |
有効化されたロールはcurrent_role()ファンクションで確認できます。
|
1 2 3 4 5 6 |
mysql> SELECT current_role(); +----------------+ | current_role() | +----------------+ | `MYROLE`@`%` | +----------------+ |
PostgreSQLでは、適用したロールは一部の権限を除き、ログイン時にデフォルトで有効化されます。
詳しくはCREATE ROLEを参照してください。
Oracleも同様に、ログイン時にはパスワード付きロールを除いてデフォルトでは有効化されます。
ロールの権限確認
MySQLではSHOW GRANTSで確認可能です。
|
1 2 3 4 5 6 |
mysql> SHOW GRANTS FOR MYROLE; +------------------------------------+ | GRANTS FOR MYROLE@% | +------------------------------------+ | GRANT USAGE ON *.* TO `MYROLE`@`%` | +------------------------------------+ |
ユーザーに付与されたロールの権限はデフォルトでは表示されませんがUSING句を使う事で表示されます。
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 |
mysql> SHOW GRANTS FOR user1; +-----------------------------------+ | GRANTS FOR user1@% | +-----------------------------------+ | GRANT USAGE ON *.* TO `user1`@`%` | | GRANT `MYROLE`@`%` TO `user1`@`%` | +-----------------------------------+ mysql> SHOW GRANTS FOR user1 USING MYROLE; +------------------------------------------------------------+ | GRANTS FOR user1@% | +------------------------------------------------------------+ | GRANT SELECT, INSERT, UPDATE, DELETE ON *.* TO `user1`@`%` | | GRANT `MYROLE`@`%` TO `user1`@`%` | +------------------------------------------------------------+ |
PostgreSQLではpsqlの\z、\duコマンドで確認可能です。
user1.tab1にはmyroleがr(read)権限を持ち、user2はmyroleのメンバであることがわかります。
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 |
postgres=# \z Access privileges Schema | Name | Type | Access privileges | Column privileges | Policies -------+------+-------+----------------------+-------------------+---------- public | t | table | | | user1 | tab1 | table | user1=arwdDxt/user1+ | | | | | myrole=r/user1 | | postgres=# \du List of roles Role name | Attributes | Member of -----------+---------------------+-------------- myrole | Cannot login | {} postgres | Superuser,... | {} user1 | | {} user2 | | {myrole} |
Oracleだとユーザーに付与されたロールはデータ・ディクショナリから確認可能です。
全てのロールはDBA_ROLE_PRIVSで確認できます。
接続したユーザーに付与されているロールを確認するにはUSER_ROLE_PRIVSを確認します。
|
1 |
SQL> SELECT * FROM user_role_privs; |
ロールに適用した権限は、DBA_TAB_PRIVSやDBA_SYS_PRIVSを確認します。
※ALL_XXX_PRIVSでも可能
例えば全ロールに付与した全権限を確認する場合は以下のようなSQLになります。
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 |
SQL> (SELECT r.role, p.privilege, admin_option, null AS owner, null AS table_name FROM dba_sys_privs p, dba_roles r WHERE r.role = p.grantee) UNION ALL (SELECT r.role, p.privilege, null as admin_option p.owner, p.table_name FROM dba_tab_privs p, dba_roles r WHERE r.role = p.grantee ); |
定義済みロールと、PUBLICロール
Oracleには、Oracleデータベースが持つ機能等の単位で事前に定義されたロールが多数ありますが、PostgreSQLやMySQLにはそのようなものはありません。
また、OracleやPostgreSQLにはあって、MySQLには無いものとしては、PUBLICロールがあります。
PUBLICロールは、全ユーザーに暗黙的に付与されているロールで、デフォルトの権限を定義する事が可能です。
MySQLでは、似たような機能として SET DEFAULT ROLEによるデフォルトロールの定義が可能ですが、ユーザー単位の設定になるため、使用感としてはmandatory_roles + init_connectの組み合わせの方が近いように思います。
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 |
-- PUBLICロールの作成 mysql> CREATE ROLE PUBLIC; -- PUBLICロールを必須ロールに追加 mysql> SET PERSIST mandatory_roles = '`PUBLIC`@`%`'; -- PUBLICロールを接続時に有効化 mysql> SET PERSIST init_connect = 'SET ROLE PUBLIC'; -- user1で接続テスト mysql> SHOW GRANTS FOR user1; +-----------------------------------+ | GRANTS FOR user1@% | +-----------------------------------+ | GRANT USAGE ON *.* TO `user1`@`%` | +-----------------------------------+ mysql> EXIT $ mysql -uuser1 mysql> > SELECT current_role(); +----------------+ | current_role() | +----------------+ | `PUBLIC`@`%` | +----------------+ |
サンプル構成
今後MySQLではロールが使用できるという事で、以下のようなロールを用意しておくと管理上見通しがよくなります。
| ロール名 | 説明 |
|---|---|
| ROLE_SCHEMA_READ | 特定のスキーマへのSELECT権限が含まれるロール |
| ROLE_SCHEMA_INSERT | 特定のスキーマへのINSERT権限が含まれるロール |
| ROLE_SCHEMA_WRITE | 特定のスキーマへのUPDATE, DELETE, REPLACE権限が含まれるロール |
| ROLE_APP | アプリケーションに必要な権限が含まれるロールをまとめるロール |
| ROLE_ADMIN | 管理系操作に必要な権限が含まれるロール |
| ROLE_REPLICATION | レプリケーションに必要な権限が含まれるロール |
まとめ
ロールを使用するメリットとしては、以下のようなものがあります。
- ユーザーに適用する権限の差異を気にする必要がなくなる
棚卸しや、運用時の権限付与、剥奪が楽に - 何度もREVOKEやGRANTコマンドを実行する必要が無くなる
長文なコマンドを実行する等ヒューマンエラーやバグの温床を回避 - ユーザーを増やすのではなく、ロールをユーザー側で切り替えて使う運用が可能に
不要なユーザー増加の回避。インフラ作業の減少
MySQLのロールは新しく追加されたものですが、他データベースと遜色ない機能が備わっており、MySQLへの移行をお考えの方はよりハードルが低くなりました。
ますます使いやすいデータベースになったのでは無いでしょうか。
