Graham Graham - 1 month ago 16
MySQL Question

Log MySQL DB Changes by User Known only in PHP

I have completed a system for a client using php and mysql. Users log into the system and then provide information to be stored in a database. The php code connects to the database with a "common" username ie. each individual user does not have privileges on the database.

Now, just before going live, the client wants a log of changes made to the database. I have investigated using triggers but the user() variable within mysql stores the "common" user, not the signed in user. To modify the system to manually log each sql transaction is not practical in the short time available before launch.

Is there any way for mysql to extract the logged-in user information from the session cookie (or any other php variable)?

Answer

No, mysql knows nothing about what does PHP do with your application's users. You should deliberately put all information you want to the database. As a quite dirty workaround you can log all mysql queries by setting

SET GLOBAL general_log = 'ON';