質問をすることでしか得られない、回答やアドバイスがある。

15分調べてもわからないことは、質問しよう!

ただいまの
回答率

89.98%

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

解決済

回答 3

投稿

  • 評価
  • クリップ 0
  • VIEW 833

ochiaishiro

score 24

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

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

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

  • 気になる質問をクリップする

    クリップした質問は、後からいつでもマイページで確認できます。

    またクリップした質問に回答があった際、通知やメールを受け取ることができます。

    クリップを取り消します

  • 良い質問の評価を上げる

    以下のような質問は評価を上げましょう

    • 質問内容が明確
    • 自分も答えを知りたい
    • 質問者以外のユーザにも役立つ

    評価が高い質問は、TOPページの「注目」タブのフィードに表示されやすくなります。

    質問の評価を上げたことを取り消します

  • 評価を下げられる数の上限に達しました

    評価を下げることができません

    • 1日5回まで評価を下げられます
    • 1日に1ユーザに対して2回まで評価を下げられます

    質問の評価を下げる

    teratailでは下記のような質問を「具体的に困っていることがない質問」、「サイトポリシーに違反する質問」と定義し、推奨していません。

    • プログラミングに関係のない質問
    • やってほしいことだけを記載した丸投げの質問
    • 問題・課題が含まれていない質問
    • 意図的に内容が抹消された質問
    • 広告と受け取られるような投稿

    評価が下がると、TOPページの「アクティブ」「注目」タブのフィードに表示されにくくなります。

    質問の評価を下げたことを取り消します

    この機能は開放されていません

    評価を下げる条件を満たしてません

    評価を下げる理由を選択してください

    詳細な説明はこちら

    上記に当てはまらず、質問内容が明確になっていない質問には「情報の追加・修正依頼」機能からコメントをしてください。

    質問の評価を下げる機能の利用条件

    この機能を利用するためには、以下の事項を行う必要があります。

回答 3

check解決した方法

+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

投稿

  • 回答の評価を上げる

    以下のような回答は評価を上げましょう

    • 正しい回答
    • わかりやすい回答
    • ためになる回答

    評価が高い回答ほどページの上位に表示されます。

  • 回答の評価を下げる

    下記のような回答は推奨されていません。

    • 間違っている回答
    • 質問の回答になっていない投稿
    • スパムや攻撃的な表現を用いた投稿

    評価を下げる際はその理由を明確に伝え、適切な回答に修正してもらいましょう。

+1

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

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

投稿

  • 回答の評価を上げる

    以下のような回答は評価を上げましょう

    • 正しい回答
    • わかりやすい回答
    • ためになる回答

    評価が高い回答ほどページの上位に表示されます。

  • 回答の評価を下げる

    下記のような回答は推奨されていません。

    • 間違っている回答
    • 質問の回答になっていない投稿
    • スパムや攻撃的な表現を用いた投稿

    評価を下げる際はその理由を明確に伝え、適切な回答に修正してもらいましょう。

  • 2017/06/14 18:21

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

    キャンセル

+1

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

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


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

投稿

編集

  • 回答の評価を上げる

    以下のような回答は評価を上げましょう

    • 正しい回答
    • わかりやすい回答
    • ためになる回答

    評価が高い回答ほどページの上位に表示されます。

  • 回答の評価を下げる

    下記のような回答は推奨されていません。

    • 間違っている回答
    • 質問の回答になっていない投稿
    • スパムや攻撃的な表現を用いた投稿

    評価を下げる際はその理由を明確に伝え、適切な回答に修正してもらいましょう。

  • 2017/06/21 11:38

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

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

    サーバー(root):user=root,host=localhost:****3 ←5ケタの数字
    サーバー(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

    キャンセル

15分調べてもわからないことは、teratailで質問しよう!

  • ただいまの回答率 89.98%
  • 質問をまとめることで、思考を整理して素早く解決
  • テンプレート機能で、簡単に質問をまとめられる