2

In order to connect to a MySQL database, you typically use the following command:

mysql -h host_address database_name -u db_user -p

where db_user is associated with the database in question. However, if you have a shared webhosting plan with a commerical webhost such as Justhost, Hostgator, and iPage, the webhost blocks you from accessing your databases in this way unless you're accessing them from your hosting server (in which case host_address would be localhost). To get around this, you have to explicitly whitelist the IP addresses you want to be able to access your databases remotely.

My question is, why? Is having access to the database restricted to specified user accounts (such as db_user in the above example) not secure enough?

migrated from superuser.com Oct 4 '14 at 23:56

This question came from our site for computer enthusiasts and power users.

  • 1
    Security reasons; host your own website on your own hardware and you can break standard security practices if you want – Ramhound Oct 4 '14 at 21:33
  • But I still don't understand why it's necessary. Why is a username and password not sufficient? Including the IP address as part of the authentication process means I can't work from anywhere in the world; I'm forced to work from a specific network (unless I add a new IP from the cPanel of my host account). – Tyler Oct 4 '14 at 22:31
  • Sure you can use a VPN... – Ramhound Oct 4 '14 at 23:32
  • Why is a username and password not sufficient? Because it's being sent plaintext and so a little packet sniffing and your database is accessible by anyone from anywhere. Not a hard concept to grasp, I'd think? Plus if you need remote access and have SSH access, it's pretty simple to run an encrypted tunnel and access the database remotely with security. – Fiasco Labs Oct 5 '14 at 6:55
  • Also, using * rather than localhost means external computers can run brute force password cracks on you SQL Instance. – Simon Hayter Oct 5 '14 at 12:23
1

The reason database servers are not universally open to the world can be boiled down into two main items: Stability & Security

  • Stability: Basically pretty much no database operations for websites should be happening via remote connections. And most database servers are tweaked & tuned to handle expected traffic; not unexpected traffic. So by restricting access to specific users on specific IP addresses the hosting service is assured they do not have to deal with the overhead & headaches that come from random, brute force access attacks.

  • Security: Related to stability is security. If you ever ran any website you would realize that web servers are constantly being probed by robots & spiders looking for weaknesses. Web servers can handle those probe attempts quite well. Database servers, not so much. The core function of a database server is to serve data, not to fight DDoS attempts. And believe me, if you open up a database server to the world you are asking to be easily DDoS'ed into oblivion because database servers are resource hogs.

The thing is as much as database servers can be optimized for traffic, they really need a lot—and I mean A LOT—of care patience to be fine tuned to real stability. And that is on a safely isolated/controlled system. Can you imagine the kind of chaos that would ensue if a database server was just opened up to the world?

This is why the vast majority of websites out there connect to a database via a localhost connection. And the reason why services like Twitter, Flickr & Tumblr use RESTful APIs to access data: Those APIs provide a web server layer that shields the database from direct access. Allowing the web server to handle the blow of hack attempts being made while the database behind the scenes sites happily doing it’s job, free from the headaches of having to deal with that mess.

  • Re: Stability - In large-scale production environments, it is extremely common that web servers and databases are separate machines. In fact, often they are often on separate LANs for security. The only access to the database is remotely for any DBA, SA, and the web server and of course this is tightly controlled. Other than hosting, smaller installations, and similar installations, all large-scale production web accesses are remote. It is a security and performance issue. – closetnoc Oct 5 '14 at 5:52
3

If MySQL servers we open to the general public like your website it would open them up to brute force attacks, and other remote attacks by bots mostly which they simply don't need to deal with. Most people who know enough that they'll need remote MySQL access should be familiar with setting that up in cPanel etc. It's a matter of security for you, and less stress for the hosting company.

  • “It's a matter of security for you, and less stress for the hosting company.” BINGO! – JakeGould Oct 5 '14 at 5:20
1

It actually goes back to how database server security is designed and not for any other reason. Database servers require that explicit username/host access be setup outside of using localhost. This has existed longer than the web as we know it today and is a throwback to the days of big iron where most users where logged on directly using a terminal. Any over the network accesses were explicitly defined. In some web hosting scenarios, this is gotten around by using web based applications. However to access it via a client tool from a remote location, databases still require user/host access to be defined as they always have. Web hosts really do not have a choice in the matter.

1

While the given answers are very valid for the sake of OS security, there is another aspect that must be given consideration: MySQL grants.

When the web hosting company provides a database, they do not want to give away access to other database that are also tenants in the same MySQL Instance.

For example, if you are given the database dbtyler, you want to restrict users to the dbtyler database. You do that with the following

GRANT USAGE ON *.* TO user_tyler@'10.20.30.40' IDENTIFIED BY 'somepassword';

This allow user_tyler to connect to mysqld from 10.20.30.40.

What about access to the dbtyler database ? You then issue this grant

GRANT ALL PRIVILEGES ON dbtyler.* TO user_tyler@'10.20.30.40';

Even if the web hosting company gave away remote access to user_tyler with this

GRANT ALL PRIVILEGES ON dbtyler.* TO user_tyler@'%';

this will still secure the dbtyler database from being accessed by other tenants. In like fashion, this will also prevent user_tyler@'10.20.30.40' from accessing tenants database.

For more information, please see my DBA StackExchange Post About GRANT statement for user creation on how MySQL User Authentication is processed.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.