SQLの学習用にデータベースを作成してみる。（その２）ユーザーとロールの作成２


# 前回では
　[SQLの学習用にデータベースを作成してみる。（その１）ユーザーとロールの作成](https://qiita.com/SAMisnotMydog/items/ec95fb594dd3ca5cd7a6)ではユーザーの作成をしたので、今回からテーブルを作成していきます。と思ったのですが、ユーザーとロールの続きです。

開発環境は前回と同じ
・macOS Monterey (12.2.1)
・MacBookPro (2019)
・VSCode (1.64.2)  拡張機能：MySQL　(v0.4.1),Python (v2022.0.1814523869)他
・Python (3.9.7)
です。

## 今回のお品書き
### 1.ユーザー作成の修正
### 2.ロールの再割り当て

となっております。
## 1.ユーザー作成の修正
　新しい作業用ユーザー`sam`で作業をしようとしたら、またVscodeでMySQLの拡張機能からデータベースにアクセス出来ませんでした、`root`では出来るのに。。。
　ユーザー`sam`を作成する際に
```SQL
CREATE USER　sam　IDENTIFIED WITH mysql_native_password BY 'チョメチョメ';
```
とするところを
```SQL
CREATE USER　sam　IDENTIFIED BY 'チョメチョメ';
```
としたために認証方法がデフォルトで設定されたためですね、`root`でログインして確認してみます。
```SQL:root
mysql> SELECT user, host, plugin FROM user;
+------------------+-----------+-----------------------+
| user             | host      | plugin                |
+------------------+-----------+-----------------------+
| boatdb_dev       | %         | caching_sha2_password |
| sam              | %         | caching_sha2_password |
| mysql.infoschema | localhost | caching_sha2_password |
| mysql.session    | localhost | caching_sha2_password |
| mysql.sys        | localhost | caching_sha2_password |
| root             | localhost | mysql_native_password |
+------------------+-----------+-----------------------+
6 rows in set (0.00 sec)
```
やはり、認証方法が`caching_sha2_password`となってしまってます。
これではVSCodeからログインできません。
ホスト名も`'%'`なので、これはこれでいいのかもしれませんが、しばらくはローカル環境からなので`'localhost'`に直しておきます。
```SQL:root
mysql> ALTER USER 'sam' IDENTIFIED WITH mysql_native_password BY 'チョメチョメ';
Query OK, 0 rows affected (0.00 sec)
mysql> RENAME USER 'sam'@'%' TO 'sam'@'localhost';
Query OK, 0 rows affected (0.00 sec)
mysql> SELECT user, host, plugin FROM user;
+------------------+-----------+-----------------------+
| user             | host      | plugin                |
+------------------+-----------+-----------------------+
| boatdb_dev       | %         | caching_sha2_password |
| mysql.infoschema | localhost | caching_sha2_password |
| mysql.session    | localhost | caching_sha2_password |
| mysql.sys        | localhost | caching_sha2_password |
| root             | localhost | mysql_native_password |
| sam              | localhost | mysql_native_password |
+------------------+-----------+-----------------------+
6 rows in set (0.00 sec)
```
と認証方法とついでにホスト名も変更して直しておきました。
VSCodeでもログイン確認しました。
## 2.ロールの再割り当て
さて、今度は`sam`から`boatraceDB`が見えません。
ロールは付与してそれで良いと思っていたら、有効化が必要とあります。
`sam`でログインし直して設定します。

```SQL:sam
mysql> SHOW GRANTS;
+---------------------------------------------+
| Grants for sam@localhost                    |
+---------------------------------------------+
| GRANT USAGE ON *.* TO `sam`@`localhost`     |
| GRANT `boatdb_dev`@`%` TO `sam`@`localhost` |
+---------------------------------------------+
2 rows in set (0.01 sec)

mysql> SHOW DATABASES;
+--------------------+
| Database           |
+--------------------+
| information_schema |
+--------------------+
1 row in set (0.00 sec)
```
ロールは付与されています。
`boatraceDB`はまだ見えません。
ロールを有効化します。
`SET ROLE ロール名`です。レファレンスでは
```
SET ROLE {
    DEFAULT
  | NONE
  | ALL
  | ALL EXCEPT role [, role ] ...
  | role [, role ] ...
}
```
となっています。
```SQL:sam
mysql> SET ROLE boatdb_dev;
Query OK, 0 rows affected (0.00 sec)

mysql> SHOW DATABASES;
+--------------------+
| Database           |
+--------------------+
| boatraceDB         |
| information_schema |
+--------------------+
2 rows in set (0.00 sec)
```
ロールが有効化され、`boatraceDB`が見えるようになりました。

ところがです、このロール権限の有効化はログアウトすると無効になってしまう一時的なものでした。
一度ログアウトして確かめます。
```SQL:sam
mysql> exit;
Bye

samisnotmaydog@MacBook ~ % mysql -u sam -p

〜略〜

mysql> SHOW DATABASES;
+--------------------+
| Database           |
+--------------------+
| information_schema |
+--------------------+
1 row in set (0.01 sec)

mysql> SELECT CURRENT_ROLE();
+----------------+
| CURRENT_ROLE() |
+----------------+
| NONE           |
+----------------+
1 row in set (0.00 sec)
```
権限がなくなってます。
　ログイン時から`sam`が`boatdb_dev`ロールを持つようにするためには、`sam`の`DEFAULT ROLE`に`boatdb_dev`を設定する必要があるようです。

https://dev.mysql.com/doc/refman/8.0/ja/set-default-role.html
>SET DEFAULT ROLE は、ALTER USER ... DEFAULT ROLE の代替構文です (セクション13.7.1.1「ALTER USER ステートメント」 を参照)。 ただし、ALTER USER では単一のユーザーに対してのみデフォルトを設定できますが、SET DEFAULT ROLE では複数のユーザーに対してデフォルトを設定できます。 一方、ALTER USER ステートメントのユーザー名として CURRENT_USER を指定できますが、SET DEFAULT ROLE のユーザー名は指定できません。

`SET DEFAULT ROLE`でも`ALTER USER ... DEFAULT ROLE `でも良いようです。
`root`で設定します。
```SQL:root
mysql> SET DEFAULT ROLE boatdb_dev to 'sam'@'localhost';
Query OK, 0 rows affected (0.01 sec)
```
samに切り替えます
```SQL:sam
mysql> SELECT CURRENT_ROLE();
+------------------+
| CURRENT_ROLE()   |
+------------------+
| `boatdb_dev`@`%` |
+------------------+
1 row in set (0.00 sec)

mysql> SHOW GRANTS;
+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Grants for sam@localhost                                                                                                                                                          |
+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| GRANT USAGE ON *.* TO `sam`@`localhost`                                                                                                                                           |
| GRANT SELECT, INSERT, UPDATE, DELETE, CREATE, DROP, REFERENCES, INDEX, ALTER, LOCK TABLES, EXECUTE, CREATE VIEW, SHOW VIEW, EVENT, TRIGGER ON `boatracedb`.* TO `sam`@`localhost` |
| GRANT `boatdb_dev`@`%` TO `sam`@`localhost`                                                                                                                                       |
+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
3 rows in set (0.00 sec)

mysql> SHOW DATABASES;
+--------------------+
| Database           |
+--------------------+
| boatraceDB         |
| information_schema |
+--------------------+
2 rows in set (0.00 sec)

mysql> 
```
見れてますね。
色々躓きますね。

# 次回こそは
　ロールの設定に思いのほか時間をとられてしまって、なかなかテーブルの作成にたどり着けませんが、次は大丈夫そうです。