MySQL:Security


 * Home
 * Administration Tools
 * Backup and Restore
 * Security
 * User Account Management
 * Utility

Securing your database is perhaps the first and foremost task in ensuring its functionality and operation. Follow the steps below to lock down your server and database. More information on MySQL security can be found by the following URL

http://dev.mysql.com/doc/refman/5.0/en/security.html

Secure MySQL's Host Computer

 * Consider the safety of your server's physical location
 * Disable unnecessary services
 * Install antivirus and antispam software
 * Configure the operating system’s firewall
 * Install any patches and or software updates on your operating system

Change Your Root Name and Password
The default administrator username on the MySQL server is "root" also know as the superuser. Hackers often attempt to gain access to its permissions. To make this task harder, rename "root" and designate a strong password to the account.

To rename the administrator’s username, use the mysql utility to log in as the anonymous superuser "root" and use the rename user command.

Example:

mysql> rename user root to comptech;

Or the command update user does the same thing when applied to mysql.user database:

mysql> update mysql.user set user="comptech" where user="root";

mysql> flush privileges;

mysql>exit

Then log into the new anonymous superuser "comptech". To change a user’s account password, use the following mysql utility command:

Example:

mysql> set password 'username'@'%hostname' = password('mynewpass');

It is also possible to change the password using the "mysqladmin" utility:

Example:

C:\xampp\mysql\bin> mysqladmin -u username -p password mynewpass

Restrict Remote Access
Consider whether MySQL will be accessed from the network or only from its own server. If remote access is used, ensure that only defined hosts can access the server. To restrict MySQL from opening a network socket, use the following parameter should be added in the [mysqld] section of my.cnf or my.ini:

"skip-networking"

This line disables the initiation of networking during MySQL startup. Note that a local connection can still be established to the MySQL server.

The bind address, by default, in the my.ini file located in the bin folder of the mysql directory is set to the loopback address of "127.0.0.1". By changing the the bind address to the IP of the host computer you allow network access to the mysql instance running on that machine. If you don't want to allow network access to the mysql utility keep the bind-address as the loopback address in the default configuration.

"bind-address=127.0.0.1"

Enable Logging
It is recommended that you enable transaction logging, by adding the following line to [mysqld] section of the my.ini file:

[mysqld]

log =/var/log/my_new_logfile

This is not recommended in instances in which a production mysql server is heavily used because it can use much of your server's processing power. For small sized databases with few queries the transaction logging can be enabled without hurting the performance of your server.

In addition, verify that only the "root" and "mysql" have write access to the logfiles which can be done by using built in windows permissions.

MySQL log

Ensure only "root" and "mysql" have access to the logfile "*logfileXY". The file is stored in the mysql data directory.

Remove Old Unused Accounts
The MySQL database comes with some anonymous users with blank passwords. As a result, anyone can connect to the database To check whether this is the case, do the following:

mysql> select * from mysql.user where user="";

If your system is secure you should get no output from the commands above or below.

mysql> show grants for ''@'localhost'; mysql> show grants for ''@'myhost';

If any grants exist, then anybody can access the database and use the "test" database. Check this try logging in with any username as seen below.

C:\xampp\mysql\bin> mysql -u helloworld

To remove the account, run the following command:

mysql>drop user "";

or

mysql> use mysql;

mysql> delete from user where user="";

mysql> flush privileges;

Remove the "test" Database
MySQL creates a "test" database. It can be accessed by the anonymous user, and is therefore used by numerous attacks. To remove this database, use the drop database command as follows:

mysql> drop database test;

Or you can drop through the "mysqladmin" utility using the command below.

C:\xampp\mysql\bin> mysqladmin -u username -p drop test

Lower your System Privileges
A common database security recommendation is to lower the permissions given to various parties. Typically, when developers work, they use the system's maximum permission and give less consideration to permission principles than we might expect. This can expose the database to significant risk.

To protect your database, make sure that the file directory in which the MySQL database is actually stored is owned by the user "mysql" and the group "mysql".

The mysql files, which reside under the /usr/bin/ directory, should be owned by "root" or the specific system "mysql" user. Other users should not have write access to these files.

Lower the Database Privileges
A good rule of thumb is that only administrator accounts need to be granted the SUPER / PROCESS / FILE privileges and access to the mysql database. Check the privileges of the rest of the users and ensure that these are set appropriately. This can be done using the following steps.

mysql> use mysql;

To Identify users

mysql> select * from user;

List grants of all users

mysql> show grants for ‘root’@’localhost’;

Run the command above for each user to see what the privileges are.

By default the show databases command is given to any user that has access to the mysql utility. This can be concerning because the show databases command can be used by an attacker to gather information and data about your databases.


 * Add " --skip-show-database" to the startup script of MySQL or add it to the MySQL configuration file
 * Grant the show databases privilege only to the users you want to use this command

To disable the usage of the show databases command, the following parameter should be added in the [mysqld] section of the my.ini:

[mysqld] skip-show-database

Disable the Use of the "LOCAL INFILE"
Disable the use of the LOAD DATA LOCAL INFILE command, which will help to prevent unauthorized reading from local files. This is especially important when new SQL Injection vulnerabilities in PHP applications are found. The LOCAL INFILE command can be used to gain access to other files on the operating system, for instance "/etc/passwd" by using the following command:

mysql> load data local infile '/etc/passwd' into table table_XYZ

Or

mysql> select load_file("/etc/passwd")

To disable the LOCAL INFILE command, the following parameter should be added in the [mysqld] section of the mysql.ini configuration file.

set-variable=local-infile=0