TCP hand off?

[Rich]

I have a database that I want to be able to allow users to connect to. I didn't design this database, just trying to come up with a way to allow users to connect.  The way they set this database up is that there are three users, Read, Read/write, and Admin.  What I have done is write a tool that can authenticate a specific user such as jsmith234, he's a member of the Admin team, when using my cli tool it logs jsmith in using the admin user of the database. JDoe432 is a member of the read group, only gets read access, when they run the tool, the tool logs the user in with read-only access.

Users can query the database, get into a mysql shell, dump the database, restore the database, save tables as excel, or csv -- but only from the command line. What my users want to do is be able to use tools like mysql workbench.  I don't want to give them the database passwords as then you're constantly getting emails with people asking for the right creds, then you've got to look them up, give them the right user which is a pain.  I don't want to get into what happens when we reset the password which by corporate standards is every 90 days. Currently I only have to change it in the database and in my tool. 

My question is if there is a way to write a middleware app that can be run, so that the user logs in with SSH, the tool authenticates them, and then starts a port forward back to their system allowing mysql workbench to connect. 

[Jason E. Aten]

I don't recall (would have to re-read the SSH RFCs) if an ssh server is allowed to initiate a tunnel. It might have to be started on the client side. But that is pretty easy to arrange using the ssh -L flag.

Could you have them run your authentication tool after they ssh login with ssh -L 3306:127.0.0.1:3307  user@databasehost, so the workbench tunnel is already started... and have the authentication tool forward 3307 to 3306 only if authentication succeeds?  Hmm.... they still wouldn't have logged in yet though... or would they?  It's hard to say without understanding the details of your authentication tool.

That would be (less code) simple approach...if it works.  If you want to get complicated, I have an ssh library for that which may be helpful.

https://github.com/glycerine/sshego

It lets you write custom ssh servers, clients, etc.

[Jason E. Aten]

Hmm.... The mysql workbench is going to expect to run the mysql authentication handshake... so you would have to jump in the middle of the tcp stream and substitute the actual password, no?   See here for how a new forward tunnel can be run from a client

https://github.com/glycerine/sshego/blob/05503dd206cf9022d58b7c6ea04e112e65f40659/sshutil.go#L392

and then also instead of the simple io.Copy at 

https://github.com/glycerine/sshego/blob/05503dd206cf9022d58b7c6ea04e112e65f40659/shovel.go#L66

you could filter for the credentials and substitute the actual correct one for the user.

This would be client side though, so users could use strings on the binary to find the actual passwords. But you could also intercept the tcp pump on the server side I think; here:

https://github.com/glycerine/sshego/blob/05503dd206cf9022d58b7c6ea04e112e65f40659/direct.go#L73