Add "Readonly" Database user to Forge

To achieve the goal of allowing the data analyst to access the MySQL database via SSH tunneling without granting them full SSH access to the server, you can follow these steps:

1. Create a New System User: Create a new system user specifically for the data analyst. This user will have limited permissions and will be used solely for the purpose of SSH tunneling.

2. Restrict SSH Access: Configure the SSH settings to restrict this new user from accessing the shell.

3. Set Up MySQL User: Ensure the MySQL user has only the SELECT privilege.

Here is a step-by-step guide:

Step 1: Create a New System User

Log in to your server as a user with sudo privileges and create a new user:

sudo adduser analyst

Step 2: Restrict SSH Access

Edit the SSH configuration file to restrict the new user from accessing the shell. Open the SSH configuration file:

sudo nano /etc/ssh/sshd_config

Add the following lines at the end of the file to restrict the analyst user:

Match User analyst
    ForceCommand /bin/false
    AllowTcpForwarding yes
    PermitTunnel yes

This configuration forces the analyst user to execute /bin/false upon login, effectively preventing shell access, but allows TCP forwarding and tunneling.

Step 3: Add the SSH Key

Add the analyst's SSH public key to the new user's ~/.ssh/authorized_keys file:

sudo mkdir /home/analyst/.ssh
sudo nano /home/analyst/.ssh/authorized_keys

Paste the analyst's public key into the authorized_keys file and save it. Set the appropriate permissions:

sudo chown -R analyst:analyst /home/analyst/.ssh
sudo chmod 700 /home/analyst/.ssh
sudo chmod 600 /home/analyst/.ssh/authorized_keys

Step 4: Set Up MySQL User

Ensure the MySQL user has only the SELECT privilege. You can do this by running the following SQL commands:

CREATE USER 'readonly_user'@'localhost' IDENTIFIED BY 'password';
GRANT SELECT ON database_name.* TO 'readonly_user'@'localhost';
FLUSH PRIVILEGES;

Replace readonly_user, password, and database_name with the appropriate values.

Step 5: SSH Tunnel Setup

The data analyst can now set up an SSH tunnel to access the MySQL database. They can use the following command on their local machine:

ssh -L 3306:localhost:3306 analyst@your_server_ip

This command forwards the local port 3306 to the server's port 3306, allowing the analyst to connect to the MySQL database using a MySQL client on their local machine:

mysql -u readonly_user -p -h 127.0.0.1

By following these steps, you ensure that the data analyst can access the MySQL database securely via SSH tunneling without having full SSH access to the server.