トリガー内でテーブルの更新クエリ―をかけてきたホストのIPを取得する方法

Question

MySQL(ver5.6)であるテーブルにたいして更新クエリ―をかけてきたホストのIPあるいはホスト名を何らかの方法で取得し、別に用意したログ用テーブルにIP又はホスト名,タイムスタンプ、更新前のレコードの値をインサーとするトリガーを作りたいと考えています。
しかし、トリガー内でテーブルの更新クエリ―をかけたホストのIPを取得する方法が分かりません。
できればユーザー名もわかればありがたい。

トリガーに関する文法は理解していますが、ホストのIPの取得方法、関係するテーブルあるいはビューとカラム名が分かりません。

どなたか、教えていただければ幸いです。

Answer 1

tacsheavenさん、ありがとうございました。

おかげさまで、自己解決しました。

MySQL5.6リファレンスマニュアルに参考となる記述を見つけました。
https://dev.mysql.com/doc/refman/5.6/ja/information-functions.html

「トリガーおよびイベントには、SQL SECURITY 特性を定義するためのオプションがありません。したがって、このようなオブジェクトの場合、CURRENT_USER() はオブジェクトを定義したユーザーのアカウントを返します。呼び出したユーザーを返すには、USER() または SESSION_USER() を使用します。」

そこで、トリガー内のselectのWHERE句を以下のように変更したところ、トリガーを起動した接続ユーザーの情報を特定できました。

（変更前）SELECT user, host INTO @host_user,@host_IP FROM information_schema.processlist WHERE id = connection_id();
（変更後）SELECT user, host INTO @host_user,@host_IP FROM information_schema.processlist WHERE user = LEFT(USER(),INSTR(USER(),'@')-1) LIMIT 1;

create文は以下の通り

CREATE DEFINER=root@localhost TRIGGER table_AFTER_UPDATE AFTER UPDATE ON table FOR EACH ROW
BEGIN
SELECT user, host INTO @host_user,@host_IP FROM information_schema.processlist WHERE user = LEFT(USER(),INSTR(USER(),'@')-1) LIMIT 1;
INSERT INTO table_log (
host_user,
host_IP,
event,
......)
VALUES (
@host_user,
@host_IP,
"UPDATE",
......);
END

Answer 2

トリガー内で取得する CONNECTION_ID() の値が、トリガーを発火させたプロセスのIDであるならば、という条件が付きますが（トリガーを起動するプロセスが違うかどうか、試していないので不明です）、

SELECT user, host FROM information_scheme.processlist WHERE id = connection_id()

で接続側のホスト名と接続ユーザーが取れるかと思います。

Comments

[ochiaishiro]

tacsheavenさん、ありがとうございました。
残念ながら、下記のように、まだ目的が達成されていません。よろしくお願いいたします。

教えていただいたクエリ―をトリガー内に下記のように設定しました。
対象とするテーブルを、サーバー（rootとuserA）とクライアント（userB）から更新をかけたところ、以下のようになりました。

サーバー（root）：user=root,host=localhost:****3 ←５ケタの数字
サーバー（userA）：user=root,host=localhost:****4
クライアント（userB）：：user=root,host=localhost:****5

information_schema.processlistのuserは、トリガーを定義したuserが入っているようで、connection_id()の値はサーバーからの接続のようです。

hostの値の数字は何なのでしょう？
WorkbenchのClient ConnectionsタブのIdでもThread IDでもなさそうです。
ちなみに、WorkbenchのClient ConnectionsタブのUserとHostにはクライアントから接続しているユーザー名とのホスト名が表示されています。

トリガーのcreate文は以下の通り

CREATE DEFINER=`root`@`localhost` TRIGGER `table_AFTER_UPDATE` AFTER UPDATE ON `table` FOR EACH ROW
BEGIN
SELECT user, host INTO @host_user,@host_IP FROM information_schema.processlist WHERE id = connection_id();
INSERT INTO table_log (
host_user,
host_IP,
event,
......)
VALUES (
@host_user,
@host_IP,
"UPDATE",
......);
END

Answer 3

mysqlの実行はどうやっていますか？
(1)コマンドラインのmysqlクライアントをご利用でしょうか？
(2)それともapacheベースのphpやperlなどで実行しますか？
(3)もしくはODBCなどを通してアプリケーションからでしょうか？

(1)であればログインユーザーやホストは特定できますのでそれを参照してください
(2)や(3)の場合、実際の実行ユーザーはrootなど管理者権限ユーザーで
クライアントはその権限をカリているだけかもしれません。
その場合各クライアントの情報は、プログラム側で抜き出すことができると思います

Comments

[ochiaishiro]

workbennchから更新等をかける場合と
ODBCを通してアプリケーションから更新等をかける場合とがあります。
なお、アプリケーションからDBにログインする場合、登録してあるユーザアカウントで接続し、接続した後も接続したユーザアカウントで実行するようにしています。