Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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.

share|improve this question
1  
What exactly are you looking to audit? I presume you mean you'll use the MySQL usernames, not system usernames? How do you intend to use the audit data later (meaning what details are important here, would system logging be sufficient instead of MySQL logging). The more information you can provide in your question the more exactly we can give you an answer, and quickly to boot. I imagine you want a better answer than "have your app make a specific sproc call before each other operation" ~ In short, what details would you need from me if I were asking this? –  jcolebrand Jan 17 '11 at 21:30

3 Answers 3

up vote 3 down vote accepted

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.

share|improve this answer
3  
Logging via syslog was added in 5.1.20 –  Joe Jan 17 '11 at 22:10
    
Oh cool, learn something new every day :-) –  Gaius Jan 17 '11 at 22:16

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      | lwdba@127.0.0.1 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      | lwdba@127.0.0.1 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 lwdba@127.0.0.1 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 !!!

share|improve this answer
    
Great article! Just sharing my testing. I tried renaming the table mysql.general_log and partition the table for purging purpose but won't log in the table. So I switch it back to non-partitioned MyIsam table. Thanks! –  user14937 Nov 1 '12 at 10:01

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.

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.