1. 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:
skip-networking
The file is located in the "/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
bind-address=127.0.0.1
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';
2. 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.
set-variable=local-infile=0
3. 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
4. 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
5. 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;
6. Lower system privileges
A very common database security recommendation is to lower the permissions given to various parties. MySQL is no different. Typically, when developers work, they use the system's maximum permission and give less consideration to permission principles than we might expect. This practice can expose the database to significant risk.
* Any new MySQL 5.x installation already installed using the correct security measures.
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".
shell>ls -l /var/lib/mysql
In addition, ensure that only the user "mysql" and "root" have access to the directory /var/lib/mysql.
The mysql binaries, 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.
shell>ls -l /usr/bin/my*
7. 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:
[mysqld]
skip-show-database
8. Enable Logging
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.
9. Change the root directory
A chroot on Linux operating systems is an operation that changes the apparent disk root directory for the current running process and its children. A program that is re-rooted to another directory cannot access or name files outside that directory, and the directory is called a "chroot jail" or (less commonly) a "chroot prison".
By using the chroot environment, the write access of the MYSQL processes (and child processes) can be limited, increasing the security of the server.
Ensure that a dedicated directory exists for the chrooted environment. This should be something like: /chroot/mysql In addition, to make the use of the database administrative tools convenient, the following parameter should be changed in the [client] section of MySQL configuration file:
[client]
socket = /chroot/mysql/tmp/mysql.sock
Thanks to that line of code, there will be no need to supply the mysql, mysqladmin, mysqldump etc. commands with the --socket=/chroot/mysql/tmp/mysql.sock parameter every time these tools are run.
10. Remove History
During the installation procedures, there is a lot of sensitive information that can assist an intruder to assault a database. This information is stored in the server’s history and can be very helpful if something goes wrong during the installation. By analyzing the history files, administrators can figure out what has gone wrong and probably fix things up. However, these files are not needed after installation is complete.
We should remove the content of the MySQL history file (~/.mysql_history), where all executed SQL commands are stored (especially passwords, which are stored as plain text):
cat /dev/null > ~/.mysql_history
or mysql_secure_installation
NOTE: RUNNING ALL PARTS OF THIS SCRIPT IS RECOMMENDED FOR ALL MySQL
SERVERS IN PRODUCTION USE! PLEASE READ EACH STEP CAREFULLY!
In order to log into MySQL to secure it, we'll need the current
password for the root user. If you've just installed MySQL, and
you haven't set the root password yet, the password will be blank,
so you should just press enter here.
Enter current password for root (enter for none):
OK, successfully used password, moving on...
Setting the root password ensures that nobody can log into the MySQL
root user without the proper authorisation.
Set root password? [Y/n] n
... skipping.
By default, a MySQL installation has an anonymous user, allowing anyone
to log into MySQL without having to have a user account created for
them. This is intended only for testing, and to make the installation
go a bit smoother. You should remove them before moving into a
production environment.
Remove anonymous users? [Y/n] y
... Success!
Normally, root should only be allowed to connect from 'localhost'. This
ensures that someone cannot guess at the root password from the network.
Disallow root login remotely? [Y/n] y
... Success!
By default, MySQL comes with a database named 'test' that anyone can
access. This is also intended only for testing, and should be removed
before moving into a production environment.
Remove test database and access to it? [Y/n] y
- Dropping test database...
... Success!
- Removing privileges on test database...
... Success!
Reloading the privilege tables will ensure that all changes made so far
will take effect immediately.
Reload privilege tables now? [Y/n] y
... Success!
Cleaning up...
All done! If you've completed all of the above steps, your MySQL
installation should now be secure.
Thanks for using MySQL!
No comments:
Post a Comment