Shulou(Shulou.com)05/31 Report--

This article introduces the relevant knowledge of "the security configuration of MySQL database". In the operation of actual cases, many people will encounter such a dilemma, so let the editor lead you to learn how to deal with these situations. I hope you can read it carefully and be able to achieve something!

Since MySQL is a multi-platform database, its default configuration should be considered to be applicable in all situations, so further security reinforcement should be carried out in our own environment. As a MySQL system administrator, we have the responsibility to maintain the data security and integrity of the MySQL database system.

The security configuration of MySQL database must start from two aspects: internal security and external security of the system. In addition, we will briefly introduce some problems and tips that we should pay attention to when programming.

Internal security of the system

Start with a brief introduction to the directory structure of the MySQL database. MySQL is installed, and after running the mysql_db_install script, the data directory is established and the database is initialized. If we install with the MySQL package and the installation directory is / usr/local/mysql, the data directory will generally be / usr/local/mysql/var. The system consists of a series of databases, each of which contains a series of database tables. MySQL uses the database name to establish a database directory in the data directory. Each database table takes the database table name as the file name, and three files with extensions of MYD, MYI and frm are placed in the database directory.

MySQL's authorization table provides flexible access control to the database, but if the local user has read access to the library file, the attacker only needs to package and copy the database directory, and then copy it to his own local data directory to access the stolen database. So the security of the host where the MySQL is located is the most important issue. If the host is not secure and is controlled by the attacker, then the security of the MySQL is out of the question. The second is the security of data directories and data files, that is, the problem of permission setting.

From some old binary distributions of the MySQL master station, the attribute of the data directory in the 3.21.xx version is 775, which is very dangerous. Any local user can read the data directory, so the database file is very insecure. In the 3.22.xx version, the attribute of the data directory is 770. this attribute is also dangerous, and local users in the same group can both read and write, so the data file is not secure. The attribute of the 3.23.xx version data directory is 700. this is better. Only the user who starts the database can read and write the database file, which ensures the security of the local data file.

If the user who started the MySQL database is, then directories and files like the following are safe, please pay attention to the data directory and the following properties:

Shell > ls-1 / 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-1 / 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-1 / 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 correct them with the following two commands:

Shell > chown-R mysql.mysql / usr/local/mysql/var

Shell > chmod-R go-rwx / usr/local/mysql/var

Starting a remote service with a root user has always been a security taboo, because if something goes wrong with the service program, the remote attacker is likely to gain full control of the host. MySQL has made a small change since version 3.23.15. By default, the service should be started by mysql users after installation, and root users are not allowed to start it. If you have to start with the root user, you must add the parameter-- user=root (. / safe_mysqld-- user=root &). Because there are LOAD DATA INFILE and SELECT in MySQL. The SQL statement of INTO OUTFILE, 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, such as LOAD DATA INFILE can only read globally readable files, SELECT. INTO OUTFILE cannot overwrite a file that already exists.

Local log files cannot be ignored, including shell's logs and MySQL's own logs. For convenience when logging in locally or backing up the database, some users sometimes bring the database password directly 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 by shell in history files, such as bash will be written to the user directory. Bash_history file, if these files are inadvertently read, then the database password will be leaked. The SQL commands executed after the user logs in to the database will also be recorded by MySQL in the. mysql_history file of the user directory. If the database user changes the database password with SQL statements, it will also be leaked because of the. MySQL _ history file. So when we log in and back up shell, do not add the password directly after-p, but enter the database password after the prompt.

In addition, we should not let these two documents 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 door-to-door commands link these two files to / dev/null, so that our operations will not be recorded in these two files.

External network security

After the MySQL database is installed, the user table for 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 for 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. Redhat is only the machine name of my test machine, so in fact, the MySQL of the Unix platform only allows the local computer to connect to the database by default. But the default root 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 to change the root password at the shell prompt:

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 passwords we actually use must not use such guessable weak passwords.)

2) modify the password with set password:

Mysql > set password for)

At this point, the root user's password is changed to test.

3) directly modify the root user password in the user table:

Mysql > use mysql

Mysql > update user set password=password (test) where user=root

This is the end of the content of "Security configuration of MySQL Database". Thank you for reading. If you want to know more about the industry, you can follow the website, the editor will output more high-quality practical articles for you!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.