MySQL is a fully networked cross-platform relational database system and a distributed database management system with a client/server architecture. MySQL is a fully networked cross-platform relational database system and a distributed database management system with a client/server architecture. It has the advantages of powerful functions, easy use, convenient management, fast running speed, strong security and reliability. Users can use many languages to write programs to access the MySQL database, especially when combined with PHP, it is a golden combination and is widely used.
Since MySQL is a multi-platform database, its default configuration should be considered to be applicable in various situations, so further security reinforcement should be carried out in our own usage environment. As a MySQL system administrator, we are responsible for maintaining the data security and integrity of the MySQL database system.
The security configuration of the MySQL database must start from two aspects, internal system security and external network security. In addition, we will also briefly introduce some issues and tips to pay attention to when programming.
System internal security
First, let’s briefly introduce the MySQL database directory structure. After MySQL is installed, the data directory and database will be initialized after running the mysql_db_install script. If we use the MySQL source code package to install, and the installation directory is /usr/local/mysql, then the data directory will generally be /usr/local/mysql/var. A database system consists of a series of databases, each of which contains a series of database tables. MySQL uses the database name to create a database directory in the data directory. Each database table uses the database table name as the file name, and three files with the extensions MYD, MYI, and frm are placed in the database directory.
MySQL's authorization table provides flexible permission control for database access, but if the local user has read permissions to the library file, the attacker only needs to package and copy the database directory, and then copy it to his own data directory. Gain access to stolen databases. Therefore, the security of the host where MySQL is located is the most important issue. If the host is not secure and controlled by attackers, then the security of MySQL cannot be discussed. The second is the security of the data directory and data files, which is the issue of permission settings.
Judging from some old binary releases of the MySQL main site, the attribute of the data directory in version 3.21.xx is 775, which is very dangerous. Any local user can read the data directory, so the database file is very unsafe. The attribute of the data directory in version 3.22.xx is 770. This attribute is also somewhat dangerous. Local users in the same group can both read and write, so the data files are not safe. The attribute of the data directory in version 3.23.xx is 700, which is better. Only the user who starts the database can read and write the database file, ensuring the security of local data files.
If the user who starts the MySQL database is mysql, then the following directories and files are safe. Please pay attention to the data directory and the following attributes:
shell>ls -l /usr/local/mysql
total 40
drwxrwxr-x 2 root root 4096 Feb 27 20:07 bin
drwxrwxr-x 3 root root 4096 Feb 27 20:07 include
drwxrwxr-x 2 root root 4096 Feb 27 20:07 info
drwxrwxr-x 3 root root 4096 Feb 27 20:07 lib
drwxrwxr-x 2 root root 4096 Feb 27 20:07 libexec
drwxrwxr-x 3 root root 4096 Feb 27 20:07 man
drwxrwxr-x 6 root root 4096 Feb 27 20:07 mysql-test
drwxrwxr-x 3 root root 4096 Feb 27 20:07 share
drwxrwxr-x 7 root root 4096 Feb 27 20:07 sql-bench
drwx------ 4 mysql mysql 4096 Feb 27 20:07 var
shell>ls -l /usr/local/mysql/var
total 8
drwx------ 2 mysql mysql 4096 Feb 27 20:08 mysql
drwx------ 2 mysql mysql 4096 Feb 27 20:08 test
shell>ls -l /usr/local/mysql/var/mysql
total 104
-rw------- 1 mysql mysql 0 Feb 27 20:08 columns_priv.MYD
-rw------- 1 mysql mysql 1024 Feb 27 20:08 columns_priv.MYI
-rw------- 1 mysql mysql 8778 Feb 27 20:08 columns_priv.frm
-rw------- 1 mysql mysql 302 Feb 27 20:08 db.MYD
-rw------- 1 mysql mysql 3072 Feb 27 20:08 db.MYI
-rw------- 1 mysql mysql 8982 Feb 27 20:08 db.frm
-rw------- 1 mysql mysql 0 Feb 27 20:08 func.MYD
-rw------- 1 mysql mysql 1024 Feb 27 20:08 func.MYI
-rw------- 1 mysql mysql 8641 Feb 27 20:08 func.frm
-rw------- 1 mysql mysql 0 Feb 27 20:08 host.MYD
-rw------- 1 mysql mysql 1024 Feb 27 20:08 host.MYI
-rw------- 1 mysql mysql 8958 Feb 27 20:08 host.frm
-rw------- 1 mysql mysql 0 Feb 27 20:08 tables_priv.MYD
-rw------- 1 mysql mysql 1024 Feb 27 20:08 tables_priv.MYI
-rw------- 1 mysql mysql 8877 Feb 27 20:08 tables_priv.frm
-rw------- 1 mysql mysql 428 Feb 27 20:08 user.MYD
-rw------- 1 mysql mysql 2048 Feb 27 20:08 user.MYI
-rw------- 1 mysql mysql 9148 Feb 27 20:08 user.frm
If the owners and attributes of these files are not like this, please use the following two commands to correct it:
shell>chown -R mysql.mysql /usr/local/mysql/var
shell>chmod -R go-rwx /usr/local/mysql/var
Using the root user to start remote services has always been a security taboo, because if there is a problem with the service program, a remote attacker is very likely to gain full control of the host. MySQL has made minor changes since version 3.23.15. By default, the service must be started by the mysql user after installation, and the root user is not allowed to start. If you must use the root user to start, you must add the --user=root parameter (./safe_mysqld --user=root &). Because there are SQL statements of LOAD DATA INFILE and SELECT... INTO OUTFILE in MySQL, if the root user starts the MySQL server, then the database user has the write permission of the root user. However, MySQL still has some restrictions. For example, LOAD DATA INFILE can only read globally readable files, and SELECT... INTO OUTFILE cannot overwrite existing files.
Local log files cannot be ignored, including shell logs and MySQL's own logs. For convenience when logging in locally or backing up the database, some users sometimes directly include the database password in the command line parameters, such as:
shell>/usr/local/mysql/bin/mysqldump -uroot -ptest test>test.sql
shell>/usr/local/mysql/bin/mysql -uroot -ptest
These commands will be recorded in the history file by the shell. For example, bash will write the .bash_history file in the user directory. If these files are accidentally read, the password of the database will be leaked. SQL commands executed after the user logs in to the database will also be recorded by MySQL in the .mysql_history file in the user directory. If the database user changes the database password using SQL statements, it will also be leaked through the .mysql_history file. Therefore, we should not add the password directly after -p during shell login and backup, but enter the database password after the prompt.
In addition, we should not let these two files record our operations, just in case.
shell>rm .bash_history .mysql_history
shell>ln -s /dev/null .bash_history
shell>ln -s /dev/null .mysql_history
These two commands link these two files to /dev/null, so our operations will not be recorded in these two files.
External network security
After the MySQL database is installed, the user table on the Unix platform looks like this:
mysql> use mysql;
Database changed
mysql> select Host,User,Password,Select_priv,Grant_priv from user;
+-----------+------+----------+-------------+----- -------+
| Host | User | Password | Select_priv | Grant_priv |
+-----------+------+----------+-------------+----- -------+
| localhost | root | | Y | Y |
| redhat | root | | Y | Y |
| localhost | | | N | N |
| redhat | | | N | N |
+-----------+------+----------+-------------+----- -------+
4 rows in set (0.00 sec)
The user table on the Windows platform looks like this:
mysql> use mysql;
Database changed
mysql> select Host,User,Password,Select_priv,Grant_priv from user;
+-----------+------+----------+-------------+----- -------+
| Host | User | Password | Select_priv | Grant_priv |
+-----------+------+----------+-------------+----- -------+
| localhost | root | | Y | Y |
| % | root | | Y | Y |
| localhost | | | Y | Y |
| % | | | N | N |
+-----------+------+----------+-------------+----- -------+
4 rows in set (0.00 sec)
Let’s first look at the user table on the Unix platform. Among them, redhat is just the machine name of my test machine, so in fact, MySQL on the Unix platform only allows this machine to connect to the database by default. However, the default root user password is empty, so the top priority is to add a password to the root user. There are three ways to add a password to a database user:
1) Use the mysqladmin command at the shell prompt to change the root user password:
shell>mysqladmin -uroot password test
In this way, the password of the MySQL database root user is changed to test. (test is just an example, the password we actually use must not use such an easy-to-guess weak password)
2) Use set password to modify the password:
mysql> set password for root@localhost=password('test' );
At this time, the password of the root user is changed to test.
3) Directly modify the root user password of the user table:
mysql> use mysql;
mysql> update user set password=password('test') where user='root';
mysql> flush privileges;
In this way, the password of the MySQL database root user has also been changed to test. The last command, flush privileges, means to forcefully refresh the memory authorization table. Otherwise, the password in the cache will still be used. At this time, illegal users can also log in with the root user and an empty password until the MySQL server is restarted.
We also see the anonymous user whose user is empty. Although it has no permissions under the Unix platform, we should delete it for security reasons:
mysql> delete from user where user='';