Solved

Open mysql database access from the outside on a VPS

Posted on 2014-02-14
17
618 Views
Last Modified: 2014-04-11
Hi,

I would like to set up a development database on my vps with access from the outside so it can be used from various locations. How should i do this?

The vps specs are:
- CentOS 6.4 x64
- DirectAdmin
- Server: Localhost via UNIX socket
- Software: MySQL
- Software version: 5.5.35
- Protocol version: 10
-  Apache/2

Thank you!
0
Comment
Question by:peps03
  • 9
  • 8
17 Comments
 
LVL 76

Expert Comment

by:arnold
ID: 39861286
NOTE:  you open the entire database server (mysql) not just a specific database. Though login access is controlled within mysql on whether to grant the connection access.
You need to open port 3306 using iptables if running on the local system.
You need to also open port 3306 or setup port forwarding from <external_port> to 3306 on the external firewall if any.

You then within the users management would add username@remotehost password database

What this will do is grant username/password and database name access when all three are matched.
Make sure you do not have root@% with full admin rights as this is the default account and it will be attacked for compromise.
0
 
LVL 76

Expert Comment

by:arnold
ID: 39861291
A more complex but more secure solution would be setup a VPN connection between the remote system/s and the database VPS.
openVPN, ssh tunnels (always limiting the connection to port 3306 of the VPS and return/established traffic to the remote system)

This way a compromise of one, will limit the exposure on the other.
0
 

Author Comment

by:peps03
ID: 39865435
@arnold thank for your reply.

Ok, so when i open 1 db on the vps, every db will be accessible from the outside? That is not what i what...

are there any other options? do you maybe know of any (hosting) companies that offer db access from outside, so i could you that as a testing db?

thanks
0
 
LVL 76

Expert Comment

by:arnold
ID: 39866380
there are several layers.
0) no external access to mysql server
1) you open a port such that your mysql server is now open to receive connections from anywhere unless you restrict the port forwarding on the firewall/iptables to limit the source of the connections.
2) within your user definition you can restrict the user to a connection from a specific origin and with access to a specific database.

0) your mysql is only suseptible to an attack indirectly, i.e. bad coding on your site (SQL code injection) or the web server is compromised and through it your mysql is attacked.
1) your firewall only allows access from a specific IP/range of IPs, your mysql is vulnerable through the weakest link approach.  Any system to which you grant access if it is compromised through it your mysql server can be attacked.
2) without restriction to IP/IP Segments, anyone who finds your system can attempt to login.  This is the weaker of the three as it is still allowing for an indirect i.e. the system to which you grant access can be compromised and the credentials to mysql obtained. or poor username/password or a dictionary attack, among others could be used to attack your system.

Depending on why you need external access i.e. scope, an opton might be to setup a local web/ajax/php/process such that your remote will "browse" the VPS and make requests and receive responses.

i.e. remote website will have a page.  The backend processing will establish an HTTP/HTTPS session to your VPS' web site and interact with a PHP code through which you will define what commands/parameters/options are permitted.
The VPS web will process and query the mysql database and then return the data as a response. The response is then processed and provided as a response to the requester.

Another option could be to setup a VPN/SSH tunnel between the remote system (as long as you are in full control of it) and the VPS.
and then the connection/queries can be sent via this secure mode without exposing the mysql server unnecessarily.

Setting up a master/master replication might be a consideration depending on the amount of data generated on the VPS and the amount of data as well as frequency of requests from the remote setup.
0
 

Author Comment

by:peps03
ID: 39894628
Arnold, thanks for your reply, sorry for my late reply.

"2) within your user definition you can restrict the user to a connection from a specific origin and with access to a specific database."

Meaning i can create a mysql user and only allow this user to access the database from the outside?

Could this user than also create / have multiple databases?


"Another option could be to setup a VPN/SSH tunnel between the remote system (as long as you are in full control of it) and the VPS. and then the connection/queries can be sent via this secure mode without exposing the mysql server unnecessarily."

I am in full control of it.
Is this difficult to setup and use? (Haven't done this before)

Thanks
0
 
LVL 76

Expert Comment

by:arnold
ID: 39895431
A user can be configured to access multiple existing databases.
You can also grant a user the right to create databases.

http://dev.mysql.com/doc/refman/5.1/en/adding-users.html
Does the system where you have mysql also have phpmyadmin (web interface to manage mysql)?  You could use it to create/manage user accounts/rights

make sure your root user only allowed access locally(localhost,127.0.0.1, or the local name of the system) select * from mysql.user where user='root'
If you have any entry with % and with full rights (most of the entries have a 'Y', you should make sure the password is extremely complex and changed regularly.
The user table has the global user rights which is where you can grant a user the right to create/drop databases.

select * from mysql.db where user='username'

the above will list the hostname, DB and the username along with the access rights the user has . Here you grant the user rights local to the database referenced on this line.

VPN/SSH Not difficult to setup, but you have to take into account issues when the tunnel (VPN or SSH) drops.
Usually a VPN will continue to retry, the ssh tunnel setup will require that you setup an infinate loop with the tunnel establishment process such that when the ssh connection/tunnel drop, the ssh program exits and the infinite loop triggers the establishment of the connection/tunnel again.
0
 

Author Comment

by:peps03
ID: 39917960
Thanks for the reply.

If i create a new user on directadmin, could i open only the database(s) of that user account for outside access? Or will that also open the whole database server (mysql) of the VPS to the outside?
0
 
LVL 76

Expert Comment

by:arnold
ID: 39919149
I am not sure what you are asking.
What permissions are you settings.
look at the mysql.user table this is where global rights are assigned
you then have the mysql.db that grants rights to the specified DB.

To access mysql, you only need a user within mysql you do not need to create a user on the system (i.e. so they can ssh)
0
Free Gift Card with Acronis Backup Purchase!

Backup any data in any location: local and remote systems, physical and virtual servers, private and public clouds, Macs and PCs, tablets and mobile devices, & more! For limited time only, buy any Acronis backup products and get a FREE Amazon/Best Buy gift card worth up to $200!

 

Author Comment

by:peps03
ID: 39920551
Well, if i create a new user in DirectAdmin, would it be possible to open that user's database for outside access only?
So other users databases remain unaccessable from the outside?

I don't see the tables you name, only: information_schema
0
 
LVL 76

Expert Comment

by:arnold
ID: 39920695
Not familiar with directaccess.

I believe I previously outlined this issue.

In order to allow an external user access to mysql, you have to either configure port forwarding on your firewalls to allow any AND all connections (this does not grant access to any data)
Or configure  a way for VPN or use ssh tunnels (more secure)
To achieve the second part using ssh tunnels, you would use directaccess to create a local user that will be used to ssh.  To prevent this user from establishing a shell session, you should define their shell as /bin/true.
What this will do is allow that user to use ssh -T -L Localport:remote_localhost:3306 username@remoteserver -f

This will establish a tunnel without an active shell session.

With the tunnel established, the user can access the mysql using mysql -u username -p -H localhost:Localport databasename

Note if you use remote_localhost as 127.0.0.1 the username in the mysql login will be seen as username@127.0.0.1 using the LAN IP I.e. 192.168.x.x Will restrict this user from being able to attempt a login as root.

The above deal with getting the external user to the mysql door.  To allow a user access, you need to create a mysql user and grant them the rights to connect/login, and the rights to only access to specific databases (entries in mysql.db is where that is controlled)
0
 

Accepted Solution

by:
peps03 earned 0 total points
ID: 39982519
Thanks all, but it's actually as simple as:

MySQL Management > Select DB > Add IP to Access Hosts > Add Host
0
 
LVL 76

Expert Comment

by:arnold
ID: 39982960
This means your mysql is open which is the first question I asked you.
Adding the IP as you have would not establish a login without a defined user@% record.
0
 

Author Comment

by:peps03
ID: 39982986
Oh, i'm sorry i misunderstood you then.

What is the consequence of this approach?
I assume the DB is only "open" for connections from the entered ip-address?

I didn't open / modify any ports.

You said: "Make sure you do not have root@% with full admin rights as this is the default account and it will be attacked for compromise."
>> How do i check this?
>> This could still only occur from the entered ip-address?

Thanks!
0
 
LVL 76

Expert Comment

by:arnold
ID: 39983417
This does mean that your mysql service is not protected by a firewall. It uses internal control to allow or deny the establishment of a connection.

Using command line mysql tool to query
Select * from mysql.user
Using mysql administrator list the users on the system and see whether you have a defined user with % as the host which means this user can connect from anywhere.
See if you connect remotely without providing a username and without providing a database name to see whether the access will be granted.
Checking the mysql.db table to make sure you do not have a default.
0
 

Author Comment

by:peps03
ID: 39983725
I logged in to phpmyadmin with the user i granted external access of 1 IP, ran the query, result:
#1142 - SELECT command denied to user 'XXXXXXXX'@'localhost' for table 'user'

"See if you connect remotely without providing a username and without providing a database name to see whether the access will be granted."
>> can this be done using a webinterface (browser)?

mysql.db > not present, only: information_schema
0
 
LVL 76

Expert Comment

by:arnold
ID: 39984675
Mysql itself has builtin restrictions.
1) deals with when a connection is attempted from an on-listed IP, mysql drops the connection.
2) the IP is authorized, user credentials are provided, mysql will deny the user access when the user is tied to specific databases.  In this type of setup, username/password and database are part of the authentication.
3) you have a DB such as test to which anyone satisfying the first condition can connect without any credentials. Test allows anonymous access.

The user you used in the example does not have select rights on the mysql (security database)

Within phpmyadmin, you have to use an account that has admin rights, usually it is root@localhost
0
 

Author Closing Comment

by:peps03
ID: 39993624
Just add the IP you wish to grant access to the db, easy as that.
0

Featured Post

IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

Join & Write a Comment

Creating and Managing Databases with phpMyAdmin in cPanel.
Nothing in an HTTP request can be trusted, including HTTP headers and form data.  A form token is a tool that can be used to guard against request forgeries (CSRF).  This article shows an improved approach to form tokens, making it more difficult to…
The viewer will learn how to dynamically set the form action using jQuery.
The viewer will learn how to count occurrences of each item in an array.

758 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

21 Experts available now in Live!

Get 1:1 Help Now