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

前回では

　SQLの学習用にデータベースを作成してみる。（その１）ユーザーとロールの作成ではユーザーの作成をしたので、今回からテーブルを作成していきます。と思ったのですが、ユーザーとロールの続きです。

開発環境は前回と同じ
・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を作成する際に

CREATE USER　sam　IDENTIFIED WITH mysql_native_password BY 'チョメチョメ';

とするところを

CREATE USER　sam　IDENTIFIED BY 'チョメチョメ';

としたために認証方法がデフォルトで設定されたためですね、rootでログインして確認してみます。

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'に直しておきます。

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でログインし直して設定します。

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 ] ...
}

となっています。

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が見えるようになりました。

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

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を設定する必要があるようです。

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で設定します。

root

mysql> SET DEFAULT ROLE boatdb_dev to 'sam'@'localhost';
Query OK, 0 rows affected (0.01 sec)

samに切り替えます

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> 

見れてますね。
色々躓きますね。

次回こそは

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