How To Install and Secure phpMyAdmin on Ubuntu 20.04

An earlier version of this tutorial was written by Brennan Bearnes.

Introduction

While many users need the functionality of a database management system like MySQL, they may not be comfortable interacting with the system solely from the MySQL prompt

phpMyAdmin was created so that users can interact with MySQL through a web interface. In this guide, we will discuss how to install and secure phpMyAdmin so that you can safely use it to manage your databases on an Ubuntu 20.04 system.

Prerequisites To

complete this guide, you will need:

An Ubuntu 20.04 server. This server must have a non-root user with administrative privileges and a firewall configured with ufw. To set this up, follow our initial server setup guide for Ubuntu 20.04.
A LAMP stack (Linux, Apache, MySQL and PHP) installed on your Ubuntu 20.04 server. If this has not yet been completed, you can follow this guide on installing a LAMP stack on Ubuntu 20.04.

In addition, there are important security considerations when using software like

phpMyAdmin, since:

Communicates directly with your

MySQL installation

Handles authentication using MySQL credentials
Executes and returns results for arbitrary SQL queries

For these reasons, and because it is a widely deployed PHP application that is frequently targeted, you should never run phpMyAdmin on remote systems over a simple HTTP connection.

If you don’t have an existing domain set up with an SSL/TLS certificate, you can follow this guide on how to secure Apache with Let’s Encrypt on Ubuntu 20.04. This will require you to register a domain name, create DNS records for your server, and set up an Apache virtual host.

Step 1 — Installing

phpMyAdmin

You can use APT to install phpMyAdmin from Ubuntu’s default repositories

As a non-root sudo user, update your server’s package index:

sudo apt update

After that, you can install the phpmyadmin package. Along with this package, the official documentation also recommends that you install some PHP extensions on your server to enable certain functionalities and improve performance.

If you followed the LAMP stack tutorial as a prerequisite, several of these modules will have been installed along with the php package. However, it is recommended that you also install these packages

php-mbstring: A module to manage non-ASCII strings and convert strings to different
php-zip encodings: This extension supports uploading .zip files to phpMyAdmin
php-gd: Enables support for the GD graphics library php-json
: Provides PHP with support for JSON
serialization php-curl: Allows PHP to interact with different types of servers using different protocols

Note that if you are using a version of PHP other than the default installed in the prerequisite LAMP stack tutorial, you will need to install the appropriate versions of these module packages. For example, if you are using PHP version 8.0, you will need to install the php8.0-mbstring package instead of the default php-mbstring package.

Run the following command to install these packages on your system. Note, however, that the installation process requires you to make some decisions to configure phpMyAdmin correctly. We will see these options shortly

sudo apt install phpmyadmin php-mbstring php-zip php-gd php-json php-curl

Here are the options you should choose when prompted to configure your installation correctly:

For server selection, choose apache2

Select Yes when prompted to use dbconfig-common to configure the database
Next, you will be asked to choose and confirm a MySQL application password for

phpMyAdmin

The installation process adds the phpMyAdmin Apache configuration file to the /etc/apache2/conf-enabled/ directory, where it is automatically read. To finish configuring Apache and PHP to work with phpMyAdmin, the only remaining task in this section of the tutorial is to explicitly enable the mbstring PHP extension, which you can do by typing

: sudo

phpenmod mbstring

Then, restart Apache so that your changes are recognized:

sudo systemctl restart apache2

phpMyAdmin is now installed and configured to work with Apache. However, before you can log in and start interacting with your MySQL databases, you will need to ensure that your MySQL users have the necessary privileges to interact with the program.

Step 2 — Setting user authentication and privileges

When you installed phpMyAdmin on your server, it automatically created a database user named phpmyadmin that performs certain underlying processes for the program. Instead of logging in as this user with the administrative password you set during installation, it is recommended that you log in as your MySQL root user or as a user dedicated to managing databases through the phpMyAdmin interface.

Configuring password access for

the MySQL root account

On Ubuntu systems running MySQL 5.7 (and later), the MySQL root user is configured to authenticate using the auth_socket plugin by default instead of with a password. This allows for greater security and ease of use in many cases, but it can also complicate things when you need to allow an external program, such as phpMyAdmin, to access the user.

To log in to phpMyAdmin as your MySQL root user, you’ll need to change your authentication method from auth_socket to one that uses a password, if you haven’t already. To do this, open

the MySQL prompt from your terminal: sudo mysql

Next, check which authentication method each of your MySQL user accounts uses with the following command:

SELECT user,authentication_string,plugin,host FROM mysql.user;

In this example, you can see that the root user authenticates using the auth_socket snap-in. To configure the root account to authenticate with a password, run the following ALTER USER command. Be sure to change the password to a strong password of your choice

: ALTER USER ‘root’@’

localhost’ IDENTIFIED WITH caching_sha2_password BY ‘password’

;

Then, check the authentication methods employed by each of your users again to confirm that root is no longer authenticated using the auth_socket

plugin:

SELECT user,authentication_string,plugin,host FROM mysql.user;

You can see in this output that the root user will be authenticated with a password. You can now log in to the phpMyAdmin interface as your root user with the password you have set for it here.

Setting up password access for a dedicated MySQL

user

Alternatively, some may find that it is better suited to their workflow to connect to phpMyAdmin with a dedicated user. To do this, open the MySQL shell once again

sudo

mysql

If you have password authentication enabled for your root user, as described in the previous section, you will need to run the following command and enter your password when prompted to connect

mysql -u root -p

From there, create a new user and assign it a strong password:

CREATE USER ‘sammy’@’

localhost’ IDENTIFIED WITH caching_sha2_password BY ‘password’;

Then grant your new user the appropriate privileges. For example, you can grant user privileges to all tables in the database, as well as the power to add, change, and remove user privileges, with this command

GRANT ALL PRIVILEGES ON *.* TO ‘sammy’@’localhost’ WITH GRANT OPTION;

After that, exit the MySQL shell:

exit You can now access the web interface by visiting your server’s domain name or public IP address followed by

/phpmyadmin: https://your_domain_or_IP/phpmyadmin

phpMyAdmin login screen

When you log in, you will see the user interface, which will look like this:

<img src="https://assets.digitalocean.com/articles/phpmyadmin_2004/pma_home_sammy.png" alt="phpMyAdmin

user interface” />

Now that you can connect and interact with phpMyAdmin, all that’s left to do is strengthen the security of your system to protect it from

attackers.

Step 3 — Secure your

phpMyAdmin instance

Due to its ubiquity, phpMyAdmin is a popular target for attackers, and you should take special care to prevent unauthorized access. One way to do this is to place a gateway in front of the entire application using Apache’s built-in .htaccess authentication and authorization capabilities.

To do this, you must first enable the use of overrides of .htaccess files by editing the Apache configuration file of your phpMyAdmin installation.

Use your preferred text editor to edit the phpmyadmin.conf file that has been placed in your Apache configuration directory. Here, we will use

nano:

sudo nano /etc/apache2/conf-available

/phpmyadmin.conf Add an AllowOverride All directive within the <Directory /usr/share/phpmyadmin> section

of the configuration file, like this: <Directory /usr/share/phpmyadmin>

SymLinksIfOwnerMatch DirectoryIndex index.php AllowOverride All . . .

When you have added this line, save and close the file. If you used nano to edit the file, do so by pressing CTRL+X, Y, and then ENTER.

To deploy the

changes made, restart Apache:

sudo systemctl restart apache2

Now that you’ve enabled the use of .htaccess files for your application, you need to create one to really implement some security.

For this to be successful, the file must be created within the application directory. You can create the necessary file and open it in your text editor with root privileges by typing: sudo

nano /usr/share/phpmyadmin/.htaccess

Within this file, enter the following information

: AuthType Basic AuthName “Restricted Files” AuthUserFile /etc/

phpmyadmin/.htpasswd

Require valid user

Here’s what each of these lines means:

AuthType Basic: This line specifies the type of authentication you are implementing. This type will implement password authentication using a password file.
AuthName: Sets the message for the authentication dialog box. You should keep this generic so that unauthorized users do not get any information about what is being protected.
AuthUserFile: Sets the location of the password file to be used for authentication. This should be outside the directories being served. We will create this file shortly.
Require valid user: Specifies that only authenticated users should have access to this resource. This is what really prevents unauthorized users from entering.

When you’re done, save and close the

file.

The location you selected for your password file was /etc/phpmyadmin/.htpasswd. You can now create this file and pass it to an initial user using

the htpasswd utility: sudo htpasswd

-c /etc/phpmyadmin/.htpasswd username

You will be prompted to select and confirm a password for the user you are creating. The file is then created with the hashed password you entered.

If you want to enter an additional user, you must do so without the -c flag, like this

: sudo htpasswd /

etc/phpmyadmin/.htpasswd additionaluser

Then restart Apache to implement authentication .htaccess

sudo systemctl restart apache2

Now, when you access your phpMyAdmin subdirectory, you will be asked for the additional account name and password you just set up:

https://domain_name_or_IP/phpmyadmin <img src="https://assets.digitalocean.com/articles/phpmyadmin_2004/pma_htaccess_small.png" alt="phpMyAdmin

apache password” />

After entering

Apache authentication, you will be taken to the normal phpMyAdmin authentication page to enter your MySQL credentials. By adding an additional set of non-MySQL credentials, you are providing your database with an extra layer of security. This is desirable, as phpMyAdmin has been vulnerable to security threats in the past.

Conclusion

You should now have phpMyAdmin set up and ready to use on your Ubuntu 20.04 server. With this interface, you can create databases, users, and tables, as well as perform common operations such as deleting and modifying structures and data.