[Debian] How to setup mariadb and phpmyadmin the safe way (English) (Update Debian 9)

Before working with phpMyAdmin, you need to have some sort of SQL based Database installed on your server.

We are using mariadb

apt install mariadb-server

after u set a safe password for the user “root” we are done with the install.

so we are able to continue to the “phpMyAdmin” installation. (Maybe u need to install Apache2 before)

apt install phpmyadmin

During the installation, phpMyAdmin will walk you through a basic configuration. Once the process starts up, follow these steps:

  1. Select Apache2 for the server
  2. Choose YES when asked about whether to Configure the database for phpmyadmin with dbconfig-common
  3. Enter your MySQL password when prompted
  4. Enter the password that you want to use to log into phpmyadmin

After the installation phpmyadmin should have created his own phpmyadmin.conf in:

/etc/apache2/conf-available; /etc/apache2/conf-enabled

————————————————— EDIT —————————————————

Since Debian 9.0 there are some changes how to handle behind here.

First we need to set a password for user “root”

sudo mysql -u root
update mysql.user set password=password('password_here') where user='root';
flush privileges;

after that we need to disable the plugin “unix_socket” so phpmyadmin is able to connect to the server again only with password

update mysql.user set plugin='' where user='root';
flush privileges;

now when you enter the following command there should be no plugin for user root

select user,host,password,plugin from mysql.user;

like so:

+------+-----------+------------------------+-------------+
| user | host | password | plugin |
 +------+-----------+------------------------+-------------+
| root | localhost | *hashcode |  |
| root | e320 | *hashcode | unix_socket |
| root | 127.0.0.1 | *hashcode | unix_socket |
| root | ::1 | *hashcode | unix_socket |
+------+-----------+------------------------+-------------+

————————————————— EDIT —————————————————

and the interface should be available at http://your-host.com/phpmyadmin

Security

Unfortunately, older versions of phpMyAdmin have had serious security vulnerabilities, including allowing remote users to eventually exploit root on the underlying virtual private server. One can prevent a majority of these attacks through a simple process: locking down the entire directory with Apache’s native user/password restrictions which will prevent these remote users from even attempting to exploit older versions of phpMyAdmin.

Set Up the .htaccess File

To set this up, start off by allowing the .htaccess file to work within the phpmyadmin directory. You can accomplish this in the phpmyadmin configuration file:

nano /etc/phpmyadmin/apache.conf

Under the directory section, add the line “AllowOverride All” under “Directory Index”, making the section look like this:

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

Configure the .htaccess file

With the .htaccess file allowed, we can proceed to set up a native user whose login would be required to even access the phpmyadmin login page.

Start by creating the .htaccess page in the phpmyadmin directory:

nano /usr/share/phpmyadmin/.htaccess

Follow up by setting up the user authorization within .htaccess file. Copy and paste the following text in:

AuthType Basic 
AuthName "Restricted Area" 
AuthUserFile /path/to/passwords/.htpasswd 
Require valid-user

Create the htpasswd file

Now we will go ahead and create the valid user information.

Start by creating a htpasswd file. Use the htpasswd command, and place the file in a directory of your choice as long as it is not accessible from a browser. Although you can name the password file whatever you prefer, the convention is to name it .htpasswd.

htpasswd -c /path/to/passwords/.htpasswd username

A prompt will ask you to provide and confirm your password.
Once the username and passwords pair are saved you can see that the password is encrypted in the file.

Finish up by restarting apache:

service apache2 restart

Accessing phpMyAdmin

phpMyAdmin will now be much more secure since only authorized users will be able to reach the login page. Accessing youripaddress/phpmyadmin should display a screen like this.

Fill it in with the username and password that you generated. After you login you can access phpmyadmin with the MySQL username and password.