0

As the title said, I'd like to get some information of the user who is executing the SQL statement in MySQL. For example, if some SQL statement is executed by a user called 'work', how could I got the user name and host with trigger before it is actually executed in MySQL.

Actually, I just want to monitor some actions, like DELETE, in MySQL. And I'm not sure the binlog can record the username and host who executed the SQL statement.

Hualiang Li
  • 65
  • 2
  • 7

2 Answers2

1

The user@host that you appear to be looking for is available from the USER() information function.

https://dev.mysql.com/doc/refman/5.6/en/information-functions.html#function_user

Michael - sqlbot
  • 169,571
  • 25
  • 353
  • 427
  • I'm not sure have u got my point. But what u've said could only get the information of yourself. e.g. I've connect my server with "work@192.168.1.103", when I execute "SELECT USER()", it will return "work@192.168.1.103". – Hualiang Li May 26 '15 at 09:52
  • You get your own information, because it's you, running the query. If you use `USER()` in the body of a trigger, the code will get the identity of the person who executed the query that caused the trigger to fire. If that isn't what you wanted to know, then you may want to expand your question to make the desired result more clear. – Michael - sqlbot May 26 '15 at 10:35
0

SELECT * FROM information_schema.PROCESSLIST WHERE USER="someuser";

Now it is up to you what you use. Write a cron job (linux) to store it into a file or write a MySQL event to enter it into a MySQL table.

Deep Kakkar
  • 5,831
  • 4
  • 39
  • 75
Revathi Muthukrishnan
  • 47
  • 9