[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 696
  • Last Modified:

Open mysql database access from the outside on a VPS

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
peps03
Asked:
peps03
  • 9
  • 8
1 Solution
 
arnoldCommented:
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
 
arnoldCommented:
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
 
peps03Author Commented:
@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
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
arnoldCommented:
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
 
peps03Author Commented:
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
 
arnoldCommented:
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
 
peps03Author Commented:
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
 
arnoldCommented:
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
 
peps03Author Commented:
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
 
arnoldCommented:
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
 
peps03Author Commented:
Thanks all, but it's actually as simple as:

MySQL Management > Select DB > Add IP to Access Hosts > Add Host
0
 
arnoldCommented:
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
 
peps03Author Commented:
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
 
arnoldCommented:
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
 
peps03Author Commented:
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
 
arnoldCommented:
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
 
peps03Author Commented:
Just add the IP you wish to grant access to the db, easy as that.
0

Featured Post

Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

  • 9
  • 8
Tackle projects and never again get stuck behind a technical roadblock.
Join Now