troubleshooting Question

MySQL audit storing application user ID

Avatar of tel2
tel2Flag for New Zealand asked on
DatabasesMySQL ServerSQL
4 Comments1 Solution908 ViewsLast Modified:
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
ASKER CERTIFIED SOLUTION
Join our community to see this answer!
Unlock 1 Answer and 4 Comments.
Start Free Trial
Learn from the best

Network and collaborate with thousands of CTOs, CISOs, and IT Pros rooting for you and your success.

Andrew Hancock - VMware vExpert
See if this solution works for you by signing up for a 7 day free trial.
Unlock 1 Answer and 4 Comments.
Try for 7 days

”The time we save is the biggest benefit of E-E to our team. What could take multiple guys 2 hours or more each to find is accessed in around 15 minutes on Experts Exchange.

-Mike Kapnisakis, Warner Bros