How to organize SSH users and setup them?

Question

I want all users to use your public/private key to any kind access to my server. Into SSH configurations, I will setup 3 groups:

1. sshaccess: Users can use shell, SFTP and tunnels;
2. sftpaccess: Users can't use the shell, but can use SFTP;
3. mysqlaccess: Users can't use the shell, but can use TCP Forwading to port 3306.

I will allow only theses 3 groups to use SSH connection:

AllowGroups sshaccess sftpaccess mysqlaccess

To users with sftpaccess group, I will force SFTP and lock user in your home folder:

Match Group sftpaccess
    ForceCommand internal-sftp
    ChrootDirectory %h
    PermitTunnel no
    AllowAgentForwarding no
    AllowTcpForwarding no

To users with mysqlaccess group, I will allow tunnel on port 3306:

Match Group mysqlaccess
    Banner no
    PermitTunnel yes
    AllowTcpForwarding yes
        PermitOpen 127.0.0.1:3306

But, let's suppouse I have the user caique and he needs:

• Acessing the SFTP, locked in your home folder;
• Acessing the MySQL Tunnel to connect with your credentials.

I don't want to create an user to SFTP an another user to MySQL Tunnel, so what is the best way to achieve this?

I don't want expose the MySQL port, therefore I use MySQL Tunnel. But, in my tests, I could not use Tunnel for users associated with sftpaccess, what am I missing?

Answer 1

How about a section like:

Match Group sftpaccess Group mysqlaccess
    ForceCommand internal-sftp
    ChrootDirectory %h
    AllowAgentForwarding no
    Banner no
    PermitTunnel yes
    AllowTcpForwarding yes
        PermitOpen 127.0.0.1:3306

Admittedly, this requires duplication, and may or may not work depending on if sshd loads all of a users groups (I suspect it does).

Alternatively, you could make a user-specific Match (which tends to assume you won't get more such users.

I suspect the ordering of the rules matters, but I'm not sure if this should be first or last (relative to the other two). I think it should be first.