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.
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:
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.
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
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.
