MySQL 8.0 DMRではロール（ROLE）がサポートされ、8.0.2で一部機能が追加されています。
MySQLユーザにはあまりなじみのない機能ですが、「デキるマネージャー／営業マンはSQLで分析！」という風潮（？）も出てきて、（マスタサーバではなくスレーブサーバだとは思いますが）DBに直接アクセスするユーザが増えたときに役立ちそうなので、使い方を確認してみます。
1. ロールの使い方
ロールを使わずにユーザに権限を付与する場合は、
- GRANT 権限 ON 対象DB.対象テーブル TO 対象ユーザ [IDENTIFIED BY パスワード]
のようにしますが、ユーザ全員にこのような形で権限を付与するのは、
- メンテナンスが面倒・間違えやすい
- 誰に何の権限が付与されているのか見づらい
などの問題があります。
通常、少人数でDBにアクセスするのでなければ、部署・チーム毎に付与すべき権限が（ほぼ）決まりますが、部署・チームのような単位で権限の「かたまり（集合）」を定義できれば管理が楽になります。
このようなときに使うのがロール（ROLE）です。
そして、そのロールをユーザに割り当てる形でユーザに権限を付与します。
- CREATE ロール名
- GRANT 権限 ON 対象DB.対象テーブル TO ロール名
でロールを作成し、
- GRANT ロール名 TO 対象ユーザ
でユーザに割り当てます。
なお、デフォルトでは、ロールをユーザに割り当てても、ログイン後すぐにその権限が使えるようになっているわけではありません。
- SET ROLE ロール名
で、（セッション内で）ロールを有効にする必要があります。
一見面倒な感じがしますが、例えばテーブルの管理者を兼ねている人が、
- 普段は管理対象のテーブルをSELECTするだけ
- テーブルをメンテナンスするときだけ、INSERT・UPDATE・DELETE権限を有効にする
というような「権限の切り替え」を行うことで、作業ミスによるトラブルを防ぐ目的でも使えます。
もちろん、デフォルトロールを指定することでログイン時からロールを有効にすることもできます。
- SET DEFAULT ROLE ロール名（またはALL） TO 対象ユーザ
2. 使ってみる
2-1. 前提
営業チームとサポートチームがあり、それぞれ「sales」・「support」のDB内のテーブルへのアクセス権を持ちます。
各チームにはマネージャー権限と一般職員権限のユーザがいて、マネージャー権限のユーザは各チームの人事考課テーブルへのアクセス権を持ちますが、一般職員権限のユーザはアクセス権を持ちません。
2-2. 準備
以下のようなデータが入ったテーブルを用意します。
テーブルデータ
※従業員テーブル
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)
※注文テーブル（営業）
mysql> SELECT * FROM sales.order;
+----------+------------+-------------+-------------+------------+--------+
| order_id | product_id | order_count | customer_id | order_date | emp_id |
+----------+------------+-------------+-------------+------------+--------+
| 1 | 1 | 4 | 6 | 2017-07-12 | 1 |
| 2 | 3 | 2 | 10 | 2017-07-15 | 1 |
| 3 | 1 | 5 | 15 | 2017-07-16 | 2 |
| 4 | 2 | 10 | 21 | 2017-07-21 | 1 |
+----------+------------+-------------+-------------+------------+--------+
4 rows in set (0.00 sec)
※人事考課テーブル（営業）
mysql> SELECT * FROM sales.emp_rng;
+--------+-------------+--------+
| emp_id | rating_date | rating |
+--------+-------------+--------+
| 1 | 2017-08-01 | 88 |
| 2 | 2017-08-01 | 55 |
| 4 | 2017-08-01 | 0 |
+--------+-------------+--------+
3 rows in set (0.00 sec)
※サポートケーステーブル（サポート）
mysql> SELECT * FROM support.support_case;
+---------+------------+------------+--------+-----------------------+
| case_id | open_date | close_date | emp_id | title |
+---------+------------+------------+--------+-----------------------+
| 1 | 2017-07-03 | 2017-07-04 | 5 | ログイン不可 |
| 2 | 2017-07-05 | 2017-07-05 | 5 | パスワード忘れ |
| 3 | 2017-07-05 | NULL | 3 | データ入力不可 |
| 4 | 2017-07-10 | 2017-07-10 | 5 | 計算結果相違 |
| 5 | 2017-07-11 | 2017-07-12 | 3 | 処理エラー発生 |
+---------+------------+------------+--------+-----------------------+
5 rows in set (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)
ユーザを作成します。
ユーザ作成
$ mysql -u root -h localhost -p
Enter password:
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 7
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 USER user001@localhost IDENTIFIED BY 'Hoge#1Fuga$2';
Query OK, 0 rows affected (0.00 sec)
※サポートチームマネージャー
mysql> CREATE USER user002@localhost IDENTIFIED BY 'Hoge$1Fuga#2';
Query OK, 0 rows affected (0.00 sec)
※営業チーム一般職員
mysql> CREATE USER user003@localhost IDENTIFIED BY 'Hoge%1Fuga&2';
Query OK, 0 rows affected (0.01 sec)
※サポートチーム一般職員
mysql> CREATE USER user004@localhost IDENTIFIED BY 'Hoge(1Fuga)2';
Query OK, 0 rows affected (0.00 sec)
2-3. ロールを作成し、権限を付与する
まずはロールを作成します。
ロール作成
※営業チーム用ロール
mysql> CREATE ROLE sales_person@localhost;
Query OK, 0 rows affected (0.00 sec)
※営業チームマネージャー用ロール
mysql> CREATE ROLE sales_manager@localhost;
Query OK, 0 rows affected (0.00 sec)
※サポートチーム用ロール（意図的にホスト名をダミーのものに）
mysql> CREATE ROLE support_person@somehost;
Query OK, 0 rows affected (0.01 sec)
※サポートチームマネージャー用ロール（同上／平常利用）
mysql> CREATE ROLE support_manager@somehost;
Query OK, 0 rows affected (0.00 sec)
※同上（人事考課テーブルメンテナンス時利用）
mysql> CREATE ROLE support_manager_update@somehost;
Query OK, 0 rows affected (0.01 sec)
各ロールに権限を付与します。
ロールに権限付与
※営業チーム用ロールには注文テーブルへのSELECT権限を付与
mysql> GRANT SELECT ON sales.order TO sales_person@localhost;
Query OK, 0 rows affected (0.01 sec)
※営業チームマネージャー用ロールには（営業）人事考課テーブルへのSELECT・INSERT・UPDATE・DELETE権限を付与
mysql> GRANT SELECT, INSERT, UPDATE, DELETE ON sales.emp_rating TO sales_manager@localhost;
Query OK, 0 rows affected (0.01 sec)
※ロールに別ロールを割り当てることも可能
mysql> GRANT sales_person@localhost TO sales_manager@localhost;
Query OK, 0 rows affected (0.00 sec)
※サポートチーム用ロールにはサポートケーステーブルへのSELECT権限を付与
mysql> GRANT SELECT ON support.support_case TO support_person@somehost;
Query OK, 0 rows affected (0.00 sec)
※サポートチームマネージャー用ロール（平常利用）には（サポート）人事考課テーブルへのSELECT権限を付与
mysql> GRANT SELECT, INSERT, UPDATE, DELETE ON support.emp_rating TO support_manager@somehost;
Query OK, 0 rows affected (0.00 sec)
mysql> GRANT support_person@somehost TO support_manager@somehost;
Query OK, 0 rows affected (0.00 sec)
※サポートチームマネージャー用ロール（メンテナンス用）には（サポート）人事考課テーブルへのINSERT・UPDATE・DELETE権限を付与
mysql> GRANT INSERT, UPDATE, DELETE ON support.emp_rating TO support_manager_update@somehost;
Query OK, 0 rows affected (0.00 sec)
※ロールを階層的に付与することも可能
mysql> GRANT support_manager@somehost TO support_manager_update@somehost;
Query OK, 0 rows affected (0.00 sec)
2-4. ロールをユーザに割り当てる
GRANTで割り当てます。
ロール割り当て
mysql> GRANT sales_manager@localhost TO user001@localhost;
Query OK, 0 rows affected (0.01 sec)
mysql> GRANT support_manager@somehost TO user002@localhost;
Query OK, 0 rows affected (0.00 sec)
mysql> GRANT support_manager_update@somehost TO user002@localhost;
Query OK, 0 rows affected (0.00 sec)
mysql> GRANT sales_person@localhost TO user003@localhost;
Query OK, 0 rows affected (0.01 sec)
mysql> GRANT support_person@somehost TO user004@localhost;
Query OK, 0 rows affected (0.00 sec)
割り当てたロールを確認します。
ロール確認
※割り当てられたロール
mysql> SELECT * FROM mysql.role_edges;
+-----------+------------------------+-----------+------------------------+-------------------+
| FROM_HOST | FROM_USER | TO_HOST | TO_USER | WITH_ADMIN_OPTION |
+-----------+------------------------+-----------+------------------------+-------------------+
| localhost | sales_manager | localhost | user001 | N |
| localhost | sales_person | localhost | sales_manager | N |
| localhost | sales_person | localhost | user003 | N |
| somehost | support_manager | somehost | support_manager_update | N |
| somehost | support_manager_update | localhost | user002 | N |
| somehost | support_person | localhost | user004 | N |
| somehost | support_person | somehost | support_manager | N |
+-----------+------------------------+-----------+------------------------+-------------------+
7 rows in set (0.00 sec)
※ロール自体はユーザと同様の扱い？
mysql> SELECT User, Host FROM mysql.user;
+------------------------+-----------+
| User | Host |
+------------------------+-----------+
| mysql.session | localhost |
| mysql.sys | localhost |
| root | localhost |
| sales_manager | localhost |
| sales_person | localhost |
| user001 | localhost |
| user002 | localhost |
| user003 | localhost |
| user004 | localhost |
| support_manager | somehost |
| support_manager_update | somehost |
| support_person | somehost |
+------------------------+-----------+
12 rows in set (0.00 sec)
2-5. ロールを使ってテーブルにアクセスする
ロールを割り当てたユーザを使ってテーブルにアクセスしてみます。
テーブルアクセス
※営業チームマネージャーでアクセス
$ mysql -u user001 -h localhost -p
Enter password:
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 8
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> SHOW GRANTS FOR user001@localhost;
+------------------------------------------------------------+
| Grants for user001@localhost |
+------------------------------------------------------------+
| GRANT USAGE ON *.* TO `user001`@`localhost` |
| GRANT `sales_manager`@`localhost` TO `user001`@`localhost` |
+------------------------------------------------------------+
2 rows in set (0.00 sec)
※現在有効なロールを確認⇒なし
mysql> SELECT CURRENT_ROLE();
+----------------+
| CURRENT_ROLE() |
+----------------+
| NONE |
+----------------+
1 row in set (0.00 sec)
※この状態でテーブルをSELECTしてみる⇒エラー
mysql> SELECT * FROM sales.emp_rating;
ERROR 1142 (42000): SELECT command denied to user 'user001'@'localhost' for table 'emp_rating'
※ロールを有効化する
mysql> SET ROLE sales_manager@localhost;
Query OK, 0 rows affected (0.00 sec)
mysql> SELECT CURRENT_ROLE();
+-----------------------------+
| CURRENT_ROLE() |
+-----------------------------+
| `sales_manager`@`localhost` |
+-----------------------------+
1 row in set (0.00 sec)
※もう一度SELECT⇒できた
mysql> SELECT * FROM sales.emp_rating;
+--------+-------------+--------+
| emp_id | rating_date | rating |
+--------+-------------+--------+
| 1 | 2017-08-01 | 88 |
| 2 | 2017-08-01 | 55 |
| 4 | 2017-08-01 | 0 |
+--------+-------------+--------+
3 rows in set (0.00 sec)
※UPDATEしてみる⇒できた
mysql> UPDATE sales.emp_rating SET rating=60 WHERE emp_id=2;
Query OK, 1 row affected (0.01 sec)
Rows matched: 1 Changed: 1 Warnings: 0
mysql> SELECT * FROM sales.emp_rating;
+--------+-------------+--------+
| emp_id | rating_date | rating |
+--------+-------------+--------+
| 1 | 2017-08-01 | 88 |
| 2 | 2017-08-01 | 60 |
| 4 | 2017-08-01 | 0 |
+--------+-------------+--------+
3 rows in set (0.00 sec)
※下位ロールで定義されている権限も有効
mysql> SELECT * FROM sales.order;
+----------+------------+-------------+-------------+------------+--------+
| order_id | product_id | order_count | customer_id | order_date | emp_id |
+----------+------------+-------------+-------------+------------+--------+
| 1 | 1 | 4 | 6 | 2017-07-12 | 1 |
| 2 | 3 | 2 | 10 | 2017-07-15 | 1 |
| 3 | 1 | 5 | 15 | 2017-07-16 | 2 |
| 4 | 2 | 10 | 21 | 2017-07-21 | 1 |
+----------+------------+-------------+-------------+------------+--------+
4 rows in set (0.00 sec)
※権限外テーブルへのアクセス⇒できない
mysql> SELECT * FROM support.support_case;
ERROR 1142 (42000): SELECT command denied to user 'user001'@'localhost' for table 'support_case'
※付与されていないロールは有効化できない
mysql> SET ROLE support_person@localhost;
ERROR 3530 (HY000): `support_person`@`localhost` is not granted to `user001`@`localhost`
※たとえ付与されているロールの下位ロールでも、単独ではSET ROLEできない
mysql> SET ROLE sales_person@localhost;
ERROR 3530 (HY000): `sales_person`@`localhost` is not granted to `user001`@`localhost`
ここまでが基本的な使い方です。
(2) では、デフォルトロール、8.0.2で追加されたmandatory roles（必須ロール？）などを取り上げます。
【おまけ】
MySQL 8.0関連投稿記事へのリンクを集めました。