MySQL の View や Stored Procedure がダンプできない

MySQL でリモート接続で定義したストアドがローカルから接続するとダンプできないという不思議な問題にどっぷりとハマってしまったのでメモ。

ユーザーが root だったりすると強制的にダンプできるので気づかないことも多いですが、MySQL の View や Stored Procedure には DEFINER という特性がついていて、誰がそれを定義したのかというのを覚えておくようになっています。これによって他のユーザーから View やストアドの内容を勝手に書き換えられるのを防ぐことができるので、この機能自体はセキュリティ的に有用です。

ちなみに DEFINER のデフォルト値は CURRENT_USER です。

これだけだと、ユーザーが同じであれば接続元をリモートからローカルに変えても問題なさそうという感じがしますが、ここにちょっとした落とし穴があります。というのも MySQL の CURRENT_USER はuser_name の値とhost_name がセットになったものだからです。

MySQL :: MySQL 5.1 リファレンスマニュアル :: 17.2.1 CREATE PROCEDUREおよびCREATE FUNCTION 構文

user 値を附与する場合、それを ' user_name'@' host_name ' フォーマット（GRANT ステートメントに使用したと同じフォーマット）の中にあるMySQLアカウントにすべきです。user_name の値とhost_name の値が両方共必要です。CURRENT_USERをCURRENT_USER()として附与することもできます。DEFINER の初期値はCREATE PROCEDUREもしくはCREATE FUNCTIONもしくはステートメントを実行するユーザです。(これはDEFINER = CURRENT_USERと同じです。)

実際にローカル接続とリモート接続の時の CURRENT_USER を SELECT してみると分かりますが、こんな感じになります。
つまり、ローカル接続のときとリモート接続の時で違う DEFINER になっているわけです。

つまり、このホスト名の部分は MySQL の認証機構の根幹に関わっているため省略することができません。
また、DEFINER の値は View の呼出に時に SQL の実行ユーザーとして使われることもあります^*1。

とりあえず、ストアドについては下記の SQL で DEFINER を一括してアップデートすることができることが分かりました。

• ^*1: SQL SECURITY { DEFINER | INVOKER } が DEFINER と指定されている場合（INVOKER の場合は呼び出したユーザーの権限で実行となる）。