8

I want to set up an SSH tunnel to my MySQL database on a remote server. Can I create an SSH user that has only the minimum permissions that are strictly necessary to access MySQL, for example with a database management application like HeidiSQL or Sequel Pro? I don't want the user to be able to access anything else.

4
  • If you're creating a tunnel with -L or similar, then you could create a user with /sbin/nologin or equivalent in $SHELL, and use ssh -N to disable running a command. That way, all they could do is create the tunnel, and no remote shell or command access would be allowed at all.
    – Tom Hunt
    Commented Oct 9, 2015 at 15:13
  • @TomHunt It looks like what I would to achieve. Would the user still be able to connect to the MySQL database through this tunnel?
    – Wistar
    Commented Oct 9, 2015 at 15:17
  • You could run a local SQL client, telling it the local address and the tunnel port, and it would connect to the remote SQL server. The user would then have to do the usual authentication to the server; SSH doesn't do anything about that.
    – Tom Hunt
    Commented Oct 9, 2015 at 15:18
  • @TomHunt sounds great. Could you post the answer with slightly more detail on how to create a user with this level of permissions?
    – Wistar
    Commented Oct 9, 2015 at 15:21

1 Answer 1

3

You can add a user without a valid login shell:

# useradd -s /sbin/nologin dbuser

Set their password:

# passwd dbuser

Or leave it unset and make SSH keys:

(on local machine)
$ ssh-keygen

(on remote machine)
# su -s /bin/bash - dbuser
$ cat local_id_rsa.pub >>~/.ssh/authorized_keys

At this point, you can use SSH to create the tunnel:

ssh -TfnN -L localhost:<local_port>:localhost:<db_server_port> dbuser@remote_host

If you used SSH keys to authenticate, this will work automatically, otherwise you'll need to type in a password. ssh will go to background immediately after authenticating, and will not attempt to execute any command, but the tunnel will be open. Type 'localhost' and the <local_port> value into your local DB client, and it'll work. However:

$ ssh dbuser@remote_host
dbuser@remote_host's password:
Last login: Fri Oct  9 09:27:24 2015 from local_host
This account is currently not available.
Connection to remote_host closed.

SSH will not execute any shell or command as the remote user; /sbin/nologin will kick it out every time.

4
  • But the user (who performs the ssh command) can still access other locally open ports, not just MySQL?
    – sebix
    Commented Oct 10, 2015 at 11:19
  • Yes. There may be some way to regulate that on the sshd end; I don't know it at the moment.
    – Tom Hunt
    Commented Oct 11, 2015 at 1:14
  • However with this sbin/nologin user, I cannot su
    – Wistar
    Commented Nov 25, 2015 at 16:04
  • 2
    I suggest to check first if /sbin/nologin exists, and if not use /usr/sbin/nologin or /bin/false instead (see unix.stackexchange.com/questions/55106/…)
    – Arik
    Commented Mar 4, 2018 at 22:47

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .