MySQL audit storing application user ID I am trying to set up auditing for changes to data in MySQL tables, for my Perl web application on Linux.  I've had a look at this:
http://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 several articles, including this one:
http://dev.mysql.com/doc/refman/5.0/en/account-activity-auditing.html
but haven't recognised a solution to my $userid problem yet.

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

Thanks.
The point of database-level audits, such as can be done with triggers is to capture "who" made the change... but from the database's perspective, there is only one "who" -- the MySQL user the application uses.  (Also, your devops and DBA users, but primarily, it sounds like the application's changes you're interested in.)

I would suggest that the appropriate approach is for the application to log the app-user actions through deliberate insertions into "history" or "event" tables, referencing that Perl $userid, which presumably would also be the primary key in "user" table in the database... you'd be generating these logs from application code. 

The role of trigger-based audits, then, is primarily one of sanity-checking the application: the audit shows this user's balance increased... did the application write a matching log entry in the appropriate "history" table?  The history table should then have the app-user id, and everything cross-references, if the app is correctly logging its actions.

(My personal preference is for the application to modify the database only through calls to stored procedures... in this way, the application can't fail to write these app-user-level history logs, because the stored procedures encapsulate every DML action into a single atomic database call... but I digress.)

There's a workhackaround available in MySQL, if you want to try to hybridize these two approaches... even if it's only to satisfy your curiosity and broaden your understanding of the nature of what you're dealing with.

User Defined Variables in MySQL are untyped variables, much like Perl scalars: they are whatever you put in them, strings, datetimes, floats, or blobs, and they're scoped to the individual MySQL connection.  Their value is independent of database transactions, but never visible from one connection to another.

$dbh-&gt;do(q[ SET @app_user_id = ? ], undef, $userid);


Until you disconnect from this connection, the variable @app_user_id (which I just made up, it's just a variable name, but the @ designates it as a user defined variable) will contain the value you stored there unless you set it to something else.  Because these variables have session scope, they also available to triggers, procedures, and stored functions (but not views or events)... so instead of using CURRENT_USER() in a trigger, use @app_user_id.

This, of course, requires that your application initialize this variable each time it begins running code that could update the database, and you have to hold on to that DBI handle, if you're working in a framework that can swap them out from under you.

So, not a perfect solution, but possibly the closest thing you'll find to accomplish what you want, using the setup you want to try.

http://dev.mysql.com/doc/refman/5.6/en/user-variables.html