0

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.

  • You are barking up the wrong tree. These forms of auditing are internal to MySQL and application based such as what you seem to be looking for. There is no switch you can turn on for this. You will have to write some code and learn about schemas. If you are not code or schema savvy, you will have to learn. Start with PHP then go onto to database schema. – closetnoc Dec 17 '14 at 0:20
  • Hi closetnoc. Thanks for your reply, but I think I may be misunderstanding you. Q1. Are you trying to say that "these forms of auditing" are "internal to MySQL" or are they "application based", or are they both? Q2. I know how to write code (both SQL and Perl), and that's how I got this far with my application, but if possible, I'd like to avoid adding auditing code to every point in my application where changes to tables are made. The 1st link I provided seems to indicate that this is doable - do you agree? If not, why not? Q3. What has PHP got to do with this Perl/MySQL application? – Terry Dec 17 '14 at 3:32
  • Re: Q1. I assume it is doable if you have the application user within your database. My apologies. I was assuming that you were using a "canned" application and that the application user was not available to the database. It has been a while since I have used triggers, (I will be again soon in a new schema), but you should be able to create triggers that populates an audit table. It has been about a dozen years since I have done this. ;-) If you know code, then, you should be able to get what you want. I would then keep it out of PHP if possible. It is more efficient. – closetnoc Dec 17 '14 at 3:58
  • Is there a way to add your PERL (BTW- I love PERL!) user to your schema? I am not sure what your user code methods are, if you store user names and passwords somewhere or do something else, but if you can move your user accounts into the schema in a meaningful way (meaningful to you if you look at it), then you should be able to audit using triggers (not gun or horse). – closetnoc Dec 17 '14 at 4:03
  • I think Michael's answer clears this up, but thanks for trying, closetnoc. – Terry Dec 18 '14 at 20:56
3

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

| improve this answer | |
  • Awesome answer, Michael! I might give that last option a try, or possibly go one of the other ways. Thanks for your time/expertise and I might get back to you if I have any follow-up questions. – Terry Dec 18 '14 at 9:12

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.