I am a SysAdmin for an online banking system that runs on Red Hat Enterprise Linux with a mysql backend. For audit purposes, we need to be able to log all operations (select, input, update, delete, etc.) that are performed in the mysql command line tool on a per user basis.

We want an audit trail for everything that happens in the database, so that when something goes wrong, we can easily tell who did it and what they did.

It needs to be logged in a way that is readable to non-technical internal management and external federal auditors.

I've looked around on the web, and I've not yet come across any plugin modules or native logging utilities for mysql to do this.

I'm hoping to get some responses here that might at least point me in the right direction.

Thanks in advance.

Kris

See the ~/.mysql_history for each user. Most standard mysql client installs create this...

Then maybe manage these at each login time, driven by a custom root-level process that saves the file to a log or repository location, creating a blank one at each new login time.

You might also exploit the --debug option to the mysql client startup to have it write log files somewhere that you can process later..

To organize the data and make it readable by non-tech people you'll have to process the data.

Or simply turn on bin logging for MySQL. If you have a lot of queries and transactions going, these can grow rather fast but should pretty much a identical record of what was done on the database, data included. These logs are typically used for recovery as well.

Unfortunately neither bin logs or query logs report command by command what user did what action, as the OP is requesting. If several users have mysql client sessions, all their queries will appear together in binlogs or querylogs -- and you wouldn't be able to tell who executed which query.

Since ~/.mysql_history operates on a per-user basis.

I don't want to use bin logging. For the amount of activity that our database sees, those logs would be too large to be useful for our internal auditors and management.

I'll have to see if our install is using mysql_history.

Thanks for the suggestions.

Well, looks like ours is using the mysql_history, but as with a normal shell history, it does not include any time and date stamping. The date would be easy enough if we setup a logrotate job to archive the log daily, but the time that the command was done is still important to what we want to use it for.

Soz for OT, but for shell logging you got three options: Bash shell logging patch (Bashv2, see honeynet), export HISTTIMEFORMAT (Bashv3 only) or using a shell wrapper like "Rootsh" (compatible, output to file or syslog). IIGC MySQL Forge shows there's an intention to do an audit plugin but apparently nobody picked it up (unassigned).

True but any user can simply just go and delete such file as well when they're done. Also like the OP mentioned, it provides no time stamp associated with each command/query.

Also, this would be hard to manage if the users have access to connect to the database remotely. At least with bin logging, you get the time stamps and you can at least narrow down who was logged in at any given time.

But I'd say, take the rootsh or bash shell logging approach like unspawn suggested. It still doesn't help when the users have access to delete such mysql_history in their home directories and or if they're given remote access which complicates getting such info from.

And if you're using 5.0 or above, you can probably get the info you need from the General Query Log built into MySQL:

http://dev.mysql.com/doc/refman/5.0/en/query-log.html

The general query log is a general record of what mysqld is doing. The server writes information to this log when clients connect or disconnect, and it logs each SQL statement received from clients. The general query log can be very useful when you suspect an error in a client and want to know exactly what the client sent to mysqld.

Edit: The General Query Log is also in older versions as well.