MySQLにおいて片方のデータベースが無効になる。

Question

前回も似たようなことを質問しましたが再現性を確認出来ましたので新しくスレッドを建たせてもらっています。

MySQLで A と B のデータベースがあり、Aに権限付与をするとBの権限付与が無効になってしまい接続することが出来なくなってしまいます。

GRANT ALL PRIVILEGES ON A.* to hoge@localhost IDENTIFIED BY 'hoge';

同様にこのままAで実行したコードをBのデータベースにすると今度はAに接続が出来なくなってしまいます。

この現象について何かご存知の方がいましたら知恵をお貸しください。

Server version: 5.5.44 MySQL Community Server (GPL) by Remi

Answer 1

ただ、データベースのパスワード統一というのはセキュリティ的にいかがなものなのでしょうか。

このコメントから推測するに、データベース A と B で、同じ hoge@localhost というユーザーで、異なるパスワードで接続しようとしている、ということですね？

MySQL でそれはできません。MySQL のデータベースという単位は、他のRDBMSで言うところのスキーマのようなものだと思ったほうが良いです。

データベースが異なっていても、ユーザーが同じならパスワードも同じなので、

GRANT ALL PRIVILEGES ON A.* to hoge@localhost IDENTIFIED BY 'hoge';
GRANT ALL PRIVILEGES ON B.* to hoge@localhost IDENTIFIED BY 'fuga';

のように実行すると、hoge@localhost というユーザーが fuga というパスワードでデータベース A と B に接続できるようになります（２発めのGRANTで１発めで設定したパスワードが上書きされる）。

セキュリティ的な目的で、データベース A と B で別のパスワードを使いたいのなら、ユーザーも別にする必要があります。

Comments

[rera]

なんと...。
そうでしたのか。ずっと出来るかと思っていました。ありがとうございます。

Answer 2

GRANT a.* を実行した後と、
GRANT b.* を実行した後に、root で以下のクエリを実行すると結果はどうなりますか？

select * from mysql.db where host='localhost' and user='hoge'\G

以下のように、Dbの違うエントリが複数できていくはずです。

*************************** 1. row ***************************
                 Host: localhost
                   Db: a
                 User: hoge
                       .
                       .
*************************** 2. row ***************************
                 Host: localhost
                   Db: b
                 User: hoge
                       .
                       .

エントリが複数できないということは mysql.db テーブルの定義がおかしくなっているということです。
以下のように正しいプライマリーキーが mysql.db テーブルに設定されていることを確認してください。

SHOW CREATE TABLE mysql.db\G
*************************** 1. row ***************************
       Table: db
Create Table: CREATE TABLE `db` (
    .
    .
  PRIMARY KEY (`Host`,`Db`,`User`),

Comments

[rera]

コメントありがとうございます。
エントリも複数表示されており、プライマリーキーもきちんと設定されていました..。

Answer 3

GRANT ALL PRIVILEGES ON *.* to hoge@localhost IDENTIFIED BY 'hoge';

…とかで同時に権限を与えてみるとかどうでしょうか。

Comments

[rera]

そのコマンドで動作することを確認出来ました。
ただ、データベースのパスワード統一というのはセキュリティ的にいかがなものなのでしょうか。
サーバー含めこのようなことは初心者でして....。

[yu-ri]

そうですねぇ…自分もインフラ周りはまだそこまで詳しくないので、その辺はその道のプロにお答えいただくしかないかと思います。
興味深い事象ではあるので、引き続き調査は続けたいと思います。