Preventing any external thesql login tries

MySQLSecurity

How to prevent any external logins to my Mysql database?

That is, how to make sure that the only way my database could be manipulated is to login to my machine with SSH and work as root/sudoer.

My machine is Ubuntu server operated and I've already deleted PHPmyadmin so people couldn't try to login from port 80 or 443 when I'm on HTTPS, but I think people can still try to login with software like Mysql workbench or similar software, and I wish to prevent that as well and to actually lock my database so only my operating system's root user/sudoer could access the DB via mysql -u root -p.

To clarify, I wish that other DB users couldn't login either from my system or outside, with their passwords — I'm the only one who uses this machine but even if there where other machine users besides root/sudoer, I would still want them not to be able to login to Mysql, and I don't want anyone to login from outside the machine as well – In other words, I don't want any other human besides me, to manipulate the database.

I assume I should lock some extra ports besides 3306. Is that correct?

I could remove any such port from these lists in /etc/csf/csf.conf:

# Allow incoming TCP ports
TCP_IN = "20,21,22,25,53,80,110,143,443,465,587,993,995,9000"

# Allow outgoing TCP ports
TCP_OUT = "20,21,22,25,53,80,110,113,443,587,993,995,9000"

Note: I've unfiltered port 9000 for PHP-FPM.

Best Answer

The correct way to lock down external access to MySQL is with the bind-address directive in /etc/mysql.cnf, as described by daisy. You can't usefully stop someone with a valid MySQL account from logging in locally (but note that a UNIX account does not imply a MySQL account; they are distinct from each other).

I'm going to answer the other part of your question:

I assume I should lock some extra ports besides 3306. Is that correct?

# Allow incoming TCP ports
TCP_IN = "20,21,22,25,53,80,110,143,443,465,587,993,995,9000"

At the moment your csf.conf file sets your firewall to allow in so many services it's hardly worth using.

  • 20 - FTP data (why are you allowing FTP over the Internet at all?)
  • 21 - FTP command (ditto)
  • 22 - SSH (this is good but ensure your accounts are locked down tight; consider disabling all non-certificate logins)
  • 25 - SMTP inbound (are you really running an incoming mail server?)
  • 53 - DNS (are you really running public DNS?)
  • 80 - HTTP (are you running a web server?)
  • 110 - POP3 (obsolete protocol; if you are running a mail server consider IMAPS on 993 instead)
  • 143 - IMAP (insecure; use IMAPS on 993 instead)
  • 443 - HTTPS (are you running a web server?)
  • 465 - SMTPS (are you really running a public mail server?)
  • 587 - SMTP-MSA (ditto)
  • 993 - IMAPS (only if you are receiving mail messages on this server for reading)
  • 995 POP3S (secure but still obsolete)
  • 9000 - who knows

If you don't offer a service then don't allow it in. POP3, IMAP and POP3S should all be switched off in favour of IMAPS (if at all). Don't offer SMTP* unless you really are running a mail server that needs to receive emails from the Internet. Don't run FTP. Use SSH for file transfer instead.

If you want to use MySQL Workbench on a different system you can still have MySQL set to permit only local connections by running a forwarder across ssh. I do this for a couple of my servers:

ssh -nf -L localhost:3306:localhost:3306 ADDRESS_OF_REMOTE_SYSTEM sleep 60
# Using Workbench, now connect to MySQL seemingly on the local machine
Related Question