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.

Tom Hunt
  • 10,056
Wistar
  • 183
  • 1
  • 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 Oct 09 '15 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 Oct 09 '15 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 Oct 09 '15 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 Oct 09 '15 at 15:21

1 Answers1

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.

Tom Hunt
  • 10,056
  • But the user (who performs the ssh command) can still access other locally open ports, not just MySQL? – sebix Oct 10 '15 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 Oct 11 '15 at 01:14
  • However with this sbin/nologin user, I cannot su – Wistar Nov 25 '15 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 https://unix.stackexchange.com/questions/55106/disable-user-shell-for-security-reasons) – Arik Mar 04 '18 at 22:47