Detecting direct changes in MySQL through

I have a PHP application that talks to Mysql. The application connects to MySQL using a username and password. Anyone who makes changes to the MySQL database through the application can get their username logged and this can be later audited. However, sometimes we need to do some operations from the backend , ie directly into the database. What I want to know is that is is possible to log the information about users logging into the DB directly( probably using phpMyAdmin ) and making changes to specific tables and how this information can be viewed later ? Further, is it possible that if the changes are made to spec开发者_开发问答ific tables directly through the backend then some triggers are fired where was if those same changes are made from the front end, ie using the PHP application , then these triggers are not fired ?

-Thanks in advance

---

1. yes, you can log changes made to the database by setting up a lots of triggers on tables which you need to track. you can use MySql's USER() function to get current user name.
2. using already mentioned USER() function you can determine, whether you need to log activity or not (I presume you are using different users in your php scripts and in your phpMyAdmin)

---

Your MySQL server must be configured to save logs. More details are here: MySQL Server Logs.

I believe this will help you.

---

set up another table

query_log

ql_id id user_id fk query small text date date time

insert into query_log set user_id = 'user_id', query = 'query', date = 'date'

---

You'll be much better off if you have just one auditing mechanism and it lives as close to the database as possible.

As such, use triggers for everything.

Have your application set a session variable, say @APP_USER = "kabir". Setup the triggers (insert/update/delete) to use @APP_USER if it exists and fall back to the MySQL's USER() function as Sergey suggested.

We created a small script that generates the triggers by looking at the schema of each table. It saves a lot of time, but you have to remember to regenerate the triggers when you modify the schema.

Good luck.