読者です 読者をやめる 読者になる 読者になる

年中アイス

いろいろつらつら

GRANTで起こすDB接続障害

MySQL

MySQLの権限追加でGRANT文を使った時に、DB接続障害を起こす失敗の仕方です。

GRANTSは、MySQLユーザに対しての権限を付与する構文です。

MySQL :: MySQL 5.6 Reference Manual :: 13.7.1.4 GRANT Syntax

よく紹介されているのは、CREATE USERを使ってユーザを作ってから、GRANTで権限を付与する流れです。 ただ、GRANTで、うっかりホスト名を厳密にすると、DBに接続できなくなります。

何が起きるか

試していきます。2つのホストを使います。試す環境の準備は省略します。*1

  • 192.168.50.11 mysql serverが稼働中
  • 192.168.50.12 mysqlクライアントの接続元

まずrootユーザで接続し、DBを作成、ユーザを作って権限を付与します

CREATE DATABASE mydb;
CREATE USER 'myuser' IDENTIFIED BY 'mypassword';
GRANT SELECT ON mydb.* TO 'myuser';

192.168.50.12から以下のように接続できます。

$ mysql -umyuser -h192.168.50.11 -p
mysql>

rootユーザで、以下のコマンドでUPDATE権限を付ける際に、ホストの指定(192.168.50.%)を追加します。

GRANT UPDATE ON mydb.* TO 'myuser'@'192.168.50.%';

そうすると、前述の接続コマンドで接続しても、以下のように接続できません

$ mysql -umyuser -h192.168.50.11 -p
Enter password:
ERROR 1045 (28000): Access denied for user 'myuser'@'192.168.50.12' (using password: YES)

なぜ?

この事象がなぜ発生するかというと、理由は簡単です。以下のようにユーザ一覧を見るとはっきりします

SELECT User,Host,Password FROM mysql.user WHERE User = 'myuser';
+--------+--------------+-------------------------------------------+
| User   | Host         | Password                                  |
+--------+--------------+-------------------------------------------+
| myuser | %            | *FABE5482D5AADF36D028AC443D117BE1180B9725 |
| myuser | 192.168.50.% |                                           |
+--------+--------------+-------------------------------------------+
2 rows in set (0.00 sec)

そうです。'myuser'と 'myuser'@'192.168.50.%'は、別扱いなのです。考えてみれば当たり前です。

気をつけること

気をつけないといけないのは、GRANTは、存在しないユーザとホストの組み合わせを指定すると、そのユーザを作成することです。*2 さらに、パスワード指定がない場合に、空パスワードでユーザを作成することです。 そのため、この場合は、厳密になったHostに該当する場合に、今まで使えていたパスワードと一致しないので接続に失敗します。

DBにつながらない。運用中のサービスだと恐ろしいですね。

最初から厳密に、ユーザと接続元を定義している場合は発生しにくいかなと思います。最初つけていなくて、あとからうっかりユーザは一緒だけど接続元だけで権限を分けよう!という気持ちになると、やったあとにひどいことになります。

*1:たまたま手元にあったvagrant環境はMySQL 5.6.19でした。

*2:ここではHostだけ変えてますが、ユーザ名を間違えると、そのユーザが作成されます