How to install and secure phpMyAdmin on Ubuntu 16.04

Introduction

Although many users need the functionality of a database management system like MySQL, they may not only interact with the system through MySQL prompts.

You can create phpMyAdmin so that users can interact with MySQL through the web interface. In this tutorial, we will discuss how to install and secure phpMyAdmin so that you can safely use it to manage databases from Ubuntu 16.04 systems.

Preparation

Before you start using this tutorial, you need to complete some basic steps.

First, you need an Ubuntu server that has a non-root account that can use the sudo command and has a firewall turned on. Students who don’t have a server can buy it from here, but I personally recommend you to use the free Tencent Cloud Developer Lab for experimentation, and then Buy Server. We assume that you are using a non-root user with sudo privileges.

We also assume that you have completed the LAMP (Linux, Apache, MySQL and PHP) installation on the Ubuntu 16.04 server. If you use it in a production environment, you can also use [Cloud Relational Database] (https://cloud.tencent.com/product/cdb-overview?from=10680).

Finally, there are some important security considerations when using software like phpMyAdmin, because it:

• Direct communication with MySQL installation
• Use MySQL credentials to handle authentication
• Execute and return the results of any SQL query

For these reasons, and because it is a widely deployed PHP application that often targets attacks, phpMyAdmin should not be run on a remote system via a normal HTTP connection. If you do not have an existing domain configured with SSL/TLS certificates, it is recommended that you go here first register a domain name, you need to add domain name resolution to your server, you can use [Tencent cloud cloud analysis] (https://cloud.tencent.com/product/cns?from=10680) for quick setting.

After completing these steps, you can start using this tutorial.

Step one-install phpMyAdmin

First, we will install phpMyAdmin from the default Ubuntu repository.

We can do this by updating the local package index and then using the apt packaging system to drop down the files and install them on our system:

sudo apt-get update
sudo apt-get install phpmyadmin php-mbstring php-gettext

This will ask you some questions in order to properly configure your installation.

**Warning: When the first prompt appears, apache2 will be highlighted, but ** will not be selected. If you did not press the Space key to select Apache, the installer will not move the necessary files during the installation. Use the Space, Tab and Enter keys to select Apache.

• For server selection, please select apache2.
• When asked whether to use dbconfig-common to set the database, please select "Yes"
• You will be prompted to enter the password of the database administrator
• Then, you will be asked to select and confirm the password of the phpMyAdmin application itself

The installation process actually adds the phpMyAdmin Apache configuration file to the /etc/apache2/conf-enabled/ directory and automatically reads the directory.

The only thing we need to do is to explicitly enable the PHP mcrypt and mbstring extensions, which we can execute by entering the following command:

sudo phpenmod mcrypt
sudo phpenmod mbstring

After that, you need to restart Apache to recognize your changes:

sudo systemctl restart apache2

You can now access the web interface by accessing the server's domain name or the public IP address followed by /phpmyadmin, and then do the following:

https://domain_name_or_IP/phpmyadmin

You can now log in to the interface using the root username and administrative password set during the MySQL installation.

When you log in, you will see the user interface as shown below:

Step 2-Protect your phpMyAdmin instance

We can easily start and run the phpMyAdmin interface. However, we are not done yet. Because of its ubiquity, phpMyAdmin is a popular target for attackers. We should take additional measures to prevent unauthorized access.

One of the easiest ways is to put the gateway in front of the entire application. We can use Apache's built-in .htaccess authentication and authorization functions to achieve this.

Configure Apache to allow .htaccess override

First, we need to enable .htaccess file override by editing the Apache configuration file.

We will edit the link file that has been placed in the Apache configuration directory:

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

We need in the configuration file<Directory /usr/share/phpmyadmin> Add an AllowOverride All directive to the `section, as shown below:

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

After adding this line, save and close the file.

To implement the changes you made, restart Apache:

sudo systemctl restart apache2

Create .htaccess file

Now that we have enabled .htaccess for our application, we need to create one to actually implement some security.

To successfully complete this step, the file must be created in the application directory. We can create the necessary file and open it in our text editor with root privileges by typing:

sudo nano /usr/share/phpmyadmin/.htaccess

In this file, we need to enter the following information:

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

Let's review the meaning of these lines:

• AuthType Basic: This line specifies the type of authentication we are implementing. This type will use a password file to achieve password authentication.
• AuthName: This will set the message of the authentication dialog. You should maintain this universality so that unauthorized users do not obtain any information about protected content.
• AuthUserFile: This will set the location of the password file that will be used for authentication. This should be outside the directory being provided. We will create this file soon.
• Require valid-user: This specifies that only authenticated users should be granted access to this resource. This effectively prevents unauthorized users from entering.

When finished, save and close the file.

Create .htpasswd file for authentication

The location we chose for the password file is "/etc/phpmyadmin/.htpasswd". We can now create this file and use the htpasswd utility to pass the initial user to it:

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

You will be prompted to select and confirm a password for the user you are creating. Then, create a file with the hashed password you entered.

If you want to enter other users, you need to do this without the **-c flag **, as shown below:

sudo htpasswd /etc/phpmyadmin/.htpasswd additionaluser

Now, when you visit the phpMyAdmin subdirectory, you will be prompted to enter the other account name and password you just configured:

https://domain_name_or_IP/phpmyadmin

After entering Apache authentication, you will enter the regular phpMyAdmin authentication page to enter other credentials. This will add an extra layer of security because phpMyAdmin has been vulnerable to vulnerabilities in the past.

in conclusion

You should now have phpMyAdmin configured and ready to be used on your Ubuntu 16.04 server. Using this interface, you can easily create databases, users, tables, etc., and perform routine operations such as deleting and modifying structures and data.

To learn more about the installation and protection of phpMyAdmin related tutorials, please go to [Tencent Cloud + Community] (https://cloud.tencent.com/developer?from=10680) to learn more.

Reference: "How To Install and Secure phpMyAdmin on Ubuntu 16.04"