MySQL security configuration document

MySQL is a real multi-user, multi-thread SQL database server. It is the implementation of a Client/Server structure. MySQL is one of the popular relational databases. Compared with other database management systems (DBMS), MySQL has the advantages of being small, complete, and fast in query. MySQL is designed to be fast, robust, and easy to use. At present, it has been well applied in large and medium-sized enterprises. However, because it is a multi-platform database, the inevitable default configuration is also suitable for various situations, therefore, you need to reinforce the use of MySQL in a custom environment.

If the software has serious security problems, even if the security configuration is better, it is useless. Therefore, you must first understand the MySQL version. The MySQL version is described in the MySQL official document as follows:
MySQL 5.2 is the latest release series and will execute new functions. In the near future, Alpha release will be available for a wide range of tests by interested users.
MySQL 5.1 is the current stable (Product Quality) release series. Only vulnerability fixes are released again; no new features affect stability are added.
MySQL 5.0 is the previous stable (Product Quality) release series. Only critical vulnerability fixes and security fixes are rereleased. No additional features will affect this series of important features.
MySQL 4.0 and 3.23 are the old stable (Product Quality) release series. This version is no longer used. New releases are only used to fix especially serious vulnerabilities (previous security issues ).
The Mysql development organization does not consider that there is a completely frozen version, because any version must fix the vulnerability and other vulnerabilities. For a "freeze to some extent", they mean they can add some things that do not affect their current work in product release. Of course, the previous series of vulnerability fixes will be transplanted to the subsequent series.

According to official recommendations, we recommend that you use the current stable MySQL 5.1 version so far. If you are running an old system and want to upgrade it, but do not want to take the risk of non-seamless upgrade, you should upgrade to the latest version of the same release series you are using (only the last part of the version number is newer than you are using, for example, 5.0 and 5.1 are the same series ).

1. Install MySQL

MySQL can be RPM installed in the redhat environment and apt installation in debian, but the latest packages are generally in the source code format. Therefore, select the source code compilation and installation method.
First log onto the official website http://www.mysql.com, download the latest released version.
# Tar zxf mysql-5.1.22-rc-linux-i686-glibc23.tar.gz
Create mysql users and mysql user groups for mysql running
# Groupadd mysql
# Useradd-g mysql
#./Configure -- prefix =/usr/local/mysql
# Make
# Make install
# Cp support-files/my-medium.cnf/etc/my. cf
# Bin/mysql_install_db -- user = mysql // use mysql to generate the initial database. thank for using mysql indicates that the database is successfully initialized.
# Chown-R root. // the current directory is given to root
# Chown-R mysql var // var to mysql. This is very important and secure.
# Chgrp-R mysql.
# Bin/mysqld_safe -- user = mysql &
# Bin/mysql-u root
At this time, the security is complete, but the most important thing is to perform security configuration for MySQL and check your system, the most basic thing is to do the following configuration.

Ii. MySQL Security Configuration

As a data management platform, database security is first determined by the system's internal security and network security. For the system administrator, you must first ensure the security of the system. When installing the MySQL database, you must configure the basic environment.

1. Modify the root user password and delete the empty password.
By default, the root user of the installed MySQL instance has a blank password. For security reasons, it must be changed to a strong password. The so-called strong password should be at least 8 characters long, an irregular password consisting of letters, numbers, and symbols. Use the MySQL Command mysaladmin to change the root password. You can also log on to the database and modify the field content of the user table under mysql. The modification method is as follows:

#/Usr/local/mysql/bin/mysqladmin-u root password "upassword" // use mysqladmin
# Mysql> use mysql;
# Mysql> update user set password = password ('upassword') where user = 'root ';
# Mysql> flush privileges; // force refresh the memory authorization table. Otherwise, the password is still cached in the memory.

2. delete default databases and database users
Generally, the MySQL database is installed locally and only needs a local php script to read mysql. Therefore, many users do not need it, especially those installed by default. After MySQL initialization, empty users and test libraries are automatically generated for installation testing. This poses a threat to the security of the database. It is necessary to delete all of them. The final state is only one root, add users and databases as needed.

# Mysql> show databases;
# Mysql> drop database test; // Delete the database test
# Use mysql;
# Delete from db; // delete the table information that stores the database because there is no database information.
# Mysql> delete from user where not (user = 'root'); // delete an initial non-root user
# Mysql> delete from user where user = 'root' and password = ''; // delete the root with an empty password. Try to repeat the operation.
Query OK, 2 rows affected (0.00 sec)
# Mysql> flush privileges; // force refresh the memory authorization table.

3. Change the default mysql Administrator Account
The Administrator name of the system mysql is root, but generally the database administrator has not modified it, which facilitates the malicious behavior of the system users to some extent, in this case, change the username to a complex username. Do not set it to admin or administraror because they are also in the user dictionary that is easy to guess.

Mysql> update user set user = "newroot" where user = "root"; // change the user name that is not easy to guess
Mysql> flush privileges;

4. Password Management
Password is an important factor in database security management. Do not save a plain text password to the database. If your computer is at risk, intruders can get all the passwords and use them. Instead, MD5 (), SHA1 (), or unidirectional hash functions should be used. Do not select passwords from the dictionary. Special programs can be used to crack them. Select a strong password consisting of at least eight characters including letters, numbers, and symbols. When accessing the password, use the SQL statement of mysql's built-in function password () to encrypt and store the password. For example, add a new user to the users table.

# Mysql> insert into users values (1, password (1234), 'test ');

5. Run msyql with an independent user
Never run the MySQL server as a root user. This operation is very dangerous because any user with FILE Permission can use root to create files (for example ,~ Root/. bashrc ). Mysqld rejects root running unless explicitly specified using the -- user = root option. Mysqld should be run with a normal non-authorized user. As in the previous installation process, create an independent mysql account in linux for the database. This account is only used to manage and run MySQL.
To start mysqld with other Unix users, add the user option to specify the username of the [mysqld] group in the/etc/my. cnf option file or the my. cnf option file of the server data directory.
# Vi/etc/my. cnf
[Mysqld]
User = mysql
This command enables the server to start with a specified user. Whether you start it manually or through mysqld_safe or mysql. server, you can ensure that you use the mysql identity. You can also add the user parameter when starting the database.

#/Usr/local/mysql/bin/mysqld_safe -- user = mysql &
As other linux users, you do not need to run mysqld as root. You do not need to change the root user name in the user table because the MySQL account user name is irrelevant to the linux account user name. Make sure that only linux users with read or write permissions on the database directory are used to run mysqld.

6. Remote database connection prohibited
Under the command line netstat-ant, the default port 3306 is enabled. In this case, the mysqld network listener is enabled, allowing you to remotely connect to the local database using the account password, by default, data can be remotely connected. To disable this function, start skip-networking and do not listen to any SQL TCP/IP connection, cut off the remote access right and ensure security. To remotely manage databases, you can install PhpMyadmin. If you do need to remotely connect to the database, at least modify the default listening port, and add firewall rules, only allow data passing through the trusted network's mysql listening port.

# Vi/etc/my. cf
Remove the # skip-networking annotation.
#/Usr/local/mysql/bin/mysqladmin-u root-p shutdown // stop the database
#/Usr/local/mysql/bin/mysqld_safe -- user = mysql & // start mysql with mysql users in the background

7. restrict the number of connected users
If a user of a database remotely connects to the database multiple times, the performance may decrease and the operations of other users may be affected. Therefore, it is necessary to restrict the operation. You can set the max_user_connections variable in mysqld of the my. cnf file to limit the number of connections allowed by a single account. The GRANT statement can also support resource control options to limit the scope of use allowed by the server to an account.

# Vi/etc/my. cnf
[Mysqld]
Max_user_connections 2

8. user directory permission restrictions
The default mysql is installed in/usr/local/mysql, and the corresponding database file is in the/usr/local/mysql/var directory. Therefore, you must ensure that this directory cannot allow unauthorized users to package and copy the database. Therefore, you must restrict access to this directory. Make sure that only linux users with read or write permissions on the database directory are used to run mysqld.
# Chown-R root/usr/local/mysql // mysql home directory to root
# Chown-R mysql. mysql/usr/local/mysql/var // ensure that the permission of the database directory belongs to the mysql user

9. Command history Protection
Database-related shell operation commands are recorded in. bash_history: if these files are accidentally read, the Database Password, database structure, and other information will be leaked, and the operations after logging on to the database will be recorded in. in the mysql_history file, if you use the update table information to modify the database user password, the password will also be read. Therefore, you need to delete these two files, during password-related operations such as login or database backup, you should use the-p parameter to add the prompt to enter the password, and then enter the password implicitly. We recommend that you leave the above files blank.

# Rm. bash_history. mysql_history // Delete the history
# Ln-s/dev/null. bash_history // empty the shell record file
# Ln-s/dev/null. mysql_history // leave the mysql record file empty

10. Prohibit MySQL from accessing local files
In mysql, the local file is read and the load data local infile command is used. This option is enabled by default in mysql 5.0, this operation will use MySQL to read local files to the database, and then the user can obtain sensitive information illegally. If you do not need to read local files, be sure to disable it.

Test: first, create the sqlfile.txt file under the test data library and separate fields with commas.
# Vi sqlfile.txt
1, sszng, 111
2. sman, 222
# Mysql> load data local infile 'sqlfile.txt 'into table users fields terminated by', '; // read data
# Mysql> select * from users;
+ -------- + ------------ + ---------- +
| Userid | username | password |
+ -------- + ------------ + ---------- +
| 1 | sszng | 111 |
| 2 | sman | 222 |
+ -------- + ------------ + ---------- +

If the local data is successfully inserted into the DATA, MySQL should be prohibited from using the "load data local infile" command. Some attack methods circulating on the Internet use load data local infile, and it is also a method used by many new SQL Injection attacks! Hackers can also use load datalocal infile to LOAD "/etc/passwd" into a database table and then use SELECT to display it. This operation is fatal to server security. You can add local-infile = 0 to my. cnf, or add the Parameter local-infile = 0 to start mysql.

#/Usr/local/mysql/bin/mysqld_safe -- user = mysql -- local-infile = 0 &
# Mysql> load data local infile 'sqlfile.txt 'into table users fields terminated ',';
# ERROR 1148 (42000): The used command is not allowed with this MySQL version

-- Local-infile = 0: Enable mysqld to disable all load data local commands on the server. If you need to obtain local files, open the command, but disable it.

11. MySQL Server permission Control
The main function of the MySQL permission system is to confirm the user connected to a given host and grant the user the SELECT, INSERT, UPDATE, and DELETE permissions on the database (see the user Super user table for details ). Its additional functions include anonymous users and the ability to authorize and manage MySQL-specific functions, such as load data infile.

The administrator can configure tables such as user, db, and host to control the user's access permissions. The user table permission is the Super user permission. It is wise to grant only the permissions of the user table to a Super user, such as a server or database supervisor. For other users, you should set the permissions in the user table to & #39; N & #39; and grant permissions only on the basis of a specific database. You can authorize a specific database, table, or column,