A server platform can never be 100% secure, save taking the nuclear option of taking it offline entirely and never plugging it in again. Of course this is an extreme measure and quite obviously not what we need for our infrastructure, so we take a multitude of steps to mitigate the risks of exposing servers and services to the world. Today I am introducing the first step in what I plan to develop into a full featured ISPConfig extension to allow us to curb the harm that an incursion into one or more of our public facing servers can do. A MySQL stored procedure is a procedure that runs directly from the MySQL server using a function or procedure call. This first step outlines a method of checking authenticated users and hosts against the mysql.user table in a MySQL host. You will need a way to run this code, directly on the MySQL server or in a GUI like MySQL Workbench. Code: USE mysql; DROP PROCEDURE IF EXISTS ShowUsers; DELIMITER $ CREATE PROCEDURE `ShowUsers`(IN KnownUsers varchar(500), IN KnownHosts varchar(500)) BEGIN SELECT user,host FROM user WHERE NOT FIND_IN_SET(host, KnownHosts) OR NOT FIND_IN_SET(user, KnownUsers) ORDER BY user, host ASC; END $ DELIMITER ; To execute the code we need to supply some users and hosts. Yours will differ to mine, so I will just use dummy values here as an example. Code: SET @Usernames = 'root,ispconfig,ispcsrv,ispcsrv2,ispcsrv3,ispcdrv6'; SET @Hostnames = '%,localhost,host1.example.com,host2.example.com, host2.example.com'; CALL ShowUsers(@Usernames, @Hostnames); What does this do. Well, we supply a list of users and a list of hosts, these are all the credentials that we know about and are happy to allow into our database, most likely we will run this only on the master database but could be any MySQL server that we want to allow authorised connections to. In short, the procedure checks all of the users against our supplied lists. If there is an unknown user or host found in our mysql.user table the record is returned. So when the procedure is called we want to see no results. If there are any results then either we have missed a legitimate user or host or an unauthorised user or host has been added to our database. In either case we will want to investigate further just to make sure we have no breach. It is possible of course that this step could be gotten around. Say a server is breached, they somehow work out that we may also have users ispcsrv[1-4] because they have breached a host that connects using ispcsrv5 as the user. Because we have root credentials for the master stored on slave servers, a breached server could add ispcsrv[1-4] to the masters mysql.user table with the hostname of ispcserv5 and they would not show in the results here. That said, this is a small first step towards keeping things a little more secure. Further integration steps to undertake: Update the procedure to obtain exact matches of authenticated user:host pairs. Create a script for the ISPConfig cron job that can block a hosts access to the master and alert system admin. ISPConfig extension to allow configurations/authorisations Add a flag/option into ISPConfig that puts the system into attach mode allowing new users to be added without triggering an alert or lock down. These will take time to build, but for now you have another small tool albeit slightly imperfect to hang on your belt.
No, you do not store the master server's root credentials on any slave server. If you find them there, you did something wrong. The only time those credentials are needed in a slave server is when installing or upgrading ISPConfig, when you will be interactively prompted for them, they will be used for that session, and not saved anywhere. If you wanted an additional layer of precaution/security, you can even disable the root@slave-server users in your master mysql server between upgrades, as that account is not used for any other purpose.
You might also audit your mysql users using an external baseline, eg a cron job from the master and/or an external machine. (As you are attempting to detect root level mysql compromise, you should not rely solely on the very same mysql instance, as the attacker can simply replace the current stored procedure with one which always returns true. ) Keep up the work, it's always good to be working towards more and better methods of preventing and detecting attacks! And if you've not done so yet, look at using fail2ban to catch failed mysql logins, and of course firewalls to block access from everywhere it's not needed (I don't run mysql open to the world, but I realize some do, by necessity or choice).
Re your first reply: The root password was obviously my misinterpretation of which root user and password was meant in the guide. I entered the masters user and password in the stated file on the slave, So in this instance its my own security flaw that meant system continued working after i did so as I used the same password for the slave and masters mysql root. Changing it won't break the system, I will just update the file that needs it so all is good there and its a lesson for me to use different passwords for each server. You make a good point about the slaves root users. To be honest I see what Im thinking right now as a feature request. Something for ISPC to work on as it would be in my mind a system option rather than something an extension should do. That is, system configuration mode with options to set what is disabled when the config mode is deactivated. Things like mysql root access could be turned off on all systems when the mode is off. Anything that could be misused and that is only needed during attach or upgrade could be disabled at the flick of a switch. Security would be vastly increased by doing so.
