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

最近の目標の一つは「生の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)](http://db.yulib.com/mysql/000052.html)

今回は両方とも``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)
```

今回はここまで。