We help IT Professionals succeed at work.
Get Started

MySQL audit storing application user ID

tel2
tel2 asked
on
906 Views
Last Modified: 2021-04-21
Hi Experts,

I am trying to set up auditing for changes to data in MySQL tables, for my Perl web application.  I've had a look at this:
    www.go4expert.com/articles/database-transaction-auditing-mysql-t7252
and that looks good because it uses triggers to automatically create the audit records, but it looks as if it will record the MySQL user (e.g. USER() or CURRENT_USER()) in the audit table record, and since my application uses the same MySQL user for all web users, this won't identify the actual person who is making the change.  In my application, the ID of the person making the change is stored in a Perl variable (say $userid, which is an integer).

So, without me having to do extra coding wherever I make changes to tables (presumably by using triggers), how can I get MySQL to audit all changes to my tables, recording the $userid (Perl variable) as the user who made the change?

I've also had a quick look at these, but haven't recognised a solution to this $userid problem yet:
    http://dev.mysql.com/doc/refman/5.0/en/create-trigger.html
    http://dev.mysql.com/doc/refman/5.0/en/account-activity-auditing.html
    http://www.go4expert.com/forums/showthread.php?t=7252
    http://ronaldbradford.com/blog/auditing-your-mysql-data-2008-07-15

I'm using:
- mysql: Ver 14.14 Distrib 5.5.40, for Linux (x86_64) using readline 5.1
- Perl: 5.10.1

Thanks.
tel2
Comment
Watch Question
CERTIFIED EXPERT
Top Expert 2011
Commented:
This problem has been solved!
Unlock 1 Answer and 4 Comments.
See Answer
Why Experts Exchange?

Experts Exchange always has the answer, or at the least points me in the correct direction! It is like having another employee that is extremely experienced.

Jim Murphy
Programmer at Smart IT Solutions

When asked, what has been your best career decision?

Deciding to stick with EE.

Mohamed Asif
Technical Department Head

Being involved with EE helped me to grow personally and professionally.

Carl Webster
CTP, Sr Infrastructure Consultant
Ask ANY Question

Connect with Certified Experts to gain insight and support on specific technology challenges including:

  • Troubleshooting
  • Research
  • Professional Opinions
Did You Know?

We've partnered with two important charities to provide clean water and computer science education to those who need it most. READ MORE