最近の目標の一つは「生の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は違うのだろうか？」と思っていたら、やはり若干動きが異なるようだ。（匿名ログイン時のみ、らしい。）
今回は両方とも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)
今回はここまで。