MariaDBでユーザーアカウントの整理をしている際、突然以下のような権限エラーが発生するようになってしまった。

1ERROR 1045 (28000): Access denied for user 'user'@'%' (using password: YES)
2(userは削除したのとは全く別の存在するユーザー)

ちなみにこのエラーが発生したのは最初の接続時ではなくクエリの実行直後である。 そもそもこのエラーが最初の認証以外で起こること自体おかしい気がするが、 さらに言えばこのユーザーはちゃんと存在しているし、使用しているパスワードも完全に正しいのである。

ちなみにこのエラーを引き起こしていたクエリは普通のSELECT文だったのだが、 他のクエリと違ってビューに対するSELECTを発行していた。 調査したところ、どうもユーザーの削除によりビューの設定の整合性が失われ、 呼び出しているビューのセキュリティ機能で弾かれてしまっているらしい。

MySQLビューのセキュリティ

そもそも、MySQLのビューがSELECT文で呼び出された場合、 結果セットを作るためにさらに内部でもう一度SELECT文を呼び出すわけだが、 内部でSELECT文を実行する際の権限は、必ずしも現在のユーザーの権限が用いられるわけではない。

具体的には、CREATE VIEWで指定できるDEFINERとSQL SECURITYというパラメーターによって定まる。

• SQL SECURITY
  • ビューのSELECT文を実行するユーザーの決定方法を指定する。DEFINERまたはINVOKERを指定できる
  • SQL SECURITY = DEFINERの場合、DEFINER = [user]で指定されたユーザーの権限を用いる
  • SQL SECURITY = INVOKERの場合、ビューを呼び出したユーザーの権限を用いる
• DEFINER
  • SQL_SECURITY = DEFINERのときに参照されるユーザー

このように、ビューを呼び出す際には、現在のユーザーの権限だけでなくDEFINERのユーザーの権限も参照する可能性があるのである。

DEFINERとユーザー削除

CREATE VIEWでビューを作る際に上記のパラメータを指定することはまず無いと思うが、 この場合、CREATE VIEWを呼び出したユーザーがDEFINERとなり、SQL SECURITYもDEFINERにセットされる。

では、このCREATE VIEWを呼び出したユーザーを後から削除した場合どうなるかというと、 ビューのDEFINERは自動で書き換わることはないため、 DEFINERは存在しないユーザーを参照することになる。 これにより、ビューを呼び出した際、 存在しないユーザーの権限を参照しようとするため、ERROR 1045 Access deniedが発生してしまうのである。

このようにして先述の現象が起こるというわけである。 (一つ納得がいかないのが、あたかも現在のユーザーのログイン権限が無いように表示されてしまうことであるが、 これ自体は仕様としか言いようがないのだろう。)

解決策

DEFINERに指定されたユーザーが存在しないのが問題なので、ALTER TABLEステートメントによりビューを再定義する。

1# はじめにビューの定義を持ってくる必要がある
2> SHOW CREATE VIEW my_view;
3+---------+----------------------
4| View    | Create View 
5+---------+---------------------
6| my_view | CREATE ALGORITHM=UNDEFINED DEFINER=`originaldefiner`@`%` SQL SECURITY DEFINER VIEW `my_view` AS select ...
7# ↑のselect文をコピーして用いる
8> ALTER VIEW `my_view` AS select ...

これにより、DEFINERが現在のユーザー(存在するユーザー)に置き換わるため、問題が解決するはずである。 なお、ALTER VIEWを実行する際にはCREATE VIEW, DROPおよびビューの参照先テーブルのSELECT権限が必要となるので注意。

参考文献

MySQL :: MySQL 8.0 Reference Manual :: 13.1.23 CREATE VIEW Statement

MySQLでViewを使うときに注意すること - よかろうもん！