3

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.

Improve this question
6
  • Please note that the mysql "root" user has nothing to do with the "root" user on the hosting operating system (or sudo for that matter). Even if you ensure that no connection can be opened over the network (answer from @daisy seems reasonable) any user logged into the host operating system can connect to the database if they know a valid database user name and password (you have passwords for your mysql database users, right?). Be it with a program like workbench or the console mysql client. Commented Nov 18, 2017 at 9:52
  • Not sure I understand: To my system itself only one user can login (may it be the root, or if the root is disabled, only one user which is a sudoer). Are you saying that even in that case, a DB user and it's password could login from mysql workbench with its DBusername and password?... Hmm I wouldn't want that to happen. I don't want anyone to manipulate any database besides my operating system's main user (root or sudoer). Commented Nov 18, 2017 at 9:58
  • I slightly edited the question to best explain my aim. I didn't change the essence of the question. Commented Nov 18, 2017 at 10:01
  • It depends where the workbench is run. If it is run from the same computer as the database you (or anybody who has the db password) can still log into the db. If it is run on another computer it would have to log into the db via the network but if the db only accepts connections from localhost that won't be a problem. Commented Nov 18, 2017 at 10:12
  • 1
    I hope you have root login disabled in ssh as well, just as an aside, that's a frequent default setting that is left in place. That's why you constantly see scans looking for that root user name, as well as stuff like phpmyadmin, easy targets. But just asking the right questions already puts you ahead in this game. Commented Nov 18, 2017 at 20:58

3 Answers 3

Reset to default
7

Just let mysqld listen on loopback only

e.g in /etc/mysql/my.cnf add or change the following configuration

bind-address 127.0.0.1

In that way only programs that runs locally can connect to mysql database

Improve this answer
5
  • Interesting way, thanks, but given the fact mysql/php developers like to change the file system architecture (in some versions php.ini could be in different directories for example) I would assume doing it from CSF-LFD in csf.conf could be more stable by means of version changes. Would you agree with this notion? Commented Nov 16, 2017 at 9:17
  • @roaima I didn't understand you at all, sadly. Commented Nov 18, 2017 at 19:57
  • Please let me make sure I understand - If port 3306 is filtered than I don't have to do what's described in the answer here because either way it will make my mysql server accessed only locally. Commented Nov 19, 2017 at 7:36
  • @Arcticooling you can leave your valuables in display if you are sure your house is always locked. Personally, I'd prefer to lock them in a safe place so I didn't have to worry so much about someone breaking in through the window. (Poor analogy, but the best I can come up with on a Sunday morning.) Commented Nov 19, 2017 at 9:22
  • roaima, your answer helped me very much and I couldn't give you the bounty sooner today. Sure I agree that the valuables shouldn't be viewed by anyone in front of the window, I'm sorry for any other impression. I just ask if filtering all ports as you suggested (unfiltering only 80, 443, 9000), redundants the solution offered in this particular answer... I really still miss that sadly. Commented Nov 19, 2017 at 14:05
3
+100

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
Improve this answer
3
  • Besides 9000, it's all default but I understand. I think I should only allow SSH, 80, and 443, because all my websites on the VPS or simple Wordpress sites, each with single contact form going to my personal Gmail account, and if I charge someone it's only with paypal. Commented Nov 18, 2017 at 20:54
  • @Arcticooling if this is all default then IMO it's a very poor choice of values for a default setting. Commented Nov 18, 2017 at 20:57
  • This should be the top upvoted and accepted answer, it's comprehensive and explains the issue well. I would also add to run a real nmap port scan against the vps as well, to make sure no other surprises are there from defaults and poor configurations. Commented Nov 18, 2017 at 20:59
1

roaima certainly has the authoritative answer here so far, so I'd just like to add a very simple set of things that you may not be aware of running MySQL on the command line via ssh:

  1. disable root login, and, if practical, disable password login, and require keys, but that's not as critical and may not always be convenient, but leaving root enabled for ssh once you have the system set up should be avoided as far as is practical, and if you don't have a real reason to need it, do not do it. Hint: use complicated random user names, that is just one more barrier to guessing.

  2. This is something that I think most people are not aware of: when you type something on the ssh shell, that is stored in the shell history, and can be retrieved by just running through the history, so if you use mysql passwords on the command line as part of issuing commands or connecting to mysql, those will be in the history. There is an absurdly easy way to avoid this issue: place a space before all your mysql commands you type in on the command line. That's it. The space makes the shell ignore the command in terms of adding it to its history.

  3. Then run: history -c to clear your shell history. This is valid for at least Bash, I do not know for all the others.

These are very simple things to do that do not cost you hardly any effort, and which get rid of two common weak points, the shell history, and a trivially easy to guess username for ssh.

Improve this answer

You must log in to answer this question.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.