突然MySQLのVIEWが参照できなくなって困った話

経緯

前日までは問題なく使えていたのに、ある時点から突然データベースのアクセス権エラーが発生してとあるWebアプリが使えなくなってしまったのです。
これは困ったってことで解析してみると、そのアプリの中でVIEWを参照したときにエラーになっている模様。
管理者権限でログインして参照すると問題なく見えているので、Webアプリで利用している一般ユーザーのアクセス権の問題のようでした。
ですが、当該ユーザーの権限をいじったつもりはまったくありません。
またVIEWやVIEWが参照しているテーブルについてもいじっていません。
なぜ・・・・・




.

思い当たる節

ひとつだけ思い当たるとしたら、現象が発生する直前に離職した開発者用のユーザーをDROPしてたことです。
ですが、無知な私はそれとこの現象が直接結びつくことに気づいていませんでした。

答えはVIEWの作り方

CREATE VIEW文の詳細な解説はマニュアルを見ていただくとして、ここで重要になるのは「SQL SECURITY」句です。

以下マニュアル抜粋

SQL SECURITY特徴はビューの実行においてビューに対するアクセス権限をチェックする時、どのMySQLアカウントを使用すべきかを決定します。有効な特徴値はDEFINERとINVOKERです。これらはそれぞれ、ビューがそれを定義か起動したユーザによって実行可能でなければならないことを示します。SQL SECURITYのデフォルト値は DEFINERです。
～中略～
ビューの実行において、SQL SECURITY特性がDEFINERあるいはINVOKERであるか否かによって、ビューがアクセスしたオブジェクトに対する権限が、ビュー生成者あるいはインボーカーが保持する権限と照合してそれぞれチェックされます。

まぁなんだかよくわかりませんが、つまり「SQL SECURITY DEFINER」で作られたVIEWを参照するときは、「DEFINERが保持する権限と照合してチェックされる」らしいです。
ところがこのDEFINERにあたるユーザーがDROPされていたため、権限がチェックできずエラーになってしまうということではなかろうかと思われました。
VIEWを作るときはたいていphpMyAdminで作っていて、「SQL SECURITY」なんか指定するわけもなく、デフォルトにしていたのです。

暫定的対策

対象のVIEWのSQL文をInformation SchemeのVIEWSから抜き出しておきます。
で、有効なSUPERなユーザーでREPLACE VIEW文で上書き再定義します。
その際、念のため「SQL SECURITY INVOKER」にしておきます。

また、他にDROPしたユーザーが定義したVIEWがないか、同じくVIEWSの中をチェックしておきます。

恒久的対策

さて、こういうケースは普通どうしているのでしょうか。
「SQL SECURITY INVOKER」にすれば解決ですが、デフォルトがそうではないのが気になります。
かといって離職した開発者ユーザーを残しておくのも個人的には変だと思うのです。
VIEWやストアド作成はそれ専用のユーザーで行うのでしょうか。

悩んでますが、とりあえずは安全側に倒すため、すべてのVIEWを「SQL SECURITY INVOKER」で再定義しておくことにします。
それもそれで面倒くさいけど。


みなさんどうしているのか、教えて欲しいです。


.