Mysql – thesql logging activity from specific user or ip

auditloggingmonitoringMySQLwireshark

I have mysql server.

The server is accessed by my application, and by external auditor (person using mysql workbench).
The auditor has specific user and password and dedicated IP and it is granted only for select privileges.

I need to log the activities from the auditor.

I have read similar questions here on serverfault, but I did not saw any acceptable answers that it is possible to do sql logging by the sql server only for a specific user.

But is it possible and how, to log the network packages from the auditors IP, but only the text (the queries), not the complete packages ?

Any other solutions how can I monitor what the auditor was doing on the server ?

Best Answer

But is it possible and how, to log the network packages from the auditors IP, but only the text (the queries), not the complete packages?

Yes, it is possible:

tcpdump -s0 -i eth0 src host auditors.ip and dst port 3306 -w mysql.pcap

then you can filter only the MySQL queries by running:

while read stream; do \
tshark -qz follow,tcp,ascii,$stream -r mysql.pcap; done \
< <(tshark -R "mysql" -T fields -e tcp.stream -r mysql.pcap | sort | uniq)
Related Topic