Routine Audit

Once everything's up and running, you shouldn't make the mistake of leaving MySQL to run without administration. If your lockdown has been sufficient you will be well protected against attackers , but it's helpful to know when someone is attempting to attack you, even if they're unsuccessful . Who knows , they might return armed with some 0-day overflow exploit and be successful the next time they try. Vigilance is key.

1. Check your logs.

   If you've configured the query log with the --log option, you should check it regularly to see what's been going on. Specifically, search for common SQL injection attacks and use of the load_file, infile, and outfile filesystem syntax.

   It's important to check the error logs regularly as well, though they tend not to be as informative as the query log.

   Remember when interacting with logs that log data can be highly sensitive; if you're importing it into some other repository (such as a database) for analysis, remember that the query log may contain usernames and passwords.

2. Enumerate users and use the "show grants" statement regularly to see what privileges are granted to which users. For example:

    mysql> select user, host from mysql.user; +-------+-----------+  user   host       +-------+-----------+  monty  %           root   localhost  +-------+-----------+ 2 rows in set (0.00 sec) mysql> show grants for 'monty'@'%'; +-----------------------------------------------------------------------------+  Grants for monty@%                                                           +-----------------------------------------------------------------------------+  GRANT USAGE ON *.* TO 'monty'@'%' IDENTIFIED BY PASSWORD '5d2e19393cc5ef67'   GRANT SELECT ON `test`.* TO 'monty'@'%'                                      +-----------------------------------------------------------------------------+ 2 rows in set (0.00 sec) mysql> show grants for 'root'@'localhost'; +---------------------------------------------------------------------------------------------------------------+  Grants for root@localhost +---------------------------------------------------------------------------------------------------------------+  GRANT ALL PRIVILEGES ON *.* TO 'root'@'localhost' IDENTIFIED BY PASSWORD '5d2e19393cc5ef67' WITH GRANT OPTION  +---------------------------------------------------------------------------------------------------------------+ 1 row in set (0.00 sec) 

   So you can see that there are two users in the database, root@localhost and monty, who can log on from any host but have select privileges only in the test database. Incidentally, you can also see (from the password field of the user table) that monty and root have the same password!

3. It's sensible to periodically do a quick check on password hashes. Hashes in MySQL are unsalted, which means that the same password always hashes to the same value. If you use

    mysql> select user, password from mysql.user; +-------+------------------+  user   password          +-------+------------------+  root   5d2e19393cc5ef67   monty  5d2e19393cc5ef67  +-------+------------------+ 2 rows in set (0.00 sec) 

   you can see which accounts have the same password. In this case, monty and root have the same password (which incidentally is "password"); this is probably not desirable.