Install and Secure phpmyadmin on Ubuntu 14.04

I prefer to use phpmyadmin for MySQL and MariaDB administration. It helps me to visualize the database and run queries from the simple web UI rather than the MySQL command line. While I am comfortable with writing SQL at the command line, having an easy way to see all the database tables, triggers and stored procedures increases my productivity. I don't like having to constantly reference the table names and column names. In phpmyadmin, that information is at the ready.

Exposing a production MySQL database with phpmyadmin introduces some inherent security risks. In order to mitigate those risks, we have to secure phpmyadmin immediately after installation.

This assumes you already have a clean Ubuntu (or Unix variant) installation and have secure SSH access to your server instance.

Install phpmyadmin

OK, let's get started. The first step is to update your local package index using the package manager. Then we'll pull down the files from the web.

sudo apt-get update  
sudo apt-get install phpmyadmin  

After the files are downloaded and installed, a phpmyadmin script will run allowing you to configure phpmyadmin for use.

A special note. When the prompt first appears, it appears that apache2 is already selected. However, it is not really selected, only highlighted. You need to hit the spacebar to select your web server of choice, then tab, then enter.

Chose apache2. Select yes when asked if you will use dbconfig-common to set up the database. Next, you are asked for the root password and then a password to secure phpmyadmin.

Before continuing, we need to enable a mod for phpmyadmin called php5-mcrypt.

sudo php5enmod mcrypt  

Restart your Apache instance to enable these changes.

Now, you are ready to login to your phpmyadmin.

Browse to your domain name and append /phpmyadmin to the domain name or IP address.

phpmyadmin login screen

Enter the database user name and password to login. This is the root user and password you entered during the install.

Once you are logged in to phpmyadmin, you will see this web UI.

phpmyadmin web interface

Secure phpmyadmin

Installing phpmyadmin on Ubuntu is a very easy process. The package manager did most of the work for us. One very important step most new admins overlook is securing phpmyadmin properly. This is especially important for a production database. Because phpmyadmin is in extremely wide use, it is a target for some hackers. You should take the following steps to help secure your phpmyadmin instance from unauthorized use.

A recommended first step in securing your phpmyadmin site is to place an authentication gateway in front of the entire application. To enable this, just use Apache's built-in .htaccess authentication function to lock down access to a specific IP address or require additional authentication. I will do both.

First, enable .htaccess overrides in the Apache config file. Edit the linked file in the config directory.

sudo nano /etc/apache2/conf-available/phpmyadmin.conf  

Add an AllowOverride All directive to the directory section, like below...

<Directory /usr/share/phpmyadmin>  
    Options FollowSymLinks
    DirectoryIndex index.php
    AllowOverride All

Save and close the configuration file and restart your Apache instance.

sudo service apache2 restart  
Create a .htaccess file

In order to enable the .htaccess file, place the new file in the application directory.

sudo nano /usr/share/phpmyadmin/.htaccess  

Next, we are going to enter the following:

AuthType Basic  
AuthName "Restricted Files"  
AuthUserFile /etc/phpmyadmin/.htpasswd  
Require valid-user  

I also add the section to restrict access to the application by IP address. In this section, I allow access from a single IP and deny access from all other IP addresses.

<Limit GET POST>  
 Require all denied
 Require ip

When you are finished entering the necessary configuration and IP's, save and close the file.

Now we need to create the .htpasswd file to be used for our basic HTTP authentication.

You will need to install an additional package to enable this function.

sudo apt-get install apache2-utils  

Next, create the .htpasswd file.

sudo htpasswd -c /etc/phpmyadmin/.htpasswd <username>  

Replace username with your own username. You will be prompted to enter and confirm a password for the username you entered. You can add additional users by running the command again.

Now, browse to the phpmyadmin site again and you will notice a Basic HTTP Auth login prior to loading the phpmyadmin login page. This adds an additional layer of security to your phpmyadmin configuration.

Wrap Up

phpmyadmin is a terrific web-based database utility that I use every day. It works great, allows you to quickly make changes to the database model and when properly configured, is a great asset to developers. It needs to be hardened though, by multiple security best practice methods.

I recommend restricting by Basic Auth, restricting by IP address and of course, the default phpmyadmin login and authentication. This helps prevent authorized access to your database and phpmyadmin application.

Want to go a step further? Secure your phpmyadmin with a SSL certificate and encrypt everything.

Craig Derington

Veteran full stack web dev focused on deploying high-performance, responsive, modern web applications using Python, NodeJS, Django, Flask, MongoDB and MySQL.

comments powered by Disqus