Detecting direct changes in MySQL through

Question

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 specific 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

Sergii Nester · Accepted Answer · 2011-07-01 12:54:56Z

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.
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)

answered Jul 1, 2011 at 12:54

Sergii Nester

4742 gold badges8 silver badges14 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Andron · Accepted Answer · 2011-07-01 10:10:42Z

0

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

I believe this will help you.

answered Jul 1, 2011 at 10:10

Andron

6,6614 gold badges45 silver badges56 bronze badges

Comments

Nick Maroulis · Accepted Answer · 2011-07-01 10:13:49Z

0

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'

answered Jul 1, 2011 at 10:13

Nick Maroulis

48710 silver badges29 bronze badges

Comments

Alain Collins · Accepted Answer · 2011-07-01 19:04:01Z

0

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.

answered Jul 1, 2011 at 19:04

Alain Collins

16.4k2 gold badges34 silver badges56 bronze badges

Collectives™ on Stack Overflow

Detecting direct changes in MySQL through

4 Answers 4

Comments

Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

Comments

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related