Relational
database management systems such as MySQL and MariaDB are required for a significant portion of websites and applications. However, not all users are comfortable managing their data from the command line.
To solve this problem, a project called phpMyAdmin was created to offer an alternative in the form of a web-based administration interface. In this guide, we will demonstrate how to install and secure a phpMyAdmin configuration on a CentOS 7 server. We will build this configuration on top of the Apache web server, the world’s most popular web server.
Before
we begin, there are a few requirements that need to be resolved
.
To make sure you have a solid foundation on which to build this system, you should run our initial server setup guide for CentOS 7. Among other things, this will guide you through setting up a non-root user with sudo access for administrative commands.
The second prerequisite that needs to be met to get started with this guide is to install a LAMP stack (Linux, Apache, MariaDB, and PHP) on your CentOS 7 server. This is the platform we will use to serve our phpMyAdmin interface (MariaDB is also the database management software we want to manage). If you don’t already have a LAMP installation on your server, follow our tutorial on installing LAMP on CentOS 7.
When your server is in a healthy working state after following these guides, you can proceed to the rest of this page.
Step One – Install
phpMyAdmin
With our LAMP platform already in place, we can immediately start installing the phpMyAdmin software. Unfortunately, phpMyAdmin is not available in the default repository of CentOS 7.
To get the packages we need, we will have to add an additional repository to our system. The EPEL repository (E xtra Packages for Enterprise Linux) contains many additional packages, including the phpMyAdmin package we are looking for.
The EPEL repository can be made available to your server by installing a special package called epel-release. This will reconfigure your repository list and give you access to EPEL packages.
To install,
simply type
: sudo yum install epel-release
Now that the EPEL repository is configured, you can install the phpMyAdmin package using the yum packaging system by typing: sudo yum install phpmyadmin
The installation will now complete. The installation included an Apache configuration file that has already been launched. We will need to modify this a bit to make it work properly for our installation.
Open the file in your text editor now so we can make some changes:
sudo nano /etc/httpd/conf.d/phpMyAdmin.conf
Inside, we see some directory blocks with some conditional logic to explain the access policy for our directory. There are two distinct directories that are defined, and within these, configurations that will be valid for both Apache 2.2 and Apache 2.4 (which we are running).
Currently, this setting is configured to deny access to any connection that is not made from the server itself. Since we are working on our server remotely, we need to modify some lines to specify the IP address of your home connection.
Change the lines that say Require ip 127.0.0.1
or Allow from 127.0.0.1 to refer to the IP address of your home connection. If you need help finding the IP address of your home connection, see the next section. There should be four locations in the file that need to be changed:
. . . Require ip your_workstation_IP_address . . . Allow from your_workstation_IP_address . . . Require ip your_workstation_IP_address . . . Allow from your_workstation_IP_address . . .
When finished, restart the Apache web server to implement your modifications by typing:
sudo systemctl restart httpd.service
With that, our phpMyAdmin installation is now operational. To access the interface, go to your server’s domain name or public IP address followed by /phpMyAdmin, in your web browser:
http://server_domain_or_IP/phpMyAdmin
To log in, use a username/password pair of a valid MariaDB user. MariaDB’s root user and administrative password are a good place to get started. You will then be able
to access the administrative interface:
Find your
IP address You
will need to know the IP address of the computer you are using to access their databases to complete the above step. This is a security precaution so that unauthorized persons cannot connect to your server.
Note: This is not the IP address of your VPS, it is the IP address of your home or work computer.
You can find out how the web sees your IP address by visiting one of these sites in your web browser:
What is my IP address?
- What is my IP?
- My IP
address
Compare a few different sites and make sure they all give you the same value. Use this value in the configuration file above.
Step Two: Secure Your
phpMyAdmin Instance
The phpMyAdmin instance installed on our server should be fully usable at this point. However, by installing a web interface, we have exposed our MySQL system to the outside world.
Even with the included authentication screen, this is quite a problem. Due to the popularity of phpMyAdmin combined with the large amount of data it provides access to, installations like these are common targets for attackers.
We will implement two simple strategies to decrease the chances of our facility being targeted and compromised. We’ll change the interface location from /phpMyAdmin to something else to avoid some of the bot’s automated brute force attempts. We will also create an additional authentication gateway at the web server level that must be passed before reaching the phpMyAdmin login screen.
Changing
the application access location In order for our Apache web server to work with phpMyAdmin, our phpMyAdmin Apache configuration file
uses an alias to point to the directory location of the files. To
change the
URL where our phpMyAdmin interface can be accessed, we simply need to change the name of the alias. Open
the phpMyAdmin Apache configuration file now: sudo nano /etc/httpd/conf.d/phpMyAdmin.conf Towards the top of the
file, you will see two lines that look like this:
Alias /phpMyAdmin /usr/share/phpMyAdmin Alias /phpmyadmin /usr/share/phpMyAdmin
These two lines are our aliases, which means that if we access the domain name or IP address of our site, followed by /phpMyAdmin or /phpmyadmin, we will be served the content in /usr/share/phpMyAdmin.
We want to disable these specific aliases, as they are heavily targeted by bots and malicious users. Instead, we should decide on our own alias. It should be easy to remember, but not easy to guess. It should not state the purpose of the URL location. In our case, we’ll go with /nothingtosee.
To
apply our intended changes, we must delete or comment out the existing lines and add our own
: # Alias /phpMyAdmin /usr/share/phpMyAdmin # Alias /phpmyadmin /usr/share/phpMyAdmin Alias /nothingtosee /usr/share/phpMyAdmin
When finished, save and close the file
.
To implement the changes, restart the web
service: sudo systemctl restart httpd.service Now,
if you go to the previous location of your phpMyAdmin installation, you will get a 404 error
: http://server_domain_or_IP/phpMyAdmin
However, its phpMyAdmin interface will be available in the new location we selected:
http://server_domain_or_IP/nothingtosee
<img src="https://assets.digitalocean.com/articles/phpmyadmin_lamp_centos7/auth_gate.png" alt=
“phpMyAdmin login screen” /> Configuring a web server authentication
gateway
The next feature we wanted for our installation was an authentication prompt that a user would have to pass before seeing the phpMyAdmin login screen.
Fortunately, most web servers, including Apache, provide this capability natively. We will only need to modify our Apache configuration file to use an authorization file.
Open the phpMyAdmin
Apache configuration file in your text editor again:
sudo nano /etc/httpd/conf.d/phpMyAdmin.conf
Inside the /usr/share/phpMyAdmin directory block, but outside any of the blocks inside, we need to add an override directive. It will look like this:
. . . <Directory /usr/share/phpMyAdmin/> AllowOverride All <IfModule mod_authz_core.c> . . . . </Directory> . . .
This will allow us to specify additional configuration details in a file called .htaccess located within the phpMyAdmin directory itself. We will use this file to configure our password authentication.
Save and close the file when you are finished.
Restart the web service to implement this change:
sudo systemctl restart httpd.service
Create
an .htaccess
file Now that we have the override directive in our configuration, Apache will look for a file called .htaccess inside the /usr/share/phpMyAdmin directory. If it finds one, it will use the contained policies to supplement its previous configuration data.
Our next step is to create the .htaccess file inside that directory. Use your text editor to do it now
: sudo nano /usr/share/phpMyAdmin/.htaccess Within
this file, we need to enter the following information
: AuthType Basic AuthName “Administrator Login” AuthUserFile /etc/httpd/pma_pass Require valid user
Let’s review what each of these lines means
:
- AuthType Basic : This line specifies the type of authentication we 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 gain knowledge about what is being protected.
- AuthUserFile: Sets the location of the actual password file to be used for authentication. This should be outside the directories being served. We’ll create this file in a moment.
- 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 have finished entering this information, save and close
the file. Create the password file
for authentication
Now that we have specified the location of our password file by using the
AuthUserFile directive in our .htaccess file, we need to create and populate the password file.
This can be achieved by using an Apache utility called htpasswd. We invoke the command by passing it the location where we would like to create the file and the username for which we would like to enter the authentication details:
sudo htpasswd -c /etc/httpd/pma_pass
username The -c flag indicates that this will create an initial file. The directory location is the path and file name that will be used for the file. The username is the first user we would like to add. You will be prompted to enter and confirm a password for the user.
If you want to add additional users to authenticate, you can call the same command again without the -c flag and with a new username
: sudo htpasswd /etc/httpd/pma_pass seconduser With our password file
created, an authentication gateway has been implemented and we should now see a password prompt the next time we visit our site:
http://server_domain_or_IP/nothingtosee
Once you enter your credentials, you will be taken to the normal phpMyAdmin login page. This extra layer of protection will help keep your MySQL logs clean of authentication attempts, plus the added security benefit.
Conclusion
You can now manage your MySQL databases from a reasonably secure web interface. This user interface exposes most of the functionality that is available at the MySQL command prompt. You can view databases and schemas, run queries, and create new datasets and structures.