Audit logins on MySQL database

Question

Is there a way to audit logins to MySQL? I'd like to be able to create a username for each employee and thereby create an audit trail of logins. However, googling has turned up no good results.

The more we can audit, the better. At the very least, it would be nice to know who logged in when. It would be even better to see who executed what query when. The logs are there mostly to tell clients we have them since there is potentially sensitive information in the database.

Obviously, being able to audit the queries executed by each user (and when) would also give us the ability to better pinpoint who is the cause of an security issue if one should arise.

Answer 1

You would probably want to use the general query log.

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.

One important thing with logging for security is that an attacker cannot access the log to erase traces of their presence, so consider append-only files.

FWIW in Oracle we can send logs automatically to a remote syslog, but I don't believe MySQL has this feature yet. Perhaps you could fake it with SNMP but I have not tried it.

Answer 2

The answer by @Gauis is excellent. To add further to it, you can the following:

MySQL 5.1 now allows storing the general log and the slow query log as SQL tables.

Add this to /etc/my.cnf:

[mysqld]
log-output=TABLE
log

Restart mysql

Then, when mysqld creates the general log, instead of a text file it will create the table as a CSV table in the /var/lib/mysql/mysql folder (mysql schema database).

Just do this to see it:

SHOW CREATE TABLE mysql.general_log\G

All connections will pile up in it.

For you, that is not very useful when it comes to querying it. It would just be a full table scan each time.

What to do ??? CONVERT IT TO MyISAM and INDEX THE TABLE !!!!

SET @old_log_state = @@global.general_log;
SET GLOBAL general_log = 'OFF';
ALTER TABLE mysql.general_log ENGINE = MyISAM;
ALTER TABLE mysql.general_log ADD INDEX (event_time);
SET GLOBAL general_log = @old_log_state;

Optionally, you may want to put a fulltext index on the argument field.

I've just setup MySQL 5.5.9 on a server and tried this out. Here is the result:

Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 4
Server version: 5.5.9-log MySQL Community Server (GPL)

Copyright (c) 2000, 2010, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

iml-db10:3306 (DB (none)) :: 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.01 sec)

iml-db10:3306 (DB (none)) :: SET @old_log_state = @@global.general_log;
Query OK, 0 rows affected (0.00 sec)

iml-db10:3306 (DB (none)) :: SET GLOBAL general_log = 'OFF';
Query OK, 0 rows affected (0.00 sec)

iml-db10:3306 (DB (none)) :: ALTER TABLE mysql.general_log ENGINE = MyISAM;
Query OK, 9 rows affected (0.02 sec)
Records: 9  Duplicates: 0  Warnings: 0

iml-db10:3306 (DB (none)) :: ALTER TABLE mysql.general_log ADD INDEX (event_time);
Query OK, 9 rows affected (0.00 sec)
Records: 9  Duplicates: 0  Warnings: 0

iml-db10:3306 (DB (none)) :: SET GLOBAL slow_query_log = @old_log_state;
Query OK, 0 rows affected (0.00 sec)

iml-db10:3306 (DB (none)) :: select * from mysql.general_log;
+---------------------+-----------------------------+-----------+-----------+--------------+-------------------------------------------+
| event_time          | user_host                   | thread_id | server_id | command_type | argument                                  |
+---------------------+-----------------------------+-----------+-----------+--------------+-------------------------------------------+
| 2011-02-24 14:42:18 | [lwdba] @  [127.0.0.1]      |         3 | 106451130 | Connect      | [email protected] on                        |
| 2011-02-24 14:42:18 | lwdba[lwdba] @  [127.0.0.1] |         3 | 106451130 | Query        | select @@version_comment limit 1          |
| 2011-02-24 14:42:18 | lwdba[lwdba] @  [127.0.0.1] |         3 | 106451130 | Query        | SHOW VARIABLES LIKE 'hostname'            |
| 2011-02-24 14:42:18 | lwdba[lwdba] @  [127.0.0.1] |         3 | 106451130 | Quit         |                                           |
| 2011-02-24 14:42:18 | [lwdba] @  [127.0.0.1]      |         4 | 106451130 | Connect      | [email protected] on                        |
| 2011-02-24 14:42:18 | lwdba[lwdba] @  [127.0.0.1] |         4 | 106451130 | Query        | select @@version_comment limit 1          |
| 2011-02-24 14:42:30 | lwdba[lwdba] @  [127.0.0.1] |         4 | 106451130 | Query        | show create table mysql.general_log       |
| 2011-02-24 14:43:54 | lwdba[lwdba] @  [127.0.0.1] |         4 | 106451130 | Query        | SET @old_log_state = @@global.general_log |
| 2011-02-24 14:44:00 | lwdba[lwdba] @  [127.0.0.1] |         4 | 106451130 | Query        | SET GLOBAL general_log = 'OFF'            |
+---------------------+-----------------------------+-----------+-----------+--------------+-------------------------------------------+
9 rows in set (0.00 sec)

iml-db10:3306 (DB (none)) :: 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,
  KEY `event_time` (`event_time`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 COMMENT='General log'
1 row in set (0.00 sec)

Now, you can query by timestamp and look for specfic tokens in the argument field.

For example, notice line 4 of the SELECT I did. My logging in was recorded in the argument field as [email protected] on. You can track these.

What if the general gets too big (Believe me it will get too big very fast)

What to do ???

1. shutdown mysql
2. move the general_log.frm, general_log.MYD, and general_log.MYI to a different (and hopefull bigger) disk mount.
3. Create three symlinks to general_log.frm, general_log.MYD, and general_log.MYI from /var/lib/mysql/mysql
4. chown mysql:mysql general_log.frm general_log.MYD general_log.MYI on the new disk mount
5. chown mysql:mysql general_log.frm general_log.MYD general_log.MYI symlinks in /var/lib/mysql/mysql
6. start mysql back up

BTW Once you have the general log taken offline, you can then run these to collect the distinct logins that did something in mysqld:

SET SQL_LOG_BIN=0;
use mysql
DROP TABLE IF EXISTS audit_user_host;
CREATE TABLE audit_user_host
(
    user_host VARCHAR(32),
    PRIMARY KEY (user_host)
) ENGINE=MyISAM;
SHOW CREATE TABLE audit_user_host\G
INSERT IGNORE INTO mysql.audit_user_host SELECT user_host FROM mysql.general_log;
SELECT COUNT(1) FROM mysql.audit_user_host;

I have a client with 3 DB servers. Eeach with DB Server has over 1,000,000,000 (1 billion [thousands million]) lines in it. The script above took about 2.5 hours to complete. The audit_user_host table ended up with 27 distinct logins.

You should be good to go.

Have fun with this one, everybody !!!

Answer 3

Instead of doing so many things manually, just install Audit plugin which gives more insight at user level

http://www.mysql.com/products/enterprise/audit.html

It's available on selected commercial MySQL editions though, It would have been great if any MySQL fork adds in community edition as well so that most of the people get benefit of this feature, otherwise we have to rely on solution provided by @RolandoMySQLDBA.

Answer 4

@statichippo
How to install audit logging on MySQL.
+ Audit logging only support MySQL Enterprise
+ You can install audit logging on MySQL Community:
1. Copy file audit_log.so by You can install MySQL Enterprise Trial, then copy file audit_log.so to MySQL Community.
2. Copy audit_log.so to plugin_dir as /usr/lib64/mysql/plugin or you can show plugin dir by:
Go to mysql console: mysql> show global variables like '%plugin%';
3. Install audit logging as:
mysql> INSTALL PLUGIN audit_log SONAME 'audit_log.so';
mysql> SHOW VARIABLES LIKE 'audit_log%';
4. Output audit logging:
tail -f /var/lib/mysql/audit.log

Many Thanks.

Answer 5

A plugin is available that audits all mysql connections. This was included with recent versions of MySQL Enterprise edition, but is also available for Community versions of MySQL, MariaDB and Percona since version 5.5.34.

The plugin is provided by MariaDB and can be installed without restarting the MySQL server, and can be turned on and off at anytime.

To install:

1. Find your version of MySQL by running: mysqld --version

   mysqld Ver 5.5.47-0+deb7u1-log for debian-linux-gnu on x86_64 ((Debian))

2. The example is running version 5.5.47. Now download the correct version of MariaDB from here: https://mariadb.org/mariadb/all-releases/

3. Extract the archive and copy the file lib/plugin/server_audit.so to your MySQL server plugin directory. This is usually /usr/lib/mysql/plugin/ - If you can't find it run the MySQL command SHOW VARIABLES LIKE 'plugin_dir';

4. Set the permissions on the plugin file to 755 so the MySQL process can access it: chmod 777 /usr/lib/mysql/plugin/server_audit.so

5. Open a MySQL command line and run the following commands. This will install the plugin, set it to only log connections and disconnections, then turn the plugin on. The last command shows you the plugin configuration including the file name.

   INSTALL PLUGIN server_audit SONAME 'server_audit.so';

   SET GLOBAL server_audit_events='CONNECT';

   SET GLOBAL server_audit_logging=on;

   show global variables like "server_audit%";

You can now see MySQL connections and disconnections in the log file here:

/var/lib/mysql/server_audit.log

If you would like to see the full SQL query executed by each user (warning: this creates a huge amount of log entries) run this statement:

SET GLOBAL server_audit_events=''; 

If you want these settings to remain after the server is restarted, they must be set in the server configuration file (/etc/my.cnf.d/server.cnf in CentOS) in the [mysqld] section. To have the variable server_audit_logging set to ON, for example, add the line server_audit_logging=ON to the file.

References: https://mariadb.com/kb/en/mariadb-audit-plugin/ https://mariadb.com/resources/blog/introducing-the-mariadb-audit-plugin/