Linux in the classroom

The simple goal was to use Microsoft Azure services to setup a traditional SQL Database (SQL Server) running in the Azure cloud. Students had $100 credit towards the services. The problem we ran into is that by using all the default settings, you end up with $380/month system that was going to blow through the student credit too quickly.

So we “decommissioned” the systems we had setup and began the process of working on a local system … a Linux system in my office. The first challenge was installing all the software as shown below:

sudo apt install mysql-server apache2 phpmyadmin
sudo apt install libapache2-mod-php

This installs the basic lamp stack.

Next challenge is making sure the system is at least somewhat locked down. The default mysql setup is to only allow connections from localhost. No need to make changes there as students will be interacting with MySQL through the phpmyadmin interface.

Phpmyadmin by default sets up an alias under the URL phpmyadmin. The first step in securing is simply to change this alias. Now, of course, this is just security by obscurity. But the VAST majority of phpmyadmin attacks are going to be looking specifically for the /phpmyadmin path.

Next step was making sure that on-campus computers could indeed reach the web server and new phpmyadmin setup. No problem, can check that from the computer I’m writing this post on.

phpmyadmin login screen accessed from different computer via ip address

There’s no way to even check for “off-site” access as you cannot make it to the IP address since it is a local ip. You must be on the same network. And as I would discover based on our own internal firewall rules, you have to be on the “correct” WI-FI network.

Finally, the last piece of the puzzle and arguably the most tricky is to generate mysql user accounts for each student in the class, assigning them unique passwords, and granting them access to a newly created (but empty schema) database.

I could adapt this PHP script and set a password for the root mysql user which would have all the necessary permissions to execute the given queries. BUT, there is a reason why there is no root password by default on modern MySQL installations. The security of the root user is simply tied into the fact that it is typically harder to get access to the root operating system account, and the default config will automatically log you into the mysql client only if you are logged in as the root user.

That being said, an extra level of protection by setting a MySQL specific root password (it should not be the same as the root OS password!!!) has advantages and disadvantages. The primary disadvantage is that MySQL root password now has to be written down somewhere (such as in the script I linked to earlier). Likely, that root password will end up in any many places.

The advantage is that if they discover a vulnerability that allows a user to execute commands as root on the host OS, they will also need to discover the MySQL root password that you also set. As I mentioned in the previous paragraph, if you are using a root password then there is probably a script or text file somewhere on that system with the root password in it. And if the user has root level access to the OS, how hard will it really be for them to find it? So what extra level of security does it really add?

Super big headache trying to get password-less unix socket based authentication to work, so I ended up adapting and porting the stack overflow script into Python, but create a new MySQL user with access to create other new MySQL user accounts. Once I generated all the new user accounts with the modified script below, I then deleted the new root-level mysql account and scrubbed the script of the temporary password that I ended up using. Here is the final adapted Python script:

import mysql.connector
import csv

def createUser(connection, username, password, dbname):
    try:
        if connection.is_connected():
            queries = [f"CREATE USER '{username}'@'localhost' IDENTIFIED BY '{password}'",\ 
                        f"GRANT all privileges ON {dbname}.* TO '{username}'@'localhost'",\ 
                         "FLUSH PRIVILEGES"]
            for query in queries:
                print(query)
                cursor = connection.cursor()
                cursor.execute(query)
                records = cursor.fetchall()
                for row in records:
                    print(row)
    except mysql.connector.Error as e:
        print(e)
   
connection = mysql.connector.connect(host='127.0.0.1',user='************',password='*********************')
with open('107users.csv') as csvfile:
    reader = csv.reader(csvfile)
    for row in reader:
        createUser(connection, row[0], '***********', f"{row[0]}db")
        print(row[0])

Surprisingly, this Python script worked on the first run, enabling my students to login to phpmyadmin and create the assigned database.

Then we built a web-app together that could function as a very simple admin UI for the backend database.

Cloud Calculator ADMIN v0.1

Leave a comment

Your email address will not be published. Required fields are marked *