How to Secure Mysql server on Cpanel

Mysql is one of most popular database server widely used on Linux operating system.  Its very robust and offers many features to support today’s database needs for all kind of applications, whether its related with Joomla based content management system, WordPress sites, Magento application, E-commerce based applications and many more, they all work great with Mysql server. Being integrated with Cpanel its makes more easier to manage mysql databases with ease.

Today I am going to discuss few points on how to secure your mysql server on cpanel based server on production based environment.

Secure Mysql server in easy way

1.) General Security

a.) While security is the most important thing to be applied its just not only with Mysql server, It is necessary to consider securing the entire host against all types of attacks.

b.) On Cpanel server mysql is installed by default while cpanel installation and the root password is set to a random string. Try mysql -u root from bash shell. If you are able to connect the mysql console without being asked for a password, anyone can connect to your MySQL server as the MySQL root user with full privileges. It happens because the password is stored in hidden file located under /root/.my.cnf file. Note down the mysql root password and remove the entries as shown below.

[client]

password=”mysqlrootpassword”
user=root

c.) General password tip, Even if you want to reset the mysql root password do not use simple plain text passwords. Always use strict passwords. Use the password generator option as shown below.

 

 

 

 

 

 

 

 

d.) Install good firewall like CSF and make sure mysql is running behind the firewall. By default mysql runs on localhost or 127.0.0.1 local loop back ip.

Password Policies

Keeping passwords secure is best thing to do and to avoid many common problems.  MySQL stores passwords for all user accounts in the mysql.user table and access to this table should never be granted to any non administrative user.  Also make sure that there are no users without password in the mysql.user table as its very insecure.

For cPanel & WHM , you can set password strength policies for mysql database user as well.  This will allow the users to create strong passwords by default.

(Home >> Security Center >> Password Strength Configuration) The recommended Default Required Password Strength to 40.

System Variables that Affect Security

local-infile  is a global scope variable and if this variable is disabled, clients cannot use LOCAL in LOAD DATA statements. There are potential security issues with the LOAD DATA statement, it can load a file that is located on the server host or it can load a file located on the client host.   The command should therefore be disabled by inserting set-variable=local-infile=0 to the [mysqld] section of my.cnf.

skip-name-resolve is a global scope variable and will not resolve hostname when checking client connections. Well its an optional thing and can help to improve performance by disabling DNS lookups if you have slow DNS.  Just add –skip-name-resolve to the [mysqld] section of my.cnf.

skip-show-database  is a global scope variable and controls who is permitted to use the SHOW database statement.  Denying remote attackers of their information gathering capabilities is critical to security posture. For this reason, the SHOW DATABASES command should be removed entirely by adding skip-show-database to the [mysqld] section of my.cnf.

2.)  Secure Mysql  Server  

Since we are talking about Cpanel. cPanel comes with a script known as “securemysql” located in scripts. This script can help to secure the cPanel server’s MySQL configuration with numerous commands. Firstly, the script makes sure the cPanel MySQL root password is set. In addition, it always makes sure that /var/lib/mysql, which is the MySQL database data directory is correctly owned by user mysql so it cannot be read by rogue users or insecure compromised PHP scripts.

To get an idea of the usage of the script, simply run with the h flag to query it for informational output:

 

 

 

 

 

 

 

 

 

 

 

The above example image show common usages for the scripts, including: removing anonymous users, removing test databases, removing the ability to remotely login as root, even fixing insecure Horde webmail SQL permissions.

A test example image given below would remove any Anonymous SQL users, remove test databases, remove the global lock tables, create tmp table privileges from users, remove insecure horde logins and users with blank passwords, and finally remove the ability to login to MySQL as root remotely. This is the most common usage for the script, also the quiet flag has been removed to show the work performed:

 

 

 

 

If you have doubts about your MySQL configuration, or have had an administrator not familiar with the cPanel MySQL permission schema, it’s first suggested to backup the entire /var/lib/mysql directory (all of the database data) this can be done easily with the Backup configuration option in WHM by selecting to backup the entire mysql directory.  Once the backup is completed you can run the securemysql script to remove the possibility of your system being compromised due to anonymous users, weak or missing root privileges, or worse the ability to login as the root MySQL user remotely from outside the server.

Conclusion:  The above article is solely for the purpose of securing mysql in what I think is fine with tried and tested settings. I think the above measures are good enough to run mysql in secure environment. I have read many other articles on this and found many suggestions like changing the mysql root username to something more secure , disabling remote logins etc.. surely you can apply all these but only looking  that it won’t have any reverse effect on your clients.