How To Install And Secure PhpMyAdmin On Ubuntu 20.04

Step 2 — Adjusting User Authentication and Privileges

When you installed phpMyAdmin onto your server, it automatically created a database user called phpmyadmin which performs certain underlying processes for the program. Rather than logging in as this user with the administrative password you set during installation, it’s recommended that you log in as either your root MySQL user or as a user dedicated to managing databases through the phpMyAdmin interface.

Configuring Password Access for the MySQL Root Account

In Ubuntu systems running MySQL 5.7 (and later versions), the root MySQL user is set to authenticate using the auth_socket plugin by default rather than with a password. This allows for some greater security and usability in many cases, but it can also complicate things when you need to allow an external program like phpMyAdmin to access the user.

In order to log in to phpMyAdmin as your root MySQL user, you will need to switch its authentication method from auth_socket to one that makes use of a password, if you haven’t already done so. To do this, open up the MySQL prompt from your terminal:

1. sudo mysql

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

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

Output+------------------+-------------------------------------------+-----------------------+-----------+ | user | authentication_string | plugin | host | +------------------+-------------------------------------------+-----------------------+-----------+ | root | | auth_socket | localhost | | mysql.session | *THISISNOTAVALIDPASSWORDTHATCANBEUSEDHERE | caching_sha2_password | localhost | | mysql.sys | *THISISNOTAVALIDPASSWORDTHATCANBEUSEDHERE | caching_sha2_password | localhost | | debian-sys-maint | *8486437DE5F65ADC4A4B001CA591363B64746D4C | caching_sha2_password | localhost | | phpmyadmin | *5FD2B7524254B7F81B32873B1EA6D681503A5CA9 | caching_sha2_password | localhost | +------------------+-------------------------------------------+-----------------------+-----------+ 5 rows in set (0.00 sec)

In this example, you can see that the root user does in fact authenticate using the auth_socket plugin. To configure the root account to authenticate with a password, run the following ALTER USER command. Be sure to change password to a strong password of your choosing:

1. ALTER USER 'root'@'localhost' IDENTIFIED WITH caching_sha2_password BY 'password';

Note: The previous ALTER USER statement sets the root MySQL user to authenticate with the caching_sha2_password plugin. Per the official MySQL documentation, caching_sha2_password is MySQL’s preferred authentication plugin, as it provides more secure password encryption than the older, but still widely used, mysql_native_password.

However, some versions of PHP don’t work reliably with caching_sha2_password. PHP has reported that this issue was fixed as of PHP 7.4, but if you encounter an error when trying to log in to phpMyAdmin later on, you may want to set root to authenticate with mysql_native_password instead:

1. ALTER USER 'root'@'localhost' IDENTIFIED WITH mysql_native_password BY 'password';

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

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

Output+------------------+-------------------------------------------+-----------------------+-----------+ | user | authentication_string | plugin | host | +------------------+-------------------------------------------+-----------------------+-----------+ | root | *DE06E242B88EFB1FE4B5083587C260BACB2A6158 | caching_sha2_password | localhost | | mysql.session | *THISISNOTAVALIDPASSWORDTHATCANBEUSEDHERE | caching_sha2_password | localhost | | mysql.sys | *THISISNOTAVALIDPASSWORDTHATCANBEUSEDHERE | caching_sha2_password | localhost | | debian-sys-maint | *8486437DE5F65ADC4A4B001CA591363B64746D4C | caching_sha2_password | localhost | | phpmyadmin | *5FD2B7524254B7F81B32873B1EA6D681503A5CA9 | caching_sha2_password | localhost | +------------------+-------------------------------------------+-----------------------+-----------+ 5 rows in set (0.00 sec)

You can see from this output that the root user will authenticate using a password. You can now log in to the phpMyAdmin interface as your root user with the password you’ve set for it here.

Configuring Password Access for a Dedicated MySQL User

Alternatively, some may find that it better suits their workflow to connect to phpMyAdmin with a dedicated user. You can create a user that connects either locally or remotely. For details on how to allow remote connection access to MySQL, you’ll need to configure MySQL to accept connections from other IP addresses. To start mysql locally, open up the MySQL shell once again:

1. 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 in order to connect:

1. mysql -u root -p

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

1. CREATE USER 'sammy'@'localhost' IDENTIFIED WITH caching_sha2_password BY 'password';

Note: Again, depending on what version of PHP you have installed, you may want to set your new user to authenticate with mysql_native_password instead of caching_sha2_password:

1. ALTER USER 'sammy'@'localhost' IDENTIFIED WITH mysql_native_password BY 'password';

Then, grant your new user appropriate privileges. For example, you could grant the user privileges to all tables within the database, as well as the power to add, change, and remove user privileges, with this command (see our guides on creating MySQL users and granting permissions and managing PostgreSQL roles and permissions for more details):

1. GRANT ALL PRIVILEGES ON *.* TO 'sammy'@'localhost' WITH GRANT OPTION;

Following that, exit the MySQL shell:

1. 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

Log in to the interface, either as root or with the new username and password you just configured.

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

Now that you’re able to connect and interact with phpMyAdmin, all that’s left to do is harden your system’s security to protect it from attackers.