Thesql logging activity from specific user or ip

logsMySQL

I have mysql server.

The server is accessed by my application, and by external auditor (person using mysql workbench).
The auditor has specific user and password and dedicated IP and it is granted only for select privileges.

I need to log the activities from the auditor. Is it possible to do it somehow ? If not can I log the packages that are coming from the network ?

Best Answer

You could use the general log. In fact, you should try using the MySQL table version of the general log.

If you run this:

SHOW CREATE TABLE mysql.general_log\G

You should see something like this:

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` int(11) 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.14 sec)

mysql>

The user_host column will record the MySQL user and the IP Address/DNS Name the command came from.

You are probably saying right now, "That table is a CSV file. I would have to parse it." That's true, you would have to. However, did you know that you can convert it to a MyISAM table? I have actually tried this out for one of my employer's DB Hosting Clients, and I wrote a post explaining it (See the Feb 24, 2011 post down below).

Here are the basic steps:

Make the mysql.general_log table MyISAM

Run the following:

CREATE TABLE mysql.general_log_original LIKE mysql.general_log;
ALTER TABLE mysql.general_log ENGINE=MyISAM;
ALTER TABLE mysql.general_log ADD INDEX (event_time);

Enable the general log

Add the following to /etc/my.cnf

[mysqld]
log-output=TABLE
general-log

If you also want the text version of the general log, add this:

[mysqld]
log-output=TABLE,FILE
general-log
general-log-file=/var/log/mysql_general.log

Restart mysql

Simply run service mysql restart

I have many posts about using the general log and the slow log as MyISAM tables:

Jan 22, 2013 : How often does the slow_log table get updated in MySQL?
Nov 10, 2012 : Redirecting MySQL errors and slow logs into another database
Jan 08, 2012 : How to enable MySQL general log?
Dec 27, 2011 : How to do MySQL User Accounting
Nov 18, 2011 : Create a custom log table in MySQL
Jun 30, 2011 : How do I output MySQL logs to syslog?
Feb 24, 2011 : Audit logins on MySQL database

CAVEAT

MySQL 5.5.28 has recently introduced the audit log plugin and how to install it.

Here are the options

These are new to me so I have nothing pros or cons at this time on the Audit Log Plugin.

Related Solutions

Mysql – Grant table privileges to a user connecting from any host

When you execute GRANT SELECT ON store.catalog TO 'wordpress'@'%';, mysqld wants to insert a row into the grant table mysql.tables_priv. Here is mysql.tables_priv:

mysql> show create table mysql.tables_priv\G
*************************** 1. row ***************************
       Table: tables_priv
Create Table: CREATE TABLE `tables_priv` (
  `Host` char(60) COLLATE utf8_bin NOT NULL DEFAULT '',
  `Db` char(64) COLLATE utf8_bin NOT NULL DEFAULT '',
  `User` char(16) COLLATE utf8_bin NOT NULL DEFAULT '',
  `Table_name` char(64) COLLATE utf8_bin NOT NULL DEFAULT '',
  `Grantor` char(77) COLLATE utf8_bin NOT NULL DEFAULT '',
  `Timestamp` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  `Table_priv` set('Select','Insert','Update','Delete','Create','Drop','Grant','References','Index','Alter','Create View','Show view','Trigger') CHARACTER SET utf8 NOT NULL DEFAULT '',
  `Column_priv` set('Select','Insert','Update','References') CHARACTER SET utf8 NOT NULL DEFAULT '',
  PRIMARY KEY (`Host`,`Db`,`User`,`Table_name`),
  KEY `Grantor` (`Grantor`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_bin COMMENT='Table privileges'
1 row in set (0.00 sec)

mysql>

Since you want to insert a row into mysql.table_priv where user='wordpress' and host='%', there has to exist a row in mysql.user where user='wordpress' and host='%'.

You also mentioned that you are using MySQL Workbench. You must be using 'root'@'localhost'. That would usually have all rights and a password.

If you want to just allow anonymous SELECT against that table, first run this:

GRANT USAGE ON *.* TO 'wordpress'@'%';

This will place wordpress@'%' into mysql.user. Afterwards, GRANT SELECT ON store.catalog TO 'wordpress'@'%' should run just fine.

You will have to see what other wordpress entries are in mysql.user. This should show what SQL GRANT commands you need:

SELECT CONCAT('GRANT SELECT ON store.catalog TO ',userhost,';') GrantCommand
FROM
(
    SELECT CONCAT('''',user,'''@''',host,'''') userhost
    FROM mysql.user WHERE user='wordpress'
) A;

Mysql slow.log user specific

You can try to analyse your slow log with the Percona tool pt-query-digest and use the option :

–filter ‘($event->{user} || “”) =~ m/john/’

Max.

Best Answer

CAVEAT

Related Solutions

Mysql – Grant table privileges to a user connecting from any host

Mysql slow.log user specific

Related Question