Allow access to a host with a dynamic IP in MySQL

So I was working with the same client with the issue I described here and was setting up their MySQL.

Here's the thing though: When MySQL tries to resolve hostnames, its performance drops dramatically. What I would ideally like to do is keep the skip-name-resolve parameter in the MySQL configuration file, while avoiding allowing connections from everyone else to my client's database.

One way would of course be through iptables. One might say that since iptables allows only a specific set of addresses, then it would be OK to actually have a user like 'root'@'%'.

Honestly, it would probably be a secure scenario in most cases, but you never know. Someone might accidentally flush the iptables rules, allow access to all on port 3306, you get the picture. It's a client. And they're devs. And they'll have root access afterwards. The horror. The horror.

So what do we do? Simple. Let's change our script a bit.

Here we assume that the user we want to allow access to is root logging in from www.example.com with password "root_password". We also have a rule that allows root from 1.2.3.4 we want to keep. What we want is to check every so often if the IP address of www.example.com has changed, and if so, drop that user and create another one that will be able to connect from the new IP address.  

[root@server ~]# mysql -e 'select host,user from user' mysql
+----------------+----------+
| host           | user     |
+----------------+----------+
| 127.0.0.1      | usera    |
| 127.0.0.1      | root     |
| 127.0.0.1      | userb    |
| 1.2.3.4        | root     |
| 5.6.7.8        | root     |
+----------------+----------+

[root@server ~]# vi /root/nslookup.pl
#!/usr/bin/perl

use strict;
use warnings;
use DBI();

my $result_raw=`dig www.example.com`;
my @result=split(/\n/, $result_raw);
my $IP='0.0.0.0';
foreach my $line (@result)
{
   if ($line =~ /.*www.example.com.*/)
   {
      $IP = $line;
   }
}
chomp $IP;
$IP=~ s/(.*\s+)(\d.*)/$2/;
if ($IP =~ /(\d{1,3}\.\d{1,3}\.\d{1,3}\.\d{1,3})/ and $IP ne '0.0.0.0')
{
   system("/sbin/iptables -D INPUT 3");
   system("/sbin/iptables -I INPUT 3 -s $IP -j ACCEPT");
}
else
{
   exit 2;
}

my $users=`mysql -e 'select host,user from user' mysql`;
my $OLD_IP='0.0.0.0';
@result=split(/\n/, $users);
foreach my $line (@result)
{
     if ($line =~ /.*(\d{1,3}\.\d{1,3}\.\d{1,3}\.\d{1,3}).*root.*/)
     {
        if (($line =~ /.*localhost.*/) || ($line =~ /.*127\.0\.0\.1.*/) || ($line =~ /.*1\.2\.3\.4.*/))
        {
            #print "skipping\n";
        }
        else
        {
            $OLD_IP = $line;
        }
     }
}
$OLD_IP =~ s/^((\d{1,3}\.\d{1,3}\.\d{1,3}\.\d{1,3}))(.*)/$1/;
chomp $OLD_IP;
if ($OLD_IP =~ /(\d{1,3}\.\d{1,3}\.\d{1,3}\.\d{1,3})/ and $OLD_IP ne '0.0.0.0')
{
   if ($OLD_IP ne $IP)
   {
      my $dbh = DBI->connect("DBI:mysql:database=mysql;host=127.0.0.1", "root", "root_password", {'RaiseError' => 1});
      my $query="DROP USER 'root'\@'$OLD_IP'";
      $dbh->do($query);
      $query="CREATE USER 'root'\@'$IP' IDENTIFIED BY 'root_password'";
      $dbh->do($query);
      $query="GRANT ALL PRIVILEGES ON *.* TO 'root'\@'$IP'";
      $dbh->do($query);
      $dbh->disconnect();
   }
}
[root@server ~]# crontab -e
*/5 * * * * /usr/bin/perl /root/nslookup.pl > /dev/null 2>&1

With this script and using crontab we a) check every 5 minutes the IP of www.example.com; b) we replace the old iptables rule with a new one if needed; c) we scan through the users that are allowed to access MySQL, ignore everyone who is not root and addresses such as "localhost" (this shouldn't really exist if skip-name-resolve is on, but it's ok to have it in the script just in case), "127.0.0.1", and "1.2.3.4" (the rule that allows root from 1.2.3.4 we wanted to keep) and in case the IP has changed replace that user with a user from the new IP.

So if all goes to plan with our regular expressions and our pattern matching, the user 'root'@'5.6.7.8' will be dropped and a new one will be created:

[root@server ~]# mysql -e 'select host,user from user' mysql
+----------------+----------+
| host           | user     |
+----------------+----------+
| 127.0.0.1      | usera    |
| 127.0.0.1      | root     |
| 127.0.0.1      | userb    |
| 1.2.3.4        | root     |
| 9.10.11.12     | root     |
+----------------+----------+

Note that you'll need the perl-DBI and perl-DBD-MySQL packages in order to run this script.

Fun, right? Right?... Meh, I guess not.