MySQL Hardening on Production Servers

TABLE OF CONTENTS

1.     Run MySql under other user id and gid.

2.     Protect root account of MySql with strong password.

3.     Prevent anonymous access to MySql

4.     Regulate PROCESS , SUPER privilege to administrative users

5.     Regulate the File privilege only to administrative users

6.     Start up MySQL with the parameter to disable the use of symlinks

7.     Disable unauthorized reading from local files

8.     Rename the administrator account

9.     Enable logging in mysql

10.       Review list of privileges granted to users

11.       Remove sample preinstalled databases

12.       Disable TCP networking from MySQL

13.       Restrict connections for a single user

14.       MySQL should be run on a non default port

 

1.    Run MySql under other user id and gid

Description:

The MySQL server should not run as root. It should run under the privileges of a normal user.

Impact:

An unauthorized user might discover a buffer overflow in the MySQL server and exploit it. If MySQL is running as root, the unauthorized user, on exploiting the vulnerability will gain root access on the system and hence complete control of the system.

Solution:

A separate user by the name “mysql” must be created. While running the MySQL server it should be started under the privileges of the mysql user. Hence even if an attacker manages to compromise the running version of MySql he will gain only the privileges of the mysql user and not root. The exact flow would be as follows:

Also make sure that the low privileged user has permissions to write into the MySQL program directory (Directory in which MySQL is installed).

2.    Protect root account of MySql with strong password

Description:

There are default root user accounts which are created when MySQL is initially installed. These user accounts have no password by default. A strong password must be set.

Impact:

An unauthorized user who discovers that a MySQL server is running can directly log in to the server without entering a password.

Solution:

Set a strong password for the MySQL root account as follows:

shell> mysql -u root

mysql> SET PASSWORD FOR ‘root’@’localhost’ = PASSWORD(‘newpwd’);

mysql> SET PASSWORD FOR ‘root’@’127.0.0.1’ = PASSWORD(‘newpwd’);

mysql> SET PASSWORD FOR ‘root’@’production’ = PASSWORD(‘newpwd’);

 

3.    Prevent anonymous access to MySql

Description:

There are default anonymous user accounts which are created when MySQL is initially installed. These user accounts have no password by default. A strong password must be set or these accounts removed.

Impact:

An unauthorized user who discovers that a MySQL server is running can directly log in to the server without entering a password.

Solution:

Remove the anonymous account or set a strong password for it:

Setting a password:

shell> mysql -u root

mysql> SET PASSWORD FOR ”@’localhost’ = PASSWORD(‘newpwd’);

mysql> SET PASSWORD FOR ”@’host_name’ = PASSWORD(‘newpwd’);

Remove the anonymous account:

shell> mysql -u root

mysql> DELETE FROM mysql.user WHERE User = ”;

mysql> FLUSH PRIVILEGES;

4.    Regulate PROCESS , SUPER privilege to administrative users

Description:

The PROCESS privilege can be used to view the plain text of currently executing statements, including statements that set or change passwords. The SUPER privilege can be used to terminate other clients or change how the server operates.

Impact:

An attacker might be able to see statements issued by other users such as UPDATE user SET password=PASSWORD(‘not_secure’).

Solution:

While logged in to the database as root revoke privileges as follows:

mysql> revoke process on *.* from user;

mysql> revoke super on *.* from user;

NOTE: The changes take effect once the user logs out of his current session.

5.    Regulate the File privilege only to administrative users

Description:

Any user that has this privilege can write a file anywhere in the filesystem with the privileges of the mysqld daemon. The FILE privilege may also be used to read any file that is world-readable or accessible to the Unix user that the server runs as.

Impact:

An attacker can load sensitive configuration files into the database and then view its contents using a select query.

Solution:

Check whether the FILE privilege has been granted to non root users. Revoke these privileges if necessary. While logged in to the database as root revoke privileges as follows:

revoke file on *.* from username;

 

6.    Start up MySQL with the parameter to disable the use of symlinks

Description:

The MySQL database starts up by default permitting the usage of symlinks. When you drop a table that is using symlinks, both the symlink and the file to which the symlink points are dropped.

Impact:

An unauthorized user can create symlinks within a table that point to key configuration files and then drop the table. Any user who has write access to the data directory can delete a symlink and hence an entire database.

Solution:

Symbolic links are enabled by default for all Windows servers. Remove any .sym files in the data directory.

Disable the usage of symbolic links by starting MySQL with the –skip-symbolic-links option. Note that any applications that use symbolic links to access databases might not work if symlinks are removed. In the my.ini file set the skip-symbolic-links parameter in the [mysqld] section and restart mysql.

 

7.    Disable unauthorized reading from local files

Description:

The LOAD DATA statement can load a file that is located on the server host, or it can load a file that is located on the client host when the LOCAL keyword is specified.

Impact:

A patched server could be built that would tell the client program to transfer a file of the server’s choosing rather than the file named by the client in the LOAD DATA statement. Such a server could access any file on the client host to which the client user has read access. If a webserver is the client connecting to a database the database can gain access to all files which the webserver process has read access to.

Solution:

Start MySQL with the –local-infile=0 option to disable LOAD DATA LOCAL commands from the server side. In the my.ini file set the disable-local-infile parameter in the [mysqld] section and restart mysql.

 

8.    Rename the administrator account

Description:

The administrator account in MySQL is called root. This must be renamed to something different.

Impact:

Malicious user can try to compromise the system using root account. The effort involved in trying to brute force a MySQL password is reduced because the attacker already knows the username.

Solution:

Rename the root account to something else using the following query.

mysql> update user set user=”mydbadmin” where user=”root”;

mysql> flush privileges

Logout and login to mysql using mydbadmin to test the same. It will not be possible to login to mysql using root here on.

9.    Enable logging in mysql

Description:

Enabling logging will help administrators to monitor critical events in a MySQL server.

Impact:

Malicious activities will not be detected. Early warning towards attempts at malicious access will go undetected.

Solution:

Configure logging on the MySQL database by using the –log-error, log or –log-bin keywords while starting the MySQL daemon. The syntax for each of the types of logging is mentioned in the table below:

 

Policy	KEYWORD
Error Log	log-error=filename(absolute path)
General Query log	log=filename(absolute path)
Binary log	log-bin=filename(absolute path)

 

Set the following variables in the my.ini file and restart MySQL.

log= C:\mysql\data\filename.err

log-bin=C:\mysql\data\binaryfilename.err

 

 

10. Review list of privileges granted to users

Description:

All privileges in the database can be granted to users. There are a number of privileges that may be unnecessary for all users. Such privileges must be revoked.

Impact:

Non-privileged users granted unnecessary privileges can perform administrative tasks and can compromise the database.

Solution:

The following key privileges are present by default in MySQL.

PRIVILEGE	COLUMN
CREATE	Create_priv
DROP	Drop_priv
GRANT OPTION	Grant_priv
ALTER	Alter_priv
DELETE	Delete_priv
INSERT	Insert_priv
UPDATE	Update_priv
SHUTDOWN	Shutdown_priv
RELOAD	Reload_priv
SHOW DATABASES	Show_db_priv

A list of the users who have access to these privileges must be reviewed; those privileges which are not needed must be revoked.

revoke <privilege> on *.* from username;

NOTE: <privilege> can be one of the following: create, drop, grant, alter, delete, insert, update, shutdown, reload, show_db

11.  Remove sample preinstalled databases

Description:

There is a default database that comes preinstalled with MySQL. This database should be removed if not required.

Impact:

An unauthorized user can access sensitive information stored in this database.

Solution:

While logged in to the MySQL server run the command show databases;. If such databases exist then remove the preinstalled database “test” if it is not required. It can be removed as follows:

mysql> drop database test;

 

12. Disable TCP networking from MySQL

Description:

The MySQL database listens on port 3306 by default. If the database is going to be used only by locally installed applications, we can disable listening on that port. This will limit possibilities of attacking the MySQL database by direct TCP/IP connections from other hosts.

Impact:

An unauthorized user can connect to the MySQL database remotely and try and obtain sensitive information.

Solution:

Set the bind-address=127.0.0.1 option while starting the MySQL server thus limiting all remote connections. MySQL can only be accessed by a user at the console of the MySQL server.

NOTE: This might not be a practical solution incase there is a development team which accesses this server as part of their daily work. In such a case network level access control must be used restricting access to the database server only to a few specific IP addresses.

13. Restrict connections for a single user

Description:

It is possible to limit the following server resources for individual accounts:

The number of queries that an account can issue per hour

The number of updates that an account can issue per hour

The number of times an account can connect to the server per hour

Impact:

An unauthorized user can issue any number of connections, queries and updates to the database.

Solution:

The maximum number of connections that can be set to the MySQL server can be set by setting the max_connections system variable to a non-zero value. Set the max_connections variable after assessing the requirements of the organization and the maximum number of simultaneous connections required by clients to the database. Comment out the max_connections variable from the my.ini file and set it to the desired value as follows in the [mysqld] section:

max_connections=<desired value>

14. MySQL should be run on a non default port

Description:

By default MySQL runs on port 3306. The configuration should be changed to make MySQL run on some other port.

Impact:

The port 3306 will be detected as open by a port scan initiated by an attacker. Using this information an attacker can concentrate on gaining more SQL specific information by fingerprinting the server.

Solution:

The MySQL default configuration must be changed to listen on a port other than 3306. Change the value of the port variable in the my.ini file to a high unused port. For eg.

[mysqld]

# The TCP/IP Port the MySQL Server will listen on port=5306