Is it possible to get the information of which user changed what in a database?

Question

I'm trying to create a log of changes in a given table in a database. To do this, I'm going to get the info about all the fields in the original table and create a new one using a function.

In addition to the fields in the original table, the new table should contain the time of the change, the type of the change (insert, delete, update) and an identification of the user that made the change.

I've figured out where I can get the information about the columns of the original table in MySQL (table INFORMATION_SCHEMA.Columns), but I don't know where I can find the information related to the user.

Can anyone please point me in the right direction?

Answer 1

If you want to avoid playing with plugins, I have just thing for you.

You should enable the general log as a MySQL table because it records the following:

mysql> show create table mysql.general_log\G
*************************** 1. row ***************************
       Table: general_log
Create Table: CREATE TABLE `general_log` (
  `event_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  `user_host` mediumtext NOT NULL,
  `thread_id` bigint(21) unsigned NOT NULL,
  `server_id` int(10) unsigned NOT NULL,
  `command_type` varchar(64) NOT NULL,
  `argument` mediumtext NOT NULL
) ENGINE=CSV DEFAULT CHARSET=utf8 COMMENT='General log'
1 row in set (0.07 sec)

mysql>

Column #2 user_host has the user and host of the command issued.

The rest of the generale log table gives you

• what command was executed
• when it was executed
• process ID of the processlist at the time the command was executed

You may find the CSV Storage Engine annoying for a live log. You can convert it to MyISAM. Then, you can index the event_time. As an option, you can create a prefix index the user_host as well.

Rather than reinvent the wheel, I will not put the implementation details in this post because I have already written posts on how to go about doing this:

• Jun 30, 2011 : How do I output MySQL logs to syslog?
• Jan 08, 2012 : How to enable MySQL general log?
• Feb 11, 2012 : MySQL general log
• Apr 04, 2013 : Stress test MySQL with queries captured with general log in MySQL
• Jun 23, 2013 : Analyse MySQL General Query Log in Real-time?
• Apr 28, 2014 : MySQL (on Windows) general log too large to rotate properly?

Give it a Try !!!

Answer 2

The right direction for this is to use an audit logging plugin. There are some choices out there. Please check out the following post for more information

http://www.mysqlperformanceblog.com/2014/05/20/database-auditing-alternatives-mysql/

My personal preference is the Percona Audit Plugin but you should be using Percona Server (MySQL Alternative) for this.