MariaDBでロールを作った時にハマったこと

MariaDBでもロールが使用できると聞いて試してみたが、
OracleDBと違ってGRANT文で付与するだけでなく、
「SET ROLE」コマンドを使って明示的に設定をする必要がある模様。

---------------

#検証内容

1.USAGE権限のみ与えられているユーザーに対して
　iwate.juveに対するSELECT権限を付与したロールを作成し割当て。

```
MariaDB [(none)]> CREATE ROLE test_role;
Query OK, 0 rows affected (0.00 sec)

MariaDB [(none)]> SHOW GRANTS FOR 'you'@'localhost';
+------------------------------------------------------------------------------------------------------------+
| Grants for you@localhost                                                                                   |
+------------------------------------------------------------------------------------------------------------+
| GRANT USAGE ON *.* TO 'you'@'localhost' IDENTIFIED BY PASSWORD '*1A11AE440F0BFE14CF065EA776CEFA20B3BCF946' |
+------------------------------------------------------------------------------------------------------------+
1 row in set (0.01 sec)

MariaDB [(none)]> GRANT select ON iwate.juve TO test_role;
Query OK, 0 rows affected(0.00 sec)

MariaDB [(none)]> GRANT test_role TO 'you'@'localhost';
Query OK, 0 rows affected (0.00 sec)

MariaDB [(none)]> SHOW GRANTS FOR 'you'@'localhost';
+------------------------------------------------------------------------------------------------------------+
| Grants for you@localhost                                                                                   |
+------------------------------------------------------------------------------------------------------------+
| GRANT test_role TO 'you'@'localhost'                                                                       |
| GRANT USAGE ON *.* TO 'you'@'localhost' IDENTIFIED BY PASSWORD '*1A11AE440F0BFE14CF065EA776CEFA20B3BCF946' |
+------------------------------------------------------------------------------------------------------------+
2 rows in set (0.01 sec)
```

2.ロールの割当てまで完了したのでロールを付与したユーザーyouで
iwate DBに接続しようとしたところでエラーが発生。

```
[mysql@vmmaria01 ~]$ mysql -u you -p
Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MariaDB connection id is 26
Server version: 10.1.11-MariaDB-log Source distribution

Copyright (c) 2000, 2015, Oracle, MariaDB Corporation Ab and others.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

MariaDB [(none)]> use iwate
ERROR 1044 (42000): Access denied for user 'you'@'localhost' to database 'iwate'
MariaDB [(none)]>
```

3.FLUSH PRIVILEGESし忘れたのが原因かと思って実行してみたが、
　エラーは解消されず。

```
MariaDB [(none)]> FLUSH PRIVILEGES;
Query OK, 0 rows affected (0.00 sec)

MariaDB [(none)]> exit
Bye
[mysql@vmmaria01 ~]$ mysql -u you -p
Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MariaDB connection id is 31
Server version: 10.1.11-MariaDB-log Source distribution

Copyright (c) 2000, 2015, Oracle, MariaDB Corporation Ab and others.

Type 'help;' or '\h' or help. Type '\c' to clear the current input statement.

MariaDB [(none)]> use iwate
ERROR 1044 (42000): Access denied for user 'you'@'localhost' to database 'iwate'
```

4.オプション等の問題かと思ってリファレンスを見ていたところ、SET ROLEというコマンドを発見。
この内容を元にカレントロールを確認すると「NULL」になっていたので、
作成した「test_role」をカレントとして割り当てたらDBに接続できる様になった。
なんだこの仕様は･･･なぜGRANT文でロールを付与できる様にしているんだ！？

```
MariaDB [(none)]>
MariaDB [(none)]> SELECT CURRENT_ROLE;
+--------------+
| CURRENT_ROLE |
+--------------+
| NULL         |
+--------------+
1 row in set (0.00 sec)

MariaDB [(none)]>
MariaDB [(none)]> SET ROLE test_role;
Query OK, 0 rows affected (0.00 sec)

MariaDB [(none)]> SELECT CURRENT_ROLE;
+--------------+
| CURRENT_ROLE |
+--------------+
| test_role    |
+--------------+
1 row in set (0.00 sec)

MariaDB [(none)]> use iwate;
Database changed
MariaDB [iwate]> select * from juve;
+-------+----+------------+-----------+----------+
| id_no | no | fir_name   | fam_name  | position |
+-------+----+------------+-----------+----------+
|     1 | 10 | AAA        | AAA       | FW       |
|     2 | 8  | BBB        | BBB       | MF       |
|     3 | 1  | CCC        | CCC       | GK       |
+-------+----+------------+-----------+----------+
3 rows in set (0.00 sec)

MariaDB [iwate]> insert into juve values(4,'3','DDD','DDD','DF');
ERROR 1142 (42000): INSERT command denied to user 'you'@'localhost' for table 'juve'
MariaDB [iwate]>
```