最近の目標の一つは「生のSQLに慣れる」こと。
今回はユーザー登録・権限付与のSQLを叩いてみた。

ちなみに使用データベースはMySQL(5.7.22)だ。

起動

ApacheとMySQLを起動する

$ sudo apachectl start
Password:

$ mysql.server start
Starting MySQL
. SUCCESS!

MySQLにログインする

ユーザーはyukiを使用する。

$ mysql -u yuki -p
Enter password: 
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 8
Server version: 5.7.22 Homebrew

Copyright (c) 2000, 2018, 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.

ユーザーの登録状況

ユーザーの権限を確認する

まずはrootから。ユーザー名は'root'@'localhost'とroot@localhostのどちらでも表示される。

mysql> SHOW GRANTS FOR 'root'@'localhost';
+---------------------------------------------------------------------+
| Grants for root@localhost                                           |
+---------------------------------------------------------------------+
| GRANT ALL PRIVILEGES ON *.* TO 'root'@'localhost' WITH GRANT OPTION |
| GRANT PROXY ON ''@'' TO 'root'@'localhost' WITH GRANT OPTION        |
+---------------------------------------------------------------------+
2 rows in set (0.00 sec)

次は、yukiの権限を確認する。ちなみに現在のユーザーはCURRENT_USER()で表示可能だ。

mysql> SHOW GRANTS FOR CURRENT_USER();
+---------------------------------------------------+
| Grants for yuki@localhost                         |
+---------------------------------------------------+
| GRANT ALL PRIVILEGES ON *.* TO 'yuki'@'localhost' |
+---------------------------------------------------+
1 row in set (0.00 sec)

ユーザーの一覧を表示する

mysql.userテーブルからUserカラムとHostカラムを表示させる。

mysql> SELECT User,Host FROM mysql.user;
+---------------+-----------+
| User          | Host      |
+---------------+-----------+
| mysql.session | localhost |
| mysql.sys     | localhost |
| root          | localhost |
| yuki          | localhost |
+---------------+-----------+
4 rows in set (0.01 sec)

現在のユーザーを確認する

「USERとCURRENT_USERは違うのだろうか？」と思っていたら、やはり若干動きが異なるようだ。（匿名ログイン時のみ、らしい。）

接続時に指定されたユーザー名を確認する (USER)

今回は両方ともyuki@localhostで表示されている。

mysql> SELECT USER(), CURRENT_USER();
+----------------+----------------+
| USER()         | CURRENT_USER() |
+----------------+----------------+
| yuki@localhost | yuki@localhost |
+----------------+----------------+
1 row in set (0.00 sec)

MySQLのバージョンを確認する

mysql> SELECT VERSION();
+-----------+
| VERSION() |
+-----------+
| 5.7.22    |
+-----------+
1 row in set (0.00 sec)

ユーザーの登録

新規ユーザーを登録する

ユーザーはCREATE USER文で追加できるが、以下のSQLではユーザー名をyuki_2@localhostとすべきところをyuki_2にしてしまった。

これではユーザー一覧を表示すると、Hostカラムが%で登録されてしまう。しかも、登録自体はエラーにならない。

しかし、今回はパスワード部分でエラーが起きているので最初にこれを解決することにした。

mysql> CREATE USER yuki_2 IDENTIFIED BY 'パスワード';
ERROR 1819 (HY000): Your password does not satisfy the current policy requirements

現在はvalidate_password_policyがMEDIUMになっている。これだとパスワードの制約が厳しくなるので、これをLOWに変更する。（MySQLを再起動すると再びMEDIUMに戻されるので、要注意。）

mysql> SHOW GLOBAL VARIABLES LIKE 'validate%';
+--------------------------------------+--------+
| Variable_name                        | Value  |
+--------------------------------------+--------+
| validate_password_check_user_name    | OFF    |
| validate_password_dictionary_file    |        |
| validate_password_length             | 8      |
| validate_password_mixed_case_count   | 1      |
| validate_password_number_count       | 1      |
| validate_password_policy             | MEDIUM |
| validate_password_special_char_count | 1      |
+--------------------------------------+--------+
7 rows in set (0.01 sec)

mysql> SET GLOBAL validate_password_policy=LOW;
Query OK, 0 rows affected (0.00 sec)

改めて確認する。

mysql> SHOW GLOBAL VARIABLES LIKE 'validate%';
+--------------------------------------+-------+
| Variable_name                        | Value |
+--------------------------------------+-------+
| validate_password_check_user_name    | OFF   |
| validate_password_dictionary_file    |       |
| validate_password_length             | 8     |
| validate_password_mixed_case_count   | 1     |
| validate_password_number_count       | 1     |
| validate_password_policy             | LOW   |
| validate_password_special_char_count | 1     |
+--------------------------------------+-------+
7 rows in set (0.01 sec)

再度挑戦する。

mysql> CREATE USER yuki_2 IDENTIFIED BY 'パスワード';
Query OK, 0 rows affected (0.00 sec)

今度は上手くいった。（本当はyuki_2@localhostで登録すべき。）

mysql> GRANT ALL ON *.* TO yuki_2@localhost;
ERROR 1045 (28000): Access denied for user 'yuki'@'localhost' (using password: YES)

続けて上記のGRANT文で権限をyuki_2@localhostに与えたかったが、失敗した。それもそのはず、現在はyuki_2@localhostというユーザーは存在しないからだ。

しかし、エラーメッセージはAccess denied for user 'yuki'@'localhost'となっている。これは「現在のユーザーはGRANT文の実行権限が無い。（アクセス拒否されている。）」という意味だ。まずはこれを解決する。

とりあえず一旦ログアウトする。

mysql> exit
Bye

権限の付与

今度はrootでログインする。

$ mysql -u root -p
Enter password: 
# 以下省略

rootとyuki、今一度それぞれの権限を確認する。

mysql> SHOW GRANTS FOR 'root'@'localhost';
+---------------------------------------------------------------------+
| Grants for root@localhost                                           |
+---------------------------------------------------------------------+
| GRANT ALL PRIVILEGES ON *.* TO 'root'@'localhost' WITH GRANT OPTION |
| GRANT PROXY ON ''@'' TO 'root'@'localhost' WITH GRANT OPTION        |
+---------------------------------------------------------------------+
2 rows in set (0.00 sec)

mysql> SHOW GRANTS FOR 'yuki'@'localhost';
+---------------------------------------------------+
| Grants for yuki@localhost                         |
+---------------------------------------------------+
| GRANT ALL PRIVILEGES ON *.* TO 'yuki'@'localhost' |
+---------------------------------------------------+
1 row in set (0.00 sec)

GRANT文を実行するためにはWITH GRANT OPTIONを付与しなければならない。現在はrootのみ権限が付与されているので、これをyukiにも付与する。

mysql> GRANT ALL ON *.* TO yuki@localhost WITH GRANT OPTION;
Query OK, 0 rows affected (0.00 sec)

確認する。

mysql> SHOW GRANTS FOR yuki@'localhost';
+---------------------------------------------------------------------+
| Grants for yuki@localhost                                           |
+---------------------------------------------------------------------+
| GRANT ALL PRIVILEGES ON *.* TO 'yuki'@'localhost' WITH GRANT OPTION |
+---------------------------------------------------------------------+
1 row in set (0.00 sec)

rootでの作業はこれで終了する。

mysql> exit
Bye

再度yukiでログインして作業を行う。

$ mysql -u yuki -p
Enter password: 
# 以下省略

先ほどyuki_2は登録したが、yuki_2@localhostはまだ登録されていない。

未登録のユーザーに権限を付与する場合は、必ずGRANT文の末尾にIDENTIFIED BY 'パスワード'を加える。これでユーザーの作成と権限の付与が同時に実行可能だ。

mysql> GRANT ALL ON *.* TO yuki_2@localhost IDENTIFIED BY 'パスワード';
Query OK, 0 rows affected, 1 warning (0.01 sec)

ちなみに、未登録のユーザーにIDENTIFIED BY 'パスワード'無しのGRANT文を実行すると以下のエラーが発生する。（パスワードのエラーとして扱われているようだ。）

mysql> GRANT ALL ON *.* TO yuki_2@localhost;
ERROR 1819 (HY000): Your password does not satisfy the current policy requirements

yuki_2@localhostの権限を確認する。

mysql> SHOW GRANTS FOR yuki_2@'localhost';
+-----------------------------------------------------+
| Grants for yuki_2@localhost                         |
+-----------------------------------------------------+
| GRANT ALL PRIVILEGES ON *.* TO 'yuki_2'@'localhost' |
+-----------------------------------------------------+
1 row in set (0.00 sec)

今度はREVOKE文を使用し、逆に権限を削除してみる。

mysql> REVOKE ALL ON *.* FROM yuki_2@localhost;
Query OK, 0 rows affected (0.00 sec)

mysql> SHOW GRANTS FOR 'yuki_2'@'localhost';
+--------------------------------------------+
| Grants for yuki_2@localhost                |
+--------------------------------------------+
| GRANT USAGE ON *.* TO 'yuki_2'@'localhost' |
+--------------------------------------------+
1 row in set (0.00 sec)

ユーザーの削除

今一度、ユーザーの一覧を表示する。

mysql> SELECT User,Host FROM mysql.user;
+---------------+-----------+
| User          | Host      |
+---------------+-----------+
| yuki_2        | %         |
| mysql.session | localhost |
| mysql.sys     | localhost |
| root          | localhost |
| yuki          | localhost |
| yuki_2        | localhost |
+---------------+-----------+
6 rows in set (0.00 sec)

ユーザーyuki_2が２つ作成されている。これはHostを指定した状態、指定していない状態でそれぞれ登録されているからだ。

ユーザーを削除する

DROP USER文でユーザーを削除する。

mysql> DROP USER yuki_2@localhost;
Query OK, 0 rows affected (0.00 sec)

Hostを指定しなかったユーザーはユーザー名のみで削除を実行する。

mysql> DROP USER yuki_2;
Query OK, 0 rows affected (0.00 sec)

確認のため、ユーザーの一覧を表示させる。両ユーザーとも完全に削除されたようだ。

mysql> SELECT User,Host FROM mysql.user;
+---------------+-----------+
| User          | Host      |
+---------------+-----------+
| mysql.session | localhost |
| mysql.sys     | localhost |
| root          | localhost |
| yuki          | localhost |
+---------------+-----------+
4 rows in set (0.01 sec)

おまけ

ログイン時のパスワード入力について、何かの参考になれば。

入力したパスワードが間違っている場合

(using password: YES)と表示される。

$ mysql -u root -p
Enter password: 
ERROR 1045 (28000): Access denied for user 'root'@'localhost' (using password: YES)

パスワードを入力しなかった場合

(using password: NO)と表示される。

$ mysql -u root -p
Enter password: 
ERROR 1045 (28000): Access denied for user 'root'@'localhost' (using password: NO)

今回はここまで。

【SQLに慣れる】ユーザー登録・権限付与

起動