How To Install and Secure phpMyAdmin on Ubuntu 18.04 LTS

 

phpMyAdmin is a free and open source administration tool for MySQL and MariaDB. phpMyAdmin is a web-based tool that allows you to easily manage MySQL or MariaDB databases. You can perform administration tasks such as creating, editing, or deleting databases, import and export database backups, run searches and managing users and permissions using phpMyAdmin.

Features

  • Multi-language support.
  • Import data from CSV and SQL.
  • Provide live charts to monitor MySQL server activity like connections, processes, CPU/memory usage, etc.
  • Searching globally in a database or a subset of it.
  • Simple and easy to use web interface.
  • Export data to various formats like, CSV, SQL, XML, PDF, Word, Excel, LaTeX, and others.

In this tutorial, we will be going to explain how to install and secure phpMyAdmin on Ubuntu 18.04 server.

Requirements

    • A server running Ubuntu 18.04.
    • A non-root user with sudo privileges.

Install phpMyAdmin

Before starting, you will need to install Apache and MySQL to your server. You can install it with the following command:

sudo apt-get install apache2 mysql-server -y

By default, phpMyAdmin is not available in the Ubuntu 18.04 default repository. So, you will need to add the third party PHP repository to your system.

You can add it by just running the following command:

sudo apt-get install software-properties-common -y
sudo add-apt-repository ppa:ondrej/php

Once the repository is added, install phpMyAdmin by running the following command:

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

During the installation, you will be asked to choose the web server as shown below:

Install phpMyAdmin

Choose Apache and click on OK button. You will be redirected to the following page:

Configure database with dbconfig common

Now, click on Yes button. You should see the following page:

Set MySQL application password

Confirm password

Here, provide your MySQL application password for phpMyAdmin and click on the OK button.

Once the phpMyAdmin has been installed, enable the mbstring PHP extension with the following command:

sudo phpenmod mbstring

Next, restart Apache service to apply the changes:

sudo systemctl restart apache2

Configure User Authentication

phpMyAdmin automatically created a database user called phpmyadmin with the administrative password you set during the installation. But, it is recommended to create a separate user for managing databases through the phpMyAdmin web interface. In the latest version of MariaDB, the root MySQL user is set to authenticate using the auth_socket plugin by default. So, if you want to log in to phpMyAdmin as your root MySQL user, you will need to switch its authentication method from auth_socket to mysql_native_password.

First, log in to MySQL shell with the following command:

sudo mysql

Next, check the authentication method of MySQL user with the following command:

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

Output:

+------------------+-------------------------------------------+-----------------------+-----------+
| user             | authentication_string                     | plugin                | host      |
+------------------+-------------------------------------------+-----------------------+-----------+
| root             |                                           | auth_socket           | localhost |
| mysql.session    | *THISISNOTAVALIDPASSWORDTHATCANBEUSEDHERE | mysql_native_password | localhost |
| mysql.sys        | *THISISNOTAVALIDPASSWORDTHATCANBEUSEDHERE | mysql_native_password | localhost |
| debian-sys-maint | *AC18DF5CAE34BF4796EF975702F038A566B48B42 | mysql_native_password | localhost |
+------------------+-------------------------------------------+-----------------------+-----------+
4 rows in set (0.01 sec)

In the above output, you should see that root user uses auth_socket plugin.

You can configure the root account to authenticate with a password by running the following command:

mysql> ALTER USER 'root'@'localhost' IDENTIFIED WITH mysql_native_password BY 'password';

Next, flush the privileges with the following command:

mysql> FLUSH PRIVILEGES;

Now, check the authentication methods by running the following command:

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

Output:

+------------------+-------------------------------------------+-----------------------+-----------+
| user             | authentication_string                     | plugin                | host      |
+------------------+-------------------------------------------+-----------------------+-----------+
| root             | *2470C0C06DEE42FD1618BB99005ADCA2EC9D1E19 | mysql_native_password | localhost |
| mysql.session    | *THISISNOTAVALIDPASSWORDTHATCANBEUSEDHERE | mysql_native_password | localhost |
| mysql.sys        | *THISISNOTAVALIDPASSWORDTHATCANBEUSEDHERE | mysql_native_password | localhost |
| debian-sys-maint | *AC18DF5CAE34BF4796EF975702F038A566B48B42 | mysql_native_password | localhost |
+------------------+-------------------------------------------+-----------------------+-----------+
4 rows in set (0.01 sec)

Next, create a separate user with name phpmyadminuser to connect to phpMyAdmin.

First, log in to MySQL shell:

mysql -u root -p

Enter your root password, then create a user with the following command:

MariaDB [(none)]> CREATE USER 'phpmyadminuser'@'localhost' IDENTIFIED BY 'password';

Next, grant appropriate privileges to phpmyadminuser with the following command:

MariaDB [(none)]> GRANT ALL PRIVILEGES ON *.* TO 'phpmyadminuser'@'localhost' WITH GRANT OPTION;

Finally, exit from the MySQL shell with the following command:

MariaDB [(none)]> exit;

Now, open your web browser and type the URL http://your-server-ip/phpmyadmin. You will be redirected to the following page:

phpMyAdmin login

Here, provide your username and password. Then, click on the Go button. You should see the PhpMyAdmin dashboard in the following page:

phpMyAdmin dashboard

Secure PhpMyAdmin

phpMyAdmin is now installed and configured. But, it is recommended to secure your phpMyAdmin instance to prevent unauthorized access You can secure phpMyAdmin by using Apache’s built-in .htaccess authentication and authorization functionalities.

To do so, first enable the use of .htaccess file overrides by editing phpmyadmin.conf file:

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

Make the following changes:

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

Save and close the file. Then, restart Apache service to apply the chnages:

sudo systemctl restart apache2

Next, create a .htaccess file inside phpmyadmin directory:

sudo nano /usr/share/phpmyadmin/.htaccess

Add the following lines:

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

Save and close the file. Then, create a user named admin with the htpasswd utility:

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

Output:

New password: 
Re-type new password: 
Adding password for user admin

Your phpMyAdmin instance is now secured with an additional layer of authentication.

Now, open your web browser and type the URL http://your-server-ip/phpmyadmin. You will be redirected to the additional account name and password that you just configured as shown below:

phpMyAdmin secured with .htaccess

Now, provide your username and the password and click on the Log In button. You should be redirected to the regular phpMyAdmin authentication page.

Links

 

Source