TODO app

This is a basic to-do list implemented with an N-tier architecture.

• MySQL database
• PHP backend API
• Javascript frontend (Vue JS)

This is also commonly referred to as the LAMP stack.

Table of Contents

1. Provision Open Stack Server Virtual Hardware (Linux on Nebula)
   1. Connect to Server via SSH (Putty)
2. Install Web Server (Apache)
3. Install Database Management System Software (MySQL)
   1. Harden Security
   2. Deploy Application Physical Schema
      1. Create Database
      2. Execute DDL
      3. Provision User Accounts
      4. Authorize User Accounts
4. Install PHP
   1. Deploy Application Web Service
   2. Install DBMS interface (phpmyadmin)

Provision Virtual Machine

You will need access to a private server. The University IT Services provides this at no cost for students.

Login: https://nebula.uark.edu

If your password doesn't work, then you will need to request access here. Fill out the form with all the defaults to request a nebula account, and then wait until they respond creating your account.

Click on Launch Instance

Click on Details and name the server todoserver

Click on Source and choose Image from Select Boot Source

Choose the Ubuntu16.04-LTS image by clicking the respective +

Click Flavor and choose the m1.tiny

We will need a way to login to the server. This is accomplished through SSH and public-private key pair cryptography. It's like creating a cryptographic lock on the server to which we have a key.

Click Key Pair and then + Create Key Pair

Name it todoserver and click Create Keypair

Finally, click Launch Instance

In order to make the server accessible to our network, we need to associate a LAN IP address.

At last, verify that the server is running on the 10.0.0.0/8 network.

SSH with Putty

Download and install putty.

Start PuTTYgen

Load the todoserver.pem file we generated and downloaded from nebula.

Now we need to convert the pem file to a usable ppk format for putty.

Save it as todoserver.ppk somewhere safe, such as Desktop or S:\ drive.

Start Putty

Enter ubuntu@10.9.0.xxx (ubuntu at your server floating IP) for Host Name.

Save the session as todoserver

Visit Connection > SSH > Auth and Browse to todoserver.ppk

Click Open and in your most hacker voice, say "I'm in"

Install Apache

First we want to update the software repositories for the package manager apt

sudo apt update

Then we want to upgrade existing software get the latest patches.

sudo apt upgrade -y

Next let's install the web server.

sudo apt install -y apache2

Now open your browser (firefox/chrome) in VMWare and goto your server IP address.

Install MySQL

sudo apt install -y mysql-server

When prompted, choose a root user password, and write it down.

Once installation has finished, login to the console client.

mysql --user root --password

When prompted, enter the password for root that you just created.

Now you are able to write and execute SQL. For example, show a list of all databases:

SHOW DATABASES;

All you should see at this point are system databases because we haven't created any yet. In order to switch to a database namespace context, use a USE statement.

USE mysql;

Now you can view a list of all tables in that database.

SHOW TABLES;

When you are finished exploring the data, exit the program.

quit

Harden Security

Run the secure installation script:

mysql_secure_installation

You will be presented with a series of questions.

Would you like to setup VALIDATE PASSWORD plugin?

yes

There are three levels of password validation policy:

LOW Length >= 8
MEDIUM Length >= 8, numeric, mixed case, and special characters
STRONG Length >= 8, numeric, mixed case, special characters and dictionary

Please enter level of password validation policy.

A minimum password length of 8 characters is not good enough, but don't worry right now because we'll fix this later.

2

Change the password for root?

yes

Choose an awesome password for root and write it down somewhere safe.

Remove anonymous users?

yes

Disallow root login remotely?

yes

Remove test database and access to it?

yes

Reload privilege tables now?

yes

Now we can login to the client and check our password validation options.

mysql -u root -p

SHOW VARIABLES LIKE 'validate_password%';

As was mentioned earlier, we need to increase the minimum password length to at least 13.

Remember, a short, but wild mix of random characters is much easier to crack than a long string of lowercase letters.

Passwords are out! Passphrases are in!

Note one caveat with MySQL Replication limits password lengths to a maximum of 32 characters.

Lastly, if you really want to step up your infosec game, then add a dictionary.

In order to persist these settings after reboot, they need to be saved in the service configuration file. Edit the daemon configuration file to set startup variables.

sudo vim /etc/mysql/mysql.conf.d/mysqld.cnf

At the bottom of the file, add the following lines:

# password cannot be username
validate_password_check_user_name=ON
# minimum password length
validate_password_length=13
validate_password_policy=STRONG

Restart the daemon to load your changes.

sudo service mysql restart

Deploy Application Physical Schema

Change directory into the web folder.

cd /var/www

Clone this project as root.

sudo git clone https://github.com/ISYS4283/todo-app.git

Change directory into the project folder.

cd todo-app

Create Database

The following commands should be executed from the mysql console client.

Provision a database on the server using CREATE DATABASE syntax.

CREATE DATABASE todoapp CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_520_ci;

See also: choosing a collation.

Define Schema

Execute the data definition language for the application.

mysql -u root -p todoapp < sql/ddl.sql

Create Users

Provision a user account on the server using CREATE USER syntax.

CREATE USER jeff IDENTIFIED BY 'ISYS4283 is the best!';

Grant Permissions

Authorize access using GRANT syntax following the principle of least privilege.

GRANT SELECT ON todoapp.view_todos TO jeff;
GRANT SELECT (id) ON todoapp.todos TO jeff;
GRANT INSERT ON todoapp.todos TO jeff;
GRANT UPDATE ON todoapp.todos TO jeff;
GRANT DELETE ON todoapp.todos TO jeff;

If you would like to create an administrative user, then GRANT ALL PRIVILEGES with an asterisk wildcard for entities.

GRANT ALL PRIVILEGES ON todoapp.* TO 'adminuser'@'localhost';

Install PHP

Install php, Apache interpreter, and MySQL drivers with multibyte support.

sudo apt install -y php libapache2-mod-php php-mysql php-mbstring

Deploy App

Edit virtual host configuration and set document root to project public folder.

sudo vim /etc/apache2/sites-available/000-default.conf

Change this line:

DocumentRoot /var/www/html

to this line:

DocumentRoot /var/www/todo-app/public

Restart the web server.

sudo service apache2 restart

Browse to the website in firefox or chrome now to use the ToDo app.

Install PHPMyAdmin

For administrators and power users, provide a more robust interface for MySQL.

sudo apt install -y phpmyadmin

Push the Space bar to select apache2 for auto-configuration.

Select the defaults for the remaining options.

Browse to your server IP address's http://10.9.0.198/phpmyadmin/