MySQL 8.0（8.0.2）DMRでロールを使う (1) 基本編 の続きです。
(1) では、基本的な使い方を確認しましたが、(2) では、
- デフォルトロール
- ホスト違いのロールの扱い
- mandatory roles（必須ロール？）※8.0.2追加機能
- ログイン時に全ロール（mandatoryなものを含む）を有効化する ※8.0.2追加機能
について取り上げます。
3. デフォルトロール
(1) で記した、デフォルトロール（ログイン時に自動的に有効化するロール）の指定を試してみます。
※以降、(1) の続きの操作です。
デフォルトロール
$ mysql -u user003 -h localhost -p
Enter password:
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 9
Server version: 8.0.2-dmr MySQL Community Server (GPL)
Copyright (c) 2000, 2017, Oracle and/or its affiliates. All rights reserved.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
※デフォルトロールを指定
mysql> SET DEFAULT ROLE ALL TO user003@localhost;
Query OK, 0 rows affected (0.00 sec)
※すぐには有効にならない
mysql> SELECT CURRENT_ROLE();
+----------------+
| CURRENT_ROLE() |
+----------------+
| NONE |
+----------------+
1 row in set (0.00 sec)
すぐには有効にならないので、一度ログアウトしてログインし直します。
再ログインで有効化
※一度ログアウト
mysql> QUIT
Bye
$ mysql -u user003 -h localhost -p
Enter password:
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 10
Server version: 8.0.2-dmr MySQL Community Server (GPL)
Copyright (c) 2000, 2017, Oracle and/or its affiliates. All rights reserved.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
※ログイン時に有効化されている
mysql> SELECT CURRENT_ROLE();
+----------------------------+
| CURRENT_ROLE() |
+----------------------------+
| `sales_person`@`localhost` |
+----------------------------+
1 row in set (0.00 sec)
ログイン時に有効化されました。
4. ホスト違いのロールの扱い
ロールには、ユーザと同様「@」の後ろにホスト名の指定もあります。
先に書いてしまうと、8.0.2 DMRの時点では、割り当てるユーザと一致しないと使えない、というようなことはないようです。
ホスト違いを試す
$ mysql -u user002 -h localhost -p
Enter password:
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 16
Server version: 8.0.2-dmr MySQL Community Server (GPL)
Copyright (c) 2000, 2017, Oracle and/or its affiliates. All rights reserved.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
※有効化はエラーなし
mysql> SET ROLE support_manager@somehost;
Query OK, 0 rows affected (0.00 sec)
※実際に権限も有効
mysql> SELECT * FROM support.emp_rating;
+--------+-------------+--------+
| emp_id | rating_date | rating |
+--------+-------------+--------+
| 3 | 2017-08-01 | 10 |
| 5 | 2017-08-01 | 92 |
+--------+-------------+--------+
2 rows in set (0.00 sec)
「localhost」と「somehost」で一致していませんが、特に問題なくロールの権限が有効になります。
ロールの切り替えを試す
※↑の続きでDELETE⇒削除できない
mysql> DELETE FROM support.emp_rating;
ERROR 1142 (42000): DELETE command denied to user 'user002'@'localhost' for table 'emp_rating'
※更新権限を持つロールを有効化
mysql> SET ROLE support_manager_update@somehost;
Query OK, 0 rows affected (0.00 sec)
※更新可能になった
mysql> UPDATE support.emp_rating SET rating=89 WHERE emp_id=3;
Query OK, 1 row affected (0.01 sec)
Rows matched: 1 Changed: 1 Warnings: 0
※当然、下位ロールの権限も有効
mysql> SELECT * FROM support.emp_rating;
+--------+-------------+--------+
| emp_id | rating_date | rating |
+--------+-------------+--------+
| 3 | 2017-08-01 | 89 |
| 5 | 2017-08-01 | 92 |
+--------+-------------+--------+
2 rows in set (0.00 sec)
問題ないですね（いいのかどうかわかりませんが…）。
5. mandatory roles（必須ロール？）
どう訳すか迷ったのですが、**「mandatory_roles」**として指定すると、
- 全ユーザに暗黙的に割り当てられる（「mysql.role_edges」テーブルにも表示されない）
- 削除（DROP）できなくなる
- 割り当て解除（REVOKE）できなくなる
ようです。
「commons」DBに含まれる従業員テーブルへのSELECT権限をこれで全ユーザに割り当ててみます。
ここでは、my.cnfの[mysqld]セクションへの記述ではなく「SET PERSIST」で指定してみます。
必須ロールを指定
$ mysql -u root -h localhost -p
Enter password:
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 26
Server version: 8.0.2-dmr MySQL Community Server (GPL)
Copyright (c) 2000, 2017, Oracle and/or its affiliates. All rights reserved.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
mysql> CREATE ROLE manda;
Query OK, 0 rows affected (0.00 sec)
mysql> GRANT SELECT ON common.* TO manda;
Query OK, 0 rows affected (0.01 sec)
※my.cnfに書くか、SET PERSISTで必須のロールを指定（複数指定する場合は文字列としてカンマ区切りで 例：'manda,test'）
mysql> SET PERSIST mandatory_roles='manda';
Query OK, 0 rows affected (0.00 sec)
※必須化すると削除できなくなる
mysql> DROP ROLE manda;
ERROR 4527 (HY000): The role `manda`@`%` is a mandatory role and can't be revoked or dropped. The restriction can be lifted by excluding the role identifier from the global variable mandatory_roles.
「mandatory_roles」に指定しても、ユーザのログイン時に自動的に有効化されるわけではありません。
そのため、デフォルトロールとして指定してみます。
必須ロールをデフォルト化
※デフォルトロールとして既存ユーザに付与する
mysql> SET DEFAULT ROLE manda TO user001@localhost, user002@localhost, user003@localhost, user004@localhost;
Query OK, 0 rows affected (0.01 sec)
mysql> QUIT
Bye
$ mysql -u user003 -h localhost -p
Enter password:
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 27
Server version: 8.0.2-dmr MySQL Community Server (GPL)
Copyright (c) 2000, 2017, Oracle and/or its affiliates. All rights reserved.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
※先に割り当てたsupport_manager@somehostロールが上書きで消えた
mysql> SELECT CURRENT_ROLE();
+----------------+
| CURRENT_ROLE() |
+----------------+
| `manda`@`%` |
+----------------+
1 row in set (0.00 sec)
先に「user003@localhost」ユーザに割り当てておいたデフォルトロールをうっかり上書きしてしまいました。
割り当てられた全ロールをデフォルトロールとして指定し直します。
全ロールをデフォルトロールに
※当該ユーザに付与された全ロールをデフォルト化
mysql> SET DEFAULT ROLE ALL TO user003@localhost;
Query OK, 0 rows affected (0.00 sec)
mysql> QUIT
Bye
$ mysql -u user003 -h localhost -p
Enter password:
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 28
Server version: 8.0.2-dmr MySQL Community Server (GPL)
Copyright (c) 2000, 2017, Oracle and/or its affiliates. All rights reserved.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
※必須のロールは有効化されていない
mysql> SELECT CURRENT_ROLE();
+----------------------------+
| CURRENT_ROLE() |
+----------------------------+
| `sales_person`@`localhost` |
+----------------------------+
1 row in set (0.00 sec)
mysql> SELECT * FROM common.employee;
ERROR 1142 (42000): SELECT command denied to user 'user003'@'localhost' for table 'employee'
…仕方がないので、個別にデフォルトロールを指定します。
デフォルトロールを個別指定
※必須のロールとそれ以外のロールを個別指定して付与し直す
mysql> SET DEFAULT ROLE sales_person@localhost, manda TO user003@localhost;
Query OK, 0 rows affected (0.00 sec)
mysql> QUIT
Bye
$ mysql -u user003 -h localhost -p
Enter password:
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 29
Server version: 8.0.2-dmr MySQL Community Server (GPL)
Copyright (c) 2000, 2017, Oracle and/or its affiliates. All rights reserved.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
※ログイン時に有効化された
mysql> SELECT CURRENT_ROLE();
+----------------------------------------+
| CURRENT_ROLE() |
+----------------------------------------+
| `manda`@`%`,`sales_person`@`localhost` |
+----------------------------------------+
1 row in set (0.00 sec)
mysql> QUIT
Bye
できましたが、ちょっと面倒です。
6. ログイン時に全ロール（mandatoryなものを含む）を有効化する
こちらも8.0.2で追加された機能です。
**「activate_all_roles_on_login」**をONにすると、「mandatory_roles」に指定されたものを含め、各ユーザに割り当てられたロールがログイン時に自動的に有効化されます。
ここでも、my.cnfではなく「SET PERSIST」でやってみます。
activate_all_roles_on_loginを試す
mysql -u root -h localhost -p
Enter password:
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 30
Server version: 8.0.2-dmr MySQL Community Server (GPL)
Copyright (c) 2000, 2017, Oracle and/or its affiliates. All rights reserved.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
※ログイン時に付与された全ロールの有効化を指定する（my.cnfも可）
mysql> SET PERSIST activate_all_roles_on_login=1;
Query OK, 0 rows affected (0.00 sec)
mysql> QUIT
Bye
$ mysql -u user004 -h localhost -p
Enter password:
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 31
Server version: 8.0.2-dmr MySQL Community Server (GPL)
Copyright (c) 2000, 2017, Oracle and/or its affiliates. All rights reserved.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
※この指定の場合は必須のロールを含めてログイン時に有効化される
mysql> SELECT CURRENT_ROLE();
+-----------------------------------------+
| CURRENT_ROLE() |
+-----------------------------------------+
| `manda`@`%`,`support_person`@`somehost` |
+-----------------------------------------+
1 row in set (0.00 sec)
※mndatory_rolesで指定された権限も有効
mysql> SELECT * FROM common.employee;
+--------+--------+---------+
| emp_id | name | section |
+--------+--------+---------+
| 1 | 佐藤 | 1 |
| 2 | 田中 | 1 |
| 3 | 山田 | 2 |
| 4 | 鈴木 | 1 |
| 5 | 高橋 | 2 |
+--------+--------+---------+
5 rows in set (0.00 sec)
できました。
8.0.2の時点では、ユーザ別に適用することができないので、その点が問題になるかもしれません。
以上、MySQL 8.0 (8.0.2) DMRでの基本的なロールの使い方でした。
なお、ここでは触れていませんが、DROPやREVOKE等も普通に使えます。
但し、（今のところ）「CREATE ROLE…IDENTIFIED」はサポートされていないので、認証・認可の外部連携等はできません。
参考リンク：
- 日々の覚書: MySQL 8.0.0で追加されたROLEの仕組み（yoku0825さん）
- 漢（オトコ）のコンピュータ道: MySQL 8.0.0 Development Milestone Release登場！！（Oracle奥野さん）
【おまけ】
MySQL 8.0関連投稿記事へのリンクを集めました。