LinuxQuestions.org
Review your favorite Linux distribution.
Go Back   LinuxQuestions.org > Forums > Linux Forums > Linux - Server
User Name
Password
Linux - Server This forum is for the discussion of Linux Software used in a server related context.

Notices


Reply
  Search this Thread
Old 06-17-2008, 03:35 PM   #1
Linville79
Member
 
Registered: Nov 2006
Location: Indiana, U.S.A.
Distribution: Red Hat Enterprise Linux, Fedora, CentOS
Posts: 134

Rep: Reputation: 16
Logging all mysql command line operations


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
 
Old 06-17-2008, 11:30 PM   #2
d3ckard
LQ Newbie
 
Registered: May 2007
Posts: 13

Rep: Reputation: 0
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.
 
Old 06-18-2008, 10:10 AM   #3
trickykid
LQ Guru
 
Registered: Jan 2001
Posts: 24,149

Rep: Reputation: 256Reputation: 256Reputation: 256
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.
 
Old 06-19-2008, 06:43 AM   #4
d3ckard
LQ Newbie
 
Registered: May 2007
Posts: 13

Rep: Reputation: 0
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.
 
Old 06-19-2008, 08:06 AM   #5
Linville79
Member
 
Registered: Nov 2006
Location: Indiana, U.S.A.
Distribution: Red Hat Enterprise Linux, Fedora, CentOS
Posts: 134

Original Poster
Rep: Reputation: 16
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.
 
Old 06-19-2008, 08:09 AM   #6
Linville79
Member
 
Registered: Nov 2006
Location: Indiana, U.S.A.
Distribution: Red Hat Enterprise Linux, Fedora, CentOS
Posts: 134

Original Poster
Rep: Reputation: 16
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.
 
Old 06-19-2008, 08:33 AM   #7
unSpawn
Moderator
 
Registered: May 2001
Posts: 29,415
Blog Entries: 55

Rep: Reputation: 3590Reputation: 3590Reputation: 3590Reputation: 3590Reputation: 3590Reputation: 3590Reputation: 3590Reputation: 3590Reputation: 3590Reputation: 3590Reputation: 3590
Quote:
Originally Posted by Linville79 View Post
but as with a normal shell history, it does not include any time and date stamping.
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).
 
Old 06-19-2008, 01:48 PM   #8
trickykid
LQ Guru
 
Registered: Jan 2001
Posts: 24,149

Rep: Reputation: 256Reputation: 256Reputation: 256
Quote:
Originally Posted by d3ckard View Post
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.
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.
 
Old 06-19-2008, 01:51 PM   #9
trickykid
LQ Guru
 
Registered: Jan 2001
Posts: 24,149

Rep: Reputation: 256Reputation: 256Reputation: 256
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.

Last edited by trickykid; 06-19-2008 at 01:53 PM.
 
  


Reply


Thread Tools Search this Thread
Search this Thread:

Advanced Search

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is Off
HTML code is Off



Similar Threads
Thread Thread Starter Forum Replies Last Post
mysql works from command line, but mysql.sock (13) error from php.. leif_84 Linux - Server 0 07-23-2007 12:59 PM
httpd -l and mysql -p at any command line prompt ZC1 Linux - General 1 05-23-2006 09:58 AM
logging in from command line and doing su cause weird messages sunpascal Linux - Newbie 2 02-10-2006 09:05 AM
MySQL 4.0.12 - command-line tetractis Programming 0 07-04-2003 06:00 AM
MySQL command line buddy Linux - General 3 01-16-2003 08:14 AM

LinuxQuestions.org > Forums > Linux Forums > Linux - Server

All times are GMT -5. The time now is 10:36 AM.

Main Menu
Advertisement
My LQ
Write for LQ
LinuxQuestions.org is looking for people interested in writing Editorials, Articles, Reviews, and more. If you'd like to contribute content, let us know.
Main Menu
Syndicate
RSS1  Latest Threads
RSS1  LQ News
Twitter: @linuxquestions
Facebook: linuxquestions Google+: linuxquestions
Open Source Consulting | Domain Registration