Securing MySql on your web server


Securing MySql when running on your web server.

We are securing the Mysql root account. This account can manipulate and delete all other databases and Mysql users on the system and should never be used as a web application user.

Your web application will connect from ‘localhost’ via PHP and should never be configured to do anything else in this environment where Mysql and Apache are on the same system.

Mysql only needs to allow connections from localhost (itself) and not listen for connections from the world.

To achieve this edit the /etc/my.cnf file. Adding this one line:

[mysqld]
 bind-address = 127.0.0.1
 Restart Mysql

Restart Mysql to make this active

/etc/init.d/mysqld restart

Mysql will no longer detectable as an open or closed port. This means that the port is not responding at all to the probe. Great, but we need to keep going.

Set the “root” password for Mysql.

This password will only ever be used by the “root” user for database maintenance and setting up additional databases for web applications.

We are going to set a very long and complex password for the root user but as root you will never need to type it at the command line.

MySql access with apparently no password.

Generate a 256 character random password. I use this method because you have the tools to generate it already on your CentOS 6.5 system without installing more packages.

dd if=/dev/urandom bs=1 count=256 2>/dev/null | base64 -w 0 | rev | cut -b 2- | rev

My Result,. Yours will be different. Copy and paste (you don’t need extra software. Secret Linux cut ‘n’ paste) this password being careful to get all the characters. We could use mysql-init here but this is probably faster for a one off install.

Lf6axaorttmYPtP7CSNo0zkZsCjupxQ72HFJ1s8KOZNLI3CTAetGCDk85SaWus125TlnaCEwOEXFwlhcqrwZsRjjIcEYoK9jzKGIrh13vwKS0Es6IiAbip/z5G2H9LyBqmqWYcqyTl+yb8Wq+cag7cGLpGsKLX3SGuMjL+wy4hw1Jhq5pBbPEOUYkUyyGMLI205clERd4YQmstLi7Dlh0qPuMjtRwVK7iUfx++P4B1B+GXkCpfOGPCb6C2wbzbhhV9ENuWzCPOVCa4c++6dvce36QkxehBRyHv7uuKXEVzI8xo5bu2EhmWxWDsBYbTel4U81Ikm6z867VmwT7bIsiA=

Stop mysql and issue the commands below substituting the blue password for the one you generated.

service mysqld stop service mysqld_safe --skip-grant-tables & mysql -u root set password=password('Lf6axao<cut for example> 7bIsiA= '); Notice the (' around the password ') flush privileges; quit service mysqld stop service mysql start

We are really not going to want to type a 256 character password  every time we want to log in to mysql as root so we will save the password in the root .my.cnf file and set permissions so that only root can use it.

Setup the /root/.my.cnf file to automatically feed this password into mysql for the root user. Make sure that the password is all on one line in the file.

Create file:
touch /root/.my.cnf 
Contents of file.
[client]
user=root
password=Lf6axao<cut for example>7bIsiA

Now when you type “mysql” at the command prompt as root you will be logged in with no password.

What is actually happening is that mysql is reading the /etc/.my.cnf file for root and using that password to login.

Make sure only root can use this file.

 chmod 640 /root/.my.cnf

Type mysql at the command line and you should be logged in with no password.

Setting up a new databases for a web applications.

Never use the “root” credentials for application databases. We will make a new database with a new user and password for every application that requires a database.
As an example we will use the credentials below:

database name = testdatabase
database user = testuser
database password = your_password

mysqladmin create testdatabase

 > GRANT ALL ON *.testdatabase TO testuser@localhost IDENTIFIED BY 'your_password';
 \q

We just allowed the the user “testuser” to connect from ‘localhost’ to do anything it likes with that database using the password ‘your_password’. Set a good password and write it down since you are going to need it to install your web application. Normally it goes in config.php or is in-putted through a web interface.

If the database is hacked then at least only THIS database will be compromised the ‘testuser’ has no access to any other databases.