MySQL – Internal Database in Replication

MySQLreplicationSecurity

I searched around but the word game seems to delude any valid result and could not find anything relevant.

Mysql (the RDBMS) has an internal database named mysql, this database has information about the users and as far as I know, information and settings about the server running the RDBMS.

Background Info

In our set up we have a few MySQL 5.5 servers and one test MySQL 5.6 server.

Replication is set up so that the 5.6 is a slave of one of the 5.5

Incident

This week the replication to the 5.6 stopped working, when doing a show slave status this error was reported:

Error: Error Column count doesnt match value count at row 1 on query. 
Default database: mysql. 
Query: INSERT INTO tmp_user VALUES (localhost,root,,Y,Y,Y,Y,Y,Y,Y,Y,Y,Y,Y,Y,Y,Y,Y,Y,Y,Y,Y,Y,Y,Y,Y,Y,Y,Y,Y,Y,Y,,,,,0,0,0,0,,)

When I checked, the mysql database did not have a tmp_user table, however the error is that the column count doesnt match, what I expected was for the error to be that the table did not exist (Error 1146).

Another conclusion that I gathered from this situation is that we are replicating the database mysql.

Questions

  1. Is there any concern/benefit in replicating the mysql database?
  2. Do you see this scenario as an injection attack? or a security concern?
    (Perhaps I am missing a scenario where creating/populating/deleting a tmp_user table is
    normal)

Thanks for your time

Best Answer

Yeah, what the heck is tmp_user? This smells like an attack. And I suspect it was successful. He probably already got in -- all he had to do was to try different flavors of the INSERT based on different versions. The one successful insert would suffice. Then he RENAMEs tables, does damage, rename tables back, DROPs evidence of his naughtiness, and vanishes. That insert looks like he could log in with all permissions and no password. Once he has SUPER he can do nasties like read all files (including /etc/passwd) on your system. Who knows what other goodies he found on your machine. Nice trick.

How did he get this far?

  • Port 3306 is not firewalled from people outside your building.
  • some non-root mysql 'user' has too much access to the mysql database. Possibly you simply did GRANT ALL ON *.* to ... for each application 'user'? Instead, use ON dbname.*; that would at least prevent messing around in mysql.
  • Perhaps SQL injection allowed CREATE TABLE mysql.tmp_user ... to be performed from your externally facing UI. Escape all user-provided strings. Don't allow multiple statements to be executed in a single command.

Replicating the mysql database has benefits and drawbacks, especially between different versions.

  • (plus) GRANTs, etc, are propagated to the slaves
  • (minus) the structure of mysql tables sometimes changes between major (eg, 5.5 to 5.6) versions, thereby disallowing direct manipulation of mysql tables.
Related Question