Disable or 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. This is typically done through TCP wrappers, iptables, or any other firewall software or hardware available on the market. To restrict MySQL from opening a network socket, the following parameter should be added in the[mysqld] section of my.cnf or my.ini:
The file is located in the “C:\Program Files\MySQL\MySQL Server 5.1” directory on the Windows operating system or “/etc/my.cnf” or “/etc/mysql/my.cnf” on Linux. This line disables the initiation of networking during MySQL startup. Please note that a local connection can still be established to the MySQL server. Another possible solution is to force MySQL to listen only to the localhost by adding the following line in the [mysqld] section of my.cnf
You may not be willing to disable network access to your database server if users in your organization connect to the server from their machines or the web server installed on a different machine. In that case, the following restrictive grant syntax should be considered:
mysql> GRANT SELECT, INSERT ON mydb.* TO 'someuser'@'somehost';
Disable the use of LOCAL INFILE
The next change is to 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. In addition, in certain cases, the “LOCAL INFILE” command can be used to gain access to other files on the operating system, for instance “/etc/passwd”, using the following command:
mysql> LOAD DATA LOCAL INFILE '/etc/passwd' INTO TABLE table1
Or even simpler:
mysql> SELECT load_file("/etc/passwd")
To disable the usage of the “LOCAL INFILE” command, the following parameter should be added in the [mysqld] section of the MySQL configuration file.
Change root username and password
The default administrator username on the MySQL server is “root”. Hackers often attempt to gain access to its permissions. To make this task harder, rename “root” to something else and provide it with a long, complex alphanumeric password. To rename the administrator’s username, use the rename command in the MySQL console:
mysql> RENAME USER root TO new_user;
The MySQL “RENAME USER” command first appeared in MySQL version 5.0.2. If you use an older version of MySQL, you can use other commands to rename a user:
mysql> use mysql; mysql> update user set user="new_user" where user="root"; mysql> flush privileges;
To change a user’s password, use the following command-line command:
mysql> SET PASSWORD FOR 'username'@'%hostname' = PASSWORD('newpass');
It is also possible to change the password using the “mysqladmin” utility:
shell> mysqladmin -u username -p password newpass
Remove the “test” database
MySQL comes with a “test” database intended as a test space. It can be accessed by the anonymous user, and is therefore used by numerous attacks. To remove this database, use the drop command as follows:
mysql> drop database test; Or use the "mysqladmin" command: shell> mysqladmin -u username -p drop test
Remove Anonymous and obsolete 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="";
In a secure system, no lines should be echoed back. Another way to do the same:
mysql> SHOW GRANTS FOR ''@'localhost'; mysql> SHOW GRANTS FOR ''@'myhost';
If the grants exist, then anybody can access the database and at least use the default database”test”. Check this with:
shell> mysql -u blablabla
To remove the account, execute the following command:
mysql> DROP USER "";
The MySQL “DROP USER” command is supported starting with MySQL version 5.0. If you use an older version of MySQL, you can remove the account as follows:
mysql> use mysql; mysql> DELETE FROM user WHERE user=""; mysql> flush privileges;
Lower database privileges
Operating system permissions were fixed in the preceding section. Now let’s talk about database permissions. In most cases, there is an administrator user (the renamed “root”) and one or more actual users who coexist in the database. Usually, the “root” has nothing to do with the data in the database; instead, it is used to maintain the server and its tables, to give and revoke permissions, etc. On the other hand, some user ids are used to access the data, such as the user id assigned to the web server to execute “select\update\insert\delete” queries and to execute stored procedures. In most cases, no other users are necessary; however, only you, as a system administrator can really know your application’s needs. Only administrator accounts need to be granted the SUPER / PROCESS /FILE privileges and access to the mysql database. Usually, it is a good idea to lower the administrator’s permissions for accessing the data. Review 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; [Identify users] mysql> select * from users; [List grants of all users] mysql> show grants for ‘root’@’localhost’;
The above statement has to be executed for each user ! Note that only users who really need root privileges should be granted them. Another interesting privilege is “SHOW DATABASES”. By default, the command can be used by everyone having access to the MySQL prompt. They can use it to gather information (e.g., getting database names) before attacking the database by, for instance, stealing the data. To prevent this, it is recommended that you follow the procedures described below.
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 /etc/my.cnf:
If your database server does not execute many queries, it is recommended that you enable transaction logging, by adding the following line to [mysqld] section of the /etc/my.cnf file:
[mysqld] log =/var/log/mylogfile
This is not recommended for heavy production MySQL servers because it causes high overhead on the server. In addition, verify that only the “root” and “mysql” ids have access to these logfiles (at least write access). Error log Ensure only “root” and “mysql” have access to the logfile “hostname.err”. The file is stored in the mysql data directory. This file contains very sensitive information such as passwords, addresses, table names, stored procedure names and code parts. It can be used for information gathering, and in some cases, can provide the attacker with the information needed to exploit the database, the machine on which the database is installed, or the data inside it. MySQL log Ensure only “root” and “mysql” have access to the logfile “*logfileXY”. The file is stored in the mysql data directory.
Set max_connections to the number of concurrent connections you need. The default value is only 100 connections, which is very small. Note: connections take memory and your OS might not be able to handle a lot of connections. MySQL binaries for Linux/x86 allow you to have up to 4096 concurrent connections, but self compiled binaries often have less of a limit. Set table_cache to match the number of your open tables and concurrent connections. Watch the open_tables value and if it is growing quickly you will need to increase its size. Note: The 2 previous parameters may require a lot of open files. 20+max_connections+table_cache*2 is a good estimate for what you need. MySQL on Linux has an open_file_limit option to set this limit. If you have complex queries sort_buffer_size and tmp_table_size are likely to be very important. Values will depend on the query complexity and available resources, but 4Mb and 32Mb, respectively are recommended starting points. Note: These are “per connection” values, among read_buffer_size, read_rnd_buffer_size and some others, meaning that this value might be needed for each connection. So, consider your load and available resource when setting these parameters. For example sort_buffer_size is allocated only if MySQL nees to do a sort. Note: be careful not to run out of memory. If you have many connects established (i.e. a web site without persistent connections) you might improve performance by setting thread_cache_size to a non-zero value. 16 is good value to start with. Increase the value until your threads_created do not grow very quickly.
Index the Search Fields
Indexes are not just for the primary keys or the unique keys. If there are any columns in your table that you will search by, you should almost always index them. As you can see, this rule also applies on a partial string search like “last_name LIKE ‘a%’”. When searching from the beginning of the string, MySQL is able to utilize the index on that column. You should also understand which kinds of searches can not use the regular indexes. For instance, when searching for a word (e.g. “WHERE post_content LIKE ‘%apple%’”), you will not see a benefit from a normal index. You will be better off using mysql fulltext search or building your own indexing solution.
Enable the slow query log
MySQL prior to 5.1.0 requires a change to the MySQL my.cnf file and a restart in order to log slow queries; from MySQL 5.1.0 you can change this dynamically without having to restart. To make the change permanent whenever the MySQL server is started, and for MySQL prior 5.1.0, edit your my.cnf file (on Linux boxes this is usually somewhere like /etc/my.cnf or /etc/mysql/my.cnf) and uncomment the “log_slow_queries” line or add it if it’s not present. On a Debian box, for example, the line to uncomment looks like so:
log_slow_queries = /var/log/mysql/mysql-slow.log
You can change the log file name to something else or leave it blank so it uses the default. The default is to log the queries into a file in the MySQL data directory. On my Debian test box this was “mysqld-slow.log”. To enable or disable the setting dynamically in MySQL 5.1.0 run the following query to enable it:
set global log_slow_queries = ON;
and to disable it:
set global log_slow_queries = OFF;
Setting the long query time You can also specify how long a quey needs to run for before it is logged with the “long_query_time” setting. By default this is 10 seconds. In the my.cnf file, to change it to e.g. 5 seconds add the following:
long_query_time = 5
This can be changed dynamically in MySQL 5.0.0+ (and possibly earlier versions) by running the following query:
set global long_query_time = 5;
This will only work for new connections; any connections which have already been established will continue to use the old setting. Once the user disconnects and reconnects their new connection will use the new setting.