Start Securing A MYSQL DATABASE
MySql is an open source relational database and it has high market share in the website database area. Many, if not all, Linux and *BSD distributions have it in their repositories, but some of these distributions install it with an empty root password by default. Other allow setting root password at installation time.
Mysql server is also incorporated in many hosting solution platforms like Lamp, Cpanel and Zpanel (the actual list is longer).
When MySql database is deployed in a shared hosting environment, it is important to know few key security aspects and make sure all are under tight control.
Below steps are recommended steps to be taken at initial mysql server deployment, before any database is restored or installed.
1. Overview
2. Secure and rename the root user account (very important)
3. Remove any anonymous accounts
4. Remove test database
5. Disable MySql history file
6. Disable remote acess to MySql server
1. Overview – Securing Mysql in shared hosting environment
Running MySql in a shared hosting server means that each hosting customer has his own mysql database with own username and password that have access to only his allocated database. It is very important to have a strong username and password generation policy for mysql credentials of your clients. Leaving them to chose these is a dangerous and uncommon practice.
To open up a CLI to mysql server, just use the shell command “mysql -u root -p” assuming you are the administrator and have the credentials of course.
$ mysql -u root -p
Enter password:
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 55967
Server version: 5.5.40-0+wheezy1 (Debian)
Copyright (c) 2000, 2014, Oracle and/or its affiliates. All rights reserved.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
mysql>How to display mysql connection information ?
To list the user for the current connection to MySql server the “status” cli command shows this and a little more.
mysql> status
--------------
mysql Ver 14.14 Distrib 5.5.40, for debian-linux-gnu (i686) using readline 6.2
Connection id: 55967
Current database:
Current user: root@localhost
SSL: Not in use
Current pager: stdout
Using outfile: ''
Using delimiter: ;
Server version: 5.5.40-0+wheezy1 (Debian)
Protocol version: 10
Connection: Localhost via UNIX socket
Server characterset: latin1
Db characterset: latin1
Client characterset: latin1
Conn. characterset: latin1
UNIX socket: /var/run/mysqld/mysqld.sock
Uptime: 9 days 13 hours 42 min 50 sec
Threads: 11 Questions: 909181 Slow queries: 0 Opens: 545 Flush tables: 1 Open tables: 205 Queries per second avg: 1.099
2. Secure and rename the root user account
One key MySql security aspect is the MySql administrator name, the root user. This is because any of your customers can construct a php script that will brute force connection to mysql as administrator. When a brute force attack, if the user is known, half of the attack is successful.
Without this step, the attacker would point the script towards the “root” user and try random or efficiently, non-repeating random strings from a list of characters and, sooner or later, the script will find the password (unless it is a few phrases long).
Of course, this is not easy for the attacker as these attempts will increase the CPU usage by the mysql server and will also raise alarms in mysql logs if configured to log failed logins, but for a quiet sleep, don’t skip this step.
Rename MySql root user
Mysql keeps user information in “mysql” database, table “user”.
mysql> use mysql;
mysql> update user set user=“n0roothere” where user=“root”;
mysql> flush privileges;
Change Mysql administrator password
The same goes for the password.
mysql> update user set password=password(’Some very long password with upper/lower/digit/#52352 characters’) where user=‘n0roothere’;
mysql> flush privileges;
The privileges flushing has to be performed to load the changes in mysql memory, otherwise they will not take effect until the mysql server is restarted.
3. Remove any anonymous accounts
Some MySql installation have anonymous users either by default or by mistake. These are empty usernames:
mysql> select user,host,password from user where user='';
+------+-----------+----------+
| user | host | password |
+------+-----------+----------+
| | localhost | |
| | server1 | |
+------+-----------+----------+
2 rows in set (0.00 sec)
Here is how to login with an anonymous MySql account:
# mysql -u -p
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
mysql>
mysql> show grants;
+--------------------------------------+
| Grants for @localhost |
+--------------------------------------+
| GRANT USAGE ON *.* TO ''@'localhost' |
+--------------------------------------+
1 row in set (0.11 sec)
As the output shows, anonymous accounts have only “usage” granted on the database, but still not a good idea to keep them around.
Remove anonymous mysql accounts
mysql> delete from user where user='';
Query OK, 2 rows affected (0.01 sec)
mysql> select user,host,password from user where user='';
Empty set (0.00 sec)
mysql> flush privileges;
4. Remove test database
Another security inconvenient is the default (again: in some installations) “test” database. It is an inconvenient because this special database allows access to all mysql users. So it can be exploited to write to it until the disk hosting the databases becomes full.
mysql> drop database test;
Query OK, 0 rows affected (0.30 sec)
5. Disable MySql history file
For the conveniency of the user, mysql client writes all executed commands to the Linux system’s user home directory. This file is called “.mysql_history” by default and it is a hidden file. This is dangerous practice because it will contain commands as well as passwords.
To get rid of this, link the /dev/null to this file (I found no way of doing this in any configuration file).
# rm -rf ~/.mysql_history
# ln -sf /dev/null ~/.mysql_history
6. Disable remote acess to MySql server
If the access to the mysql database has to be supported remotely, skip this test, but have an iptables filter that specifically drops on the INPUT chain traffic from any source except the intended hosts, to tcp port 3306.
Iptables rules to secure mysql
iptables -I INPUT -i eth0 -p tcp -m tcp —s 10.1.1.1/32 — —dport 3306 -j ACCEPT
iptables -A INPUT -i eth0 -p tcp -m tcp --dport 3306 -j DROP
The above two iptables rules work in a “allow interested traffic and deny rest” manner. The order of the rules is important. The second rule is not relevant if the chain default action is DROP.
Disable Mysql tcp network port
If remote access to the database server is not required, it’s a good practice to disable mysql from listening to TCP port 3306 connections. To do so, open my.cnf file in /etc/, /etc/mysql/ or the location holding it, under the [mysqld] section and add the following line:
skip-networkingFollowed by a restart of the service. Note that this will make the mysql server unaccessible from any remote hosts, only from localhost via the unix socket /var/tmp/mysql.sock.
These should make your mysql server more secure and your customers’ businesses in a safe environment.
You can actually try those database security steps on our platform in few minutes utilizing our PCS (Private Cloud Solution) which allows you to have VPSie(s) on a private network – NAT – Port forward – traffic control for inbound and outbound – multiple gateway IPs which you could use for the load-balancing and failover.
