Linux server security (3)-MySQL

1. Preface

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 strong functions, ease of use, convenient management, fast operation speed, strong security and reliability. Users can use many languages to write programs that access the MySQL database, especially PHP, it is widely used.

Because MySQL is a multi-platform database, its default configuration should be considered to be applicable in various circumstances, so further security reinforcement should be carried out in our own use 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 the MySQL database must begin with two aspects: Internal Security of the system and external network security. In addition, we will briefly introduce some precautions and tips for programming.

2. Internal System Security

First, we will briefly introduce the directory structure of the MySQL database. After MySQL is installed and the mysql_db_install script is run, the data directory and database initialization will be established. If we use the MySQL source code package and the installation directory is/usr/local/MySQL, the data directory is usually/usr/local/MySQL/var. The database system is composed of a series of databases, each containing a series of database tables. MySQL creates a database directory in the data directory with the database name. Each database table uses the database table name as the file name, put the three files with the extension MYD, myi, and frm in the database directory.

The MySQL authorization table provides flexible permission Control for database access. However, if a local user has the permission to read database files, attackers only need to package and copy the database directories, copy it to the data directory of your local machine to access the stolen database. Therefore, the security of the MySQL host is the top priority. If the host is insecure and controlled by attackers, the security of MySQL cannot be discussed. The second is the security of data directories and data files, that is, permission settings.

From the perspective of some old binary distributions on 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, therefore, database files are insecure. In version 3.22.xx, the attribute of the data directory is 770, which is also dangerous. Local Users in the same group can both read and write data, so data files are not secure. The attribute of the Data Directory of 3.23.xx is 700, which is better. Only the user who starts the database can read and write the database files, ensuring the security of local data files.

If the user who starts the MySQL database is MySQL, the following directories and files are safe. 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 4096 Feb 27 20:07 include

Drwxrwxr-x 2 root Root 4096 Feb 27 info drwxrwxr-x 3 Root 4096 Feb 27 lib drwxrwxr-x 2 root Root 4096 Feb 27 07 libexec drwxrwxr-x 3 Root 4096 feb 27 man drwxrwxr-x 6 Root 4096 Feb 27 mysql-test drwxrwxr-x 3 Root 4096 Feb 27 07 share drwxrwxr-x 7 Root 4096 Feb 27 SQL- mongodrwx ------ 4 MySQL 4096 Feb 27 VaR

Shell> ls-L/usr/local/MySQL/var

Total 8

Drwx ------ 2 MySQL 4096 Feb 27 MySQL

Drwx ------ 2 MySQL 4096 Feb 27 Test

Shell> ls-L/usr/local/MySQL/var/MySQL

Total 104

-RW ------- 1 MySQL 0 Feb 27 20:08 columns_priv.myd

-RW ------- 1 MySQL 1024 Feb 27 columns_priv.myi

-RW ------- 1 MySQL 8778 Feb 27 columns_priv.frm

-RW ------- 1 MySQL 302 Feb 27 dB. MYD

-RW ------- 1 MySQL 3072 Feb 27 dB. myi

-RW ------- 1 MySQL 8982 Feb 27 dB. FRM

-RW ------- 1 MySQL 0 Feb 27 20:08 func. MYD

-RW ------- 1 MySQL 1024 Feb 27 func. myi

-RW ------- 1 MySQL 8641 Feb 27 func. FRM

-RW ------- 1 MySQL 0 Feb 27 20:08 host. MYD

-RW ------- 1 MySQL 1024 Feb 27 host. myi

-RW ------- 1 MySQL 8958 Feb 27 host. FRM

-RW ------- 1 MySQL 0 Feb 27 20:08 tables_priv.myd

-RW ------- 1 MySQL 1024 Feb 27 tables_priv.myi

-RW ------- 1 MySQL 8877 Feb 27 tables_priv.frm

-RW ------- 1 MySQL 428 Feb 27 user. MYD

-RW ------- 1 MySQL 2048 Feb 27 user. myi

-RW ------- 1 MySQL 9148 Feb 27 user. FRM

If the owner and attributes of these files are not the same, use the following two commands to correct them:

Shell> chown-r mysql. MySQL/usr/local/MySQL/var

Shell> chmod-r go-rwx/usr/local/MySQL/var

Starting remote services with the root user has always been a security taboo, because if the service program encounters problems, remote attackers are very likely to gain full control of the host. MySQL has made minor changes since version 3.23.15. After installation by default, the service should be started by mysql users, and root users are not allowed to start the service. If you have to use the root user for startup, you must add the -- user = Root parameter (./safe_mysqld -- user = root &). MySQL has the SQL statements of load data infile and select... into OUTFILE. If the root user starts the MySQL server, the database user has the write permission of the root user. However, MySQL still imposes 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 logs. Some users log on to or back up the database locally for convenience, and sometimes directly include the database password in the command line parameters, such: shell>/usr/local/MySQL/bin/mysqldump-uroot-Ptest test> test. SQL shell>/usr/local/MySQL/bin/MySQL-uroot-Ptest commands are recorded by Shell in history files. For example, bash is written to the user directory. bash_history files. If these files are accidentally read, the database password will be leaked. The SQL commands executed after you log on to the database are also recorded in the. mysql_history file in the user directory by MySQL. If the database user uses an SQL statement to modify the Database Password, The. mysql_history file will also leak. Therefore, do not add a password after-P during shell login and backup. Instead, enter the database password after prompt.

In addition, we should not allow these two files to 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.

3. External Network Security

After the MySQL database is installed, the user table on the UNIX platform is as follows:

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 |

| RedHat | root | Y |

| Localhost | n |

| RedHat | n |

+ ----------- + ------ + ---------- + ------------- + ------------ +

4 rows in SET (0.00 Sec)

The User table on Windows is as follows:

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 |

| % | Root | Y |

| Localhost | Y |

| % | N |

+ ----------- + ------ + ---------- + ------------- + ------------ +

4 rows in SET (0.00 Sec) Let's first look at the user table on the UNIX platform. Here, RedHat is only the name of the machine on our testing machine. Therefore, MySQL on the UNIX platform only allows the local machine to connect to the database by default. However, the default root user password is empty, so it is imperative to add a password to the root user. There are three methods to add passwords to database users: 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 root user in the MySQL database is changed to test. (Test is just an example. We cannot use this weak password that is easy to guess)

2) use set password to change the password:

Mysql> set password for root
Localhost = PASSWORD ('test ');

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 root user of the MySQL database is changed to test. The last command flush privileges indicates force refresh of the memory authorization table. Otherwise, the buffer password is used. In this case, illegal users can also log on with the root user and empty password, until the MySQL server is restarted.

We also see anonymous users with empty users. Although they do not have any permissions on UNIX platforms, we should delete them for security reasons:

Mysql> Delete from user where user = '';

The User table of Windows MySQL is very different. We can see that the Host field is % apart from localhost. Here % indicates that any host is allowed to connect to the MySQL server. This is very insecure and creates a possible opportunity for attackers. We must delete the record whose host field is %:

Mysql> Delete from user where host = '% ';

By default, the empty password of the root user must also be modified. The three methods are the same as those on the UNIX platform.

We noticed that anonymous users whose host field is localhost have all permissions! That is to say, a local user can log on to the MySQL database server with an empty username and password to obtain the highest permission! Therefore, anonymous users must be deleted!

Mysql> Delete from user where user = '';

Do not forget to use flush privileges to forcibly refresh the memory authorization table after the user table operation.

There are too many insecure factors in the installed Windows version of MySQL by default. We must configure it further after installation!

Five MySQL authorization tables: User, DB, host, tables_priv and columns_priv provide flexible security mechanisms. Two statements Grant and revoke are introduced from MySQL 3.22.11 to create and delete user permissions, you can easily restrict which user can connect to the server, where to connect to the server, and what operations can be performed after the connection. As a MySQL administrator, we must understand the meaning of the authorization table and how to use grant and revoke to create, authorize, revoke, and delete users. The MySQL authorization mechanism before version 3.22.11 is not complete and is significantly different from the new version. We recommend that you upgrade to the latest version of MySQL. (The Operation example in this book is MySQL 3.23.49.) Let's first understand the structure of the authorization table.

1) structure and content of the MySQL authorization table:

Mysql> DESC user;

+ ----------------- + ------ + ----- + --------- + ------- + | FIELD | type | null | key | default | extra | + ----------------- + --------------- + ------ + ----- + --------- + -------

| Host | char (60) binary | pri |

| User | char (16) binary | pri |

| Password | char (16) binary ||||

| Select_priv | Enum ('n', 'y') | n |

| Insert_priv | Enum ('n', 'y') | n |

| Update_priv | Enum ('n', 'y') | n |

| Delete_priv | Enum ('n', 'y') | n |

| Create_priv | Enum ('n', 'y') | n |

| Drop_priv | Enum ('n', 'y') | n |

| Reload_priv | Enum ('n', 'y') | n |

| Shutdown_priv | Enum ('n', 'y') | n |

| Process_priv | Enum ('n', 'y') | n |

| File_priv | Enum ('n', 'y') | n |

| Grant_priv | Enum ('n', 'y') | n |

| References_priv | Enum ('n', 'y') | n |

| Index_priv | Enum ('n', 'y') | n |

| Alter_priv | Enum ('n', 'y') | n |

+ ----------------- + ------ + ----- + --------- + ------- +

17 rows in SET (0.01 Sec)

The User table is the most important of the Five authorization tables. It lists the users that can connect to the server and their encryption passwords, and specifies which global (superuser) permissions they have. All permissions enabled in the User table are global permissions and apply to all databases. Therefore, we cannot grant any user the permission to access the mysql. User table!

Permission description:

+ ----------- + ------------- + ----------------------------------------------------------------------- +

| Permission specifier | column name | permission operation |

+ ----------- + ------------- + ----------------------------------------------------------------------- +

| Select | select_priv | table access is allowed. Select statements that do not allow access to data tables are not affected. For example, select 1 + 1 | + ----------- + ------------- + Limit +

| Insert | insert_priv | insert statements can be used to write data to a table. | + ----------- + ------------- + ----------------------------------------------------------------------- +

| Update | update_priv | allows you to use the update statement to modify existing records in a table. | + ----------- + ------------- + ----------------------------------------------------------------------- + | Delete | delete_priv | allow the use of Delete statements to delete existing records in a table. | + ----------- + ------------- + ----------------------------------------------------------------------- + | Create | create_priv | allow new databases and tables. | + ----------- + ------------- + ----------------------------------------------------------------------- +

| Drop | drop_priv | allows you to delete existing databases and tables. |

+ ----------- + ------------- + ----------------------------------------------------------------------- +

| Index | index_priv | allows you to create, modify, or delete an index. |

+ ----------- + ------------- + ----------------------------------------------------------------------- +

| Alter | alter_priv | the alter statement can be used to modify the table structure. |

+ ----------- + ------------- + ----------------------------------------------------------------------- +

| Grant | grant_priv | grant permissions to other users, including grant. |

+ ----------- + ------------- + ----------------------------------------------------------------------- +

| Reload | allows commands such as reloading the authorization table and refreshing the server. |

+ ----------- + ------------- + ----------------------------------------------------------------------- +

| Shutdown | shudown_priv | the mysqladmin shutdown command is allowed to shut down the MySQL server. This permission is dangerous. |

| It cannot be granted at will. |

+ ----------- + ------------- + ----------------------------------------------------------------------- +

| Process | process_priv | allows you to view and terminate the running threads (processes) and query statements of the MySQL server. |

|, Including executing the query statement for password modification. This permission is dangerous and should not be granted at will. |

+ ----------- + ------------- + --------------------------------------------------------------------- + | File | file_priv | allows you to read and write global readable files from the server. This permission is dangerous and should not be granted at will. | + ----------- + ------------- + ----------------------------------------------------------------------- +

Mysql> DESC dB;

+ ----------------- + ------ + ----- + --------- + ------- + | FIELD | type | null | key | default | extra |

+ ----------------- + ------ + ----- + --------- + ------- + | Host | char (60) binary | pri | dB | char (64) binary | pri |

| User | char (16) binary | pri |

| Select_priv | Enum ('n', 'y') | n | insert_priv | Enum ('n', 'y ') | n | update_priv | Enum ('n', 'y') | n |

| Delete_priv | Enum ('n', 'y') | n |

| Create_priv | Enum ('n', 'y') | n |

| Drop_priv | Enum ('n', 'y') | n |

| Grant_priv | Enum ('n', 'y') | n |

| References_priv | Enum ('n', 'y') | n |

| Index_priv | Enum ('n', 'y') | n |

| Alter_priv | Enum ('n', 'y') | n |

+ ----------------- + ------ + ----- + --------- + ------- +

13 rows in SET (0.01 Sec)

The database table lists the databases, and the user has the permission to access them. The permission specified here applies to all tables in a database.

Mysql> DESC host;

+ ----------------- + ------ + ----- + --------- + ------- +

| FIELD | type | null | key | default | extra |

+ ----------------- + ------ + ----- + --------- + ------- +

| Host | char (60) binary | pri |

| DB | char (64) binary | pri |

| Select_priv | Enum ('n', 'y') | n |

| Insert_priv | Enum ('n', 'y') | n |

| Update_priv | Enum ('n', 'y') | n |

| Delete_priv | Enum ('n', 'y') | n |

| Create_priv | Enum ('n', 'y') | n |

| Drop_priv | Enum ('n', 'y') | n |

| Grant_priv | Enum ('n', 'y') | n |

| References_priv | Enum ('n', 'y') | n | index_priv | Enum ('n', 'y') | n |

| Alter_priv | Enum ('n', 'y') | n |

+ ----------------- + ------ + ----- + --------- + ------- +

12 rows in SET (0.01 Sec)

The host table and DB table are used in combination to control the database access permissions of a specific host at a good level, which may be better than using the database separately. This table is not affected by the grant and revoke statements, so you may find that you are not using it at all.

Mysql> DESC tables_priv;

+ ------------- + --------------------------- + ------ + ----- + --------- + ------- + | FIELD | type | null | key | default | extra |

+ ------------- + --------------------------- + ------ + ----- + --------- + ------- + | Host | char (60) binary | pri | dB | char (64) binary | pri |

| User | char (16) binary | pri | table_name | char (60) binary | pri | grantor | char (77) | Mul |

| Timestamp (14) | Yes | null |

| Table_priv | set ('select', 'insert', |||| |

| 'Update', 'delete', 'create', ||||

| 'Drop', 'Grant ', 'references', |||| |

| 'Index', 'alter ') |

| Column_priv | set ('select', 'insert', |||| |

| 'Update', 'references ') |

+ ------------- + --------------------------- + ------ + ----- + --------- + ------- +

8 rows in SET (0.01 Sec)

Tables_priv table specifies table-level permissions. The permission specified here applies to all columns in a table.

Mysql> DESC columns_priv;

+ ------------- + ------------------------ + ------ + ----- + --------- + ---- +

| FIELD | type | null | key | default | extra |

+ ------------- + ------------------------ + ------ + ----- + --------- + ---- +

| Host | char (60) binary | pri |

| DB | char (64) binary | pri |

| User | char (16) binary | pri |

| Table_name | char (64) binary | pri |

| Column_name | char (64) binary | pri |

| Timestamp (14) | Yes | null |

| Column_priv | set ('select', 'insert', |||| |

| 'Update', 'references ') | + --------------- + ------------------------ + ------ + ----- + --------- + ------- +

7 rows in SET (0.00 Sec)

The columns_priv table specifies the column-level permission. The permission specified here applies to specific columns of a table.

2) MySQL authorization table Running Mechanism

MySQL has two steps:

A) The server checks whether the user is allowed to connect.

B) if the user has the right to connect, the server will also check whether each of its requests has sufficient permissions. For example, if you want to retrieve a table in a database, you must have the select permission for the database. to delete a table in the database, you must have the drop permission for the database.

The user, DB, and host tables in the authorization table use these two steps. tables_priv and columns_priv tables only use step 2 (check the request ). Each authorization table contains a range column that determines when a permission is applied and a permission column that determines which permission is granted. The range column specifies when permissions in the table are applied. Each authorization table entry contains the user and host columns to specify when permissions are applied to a connection from a given user to a given host. Other tables contain additional range columns. For example, a DB table contains a DB column to indicate which database the Permission applies. Similarly, tables_priv and columns_priv tables contain a range field to narrow down to a specific table in a database or a specific column in a table. The following are examples of the combination of the Host field and user field in the User table: + --------------------------- + -------- + host + | host value | user value | matched connection | + --------------------------- + -------- + accept +

| 'X. Y. Z' | 'test' | the test user can only connect to the database from x. y. z |

+ ----------------------------- + -------- + ----------------------------------------------- +

| 'X. Y. Z' | ''| any user can connect to the database from x. y. z |

+ ----------------------------- + -------- + ----------------------------------------------- +

| '%' | 'Test' | the test user can connect to the database from any host. |

+ ----------------------------- + -------- + ---------------------------------------------- +

| ''| Any user can connect to the database from any host |

+ ----------------------------- + -------- + ----------------------------------------------- +

| '%. Y. Z' | 'test' | the test user can connect to the database from any host in the Y. z domain |

+ ----------------------------- + -------- + ---------------------------------------------- +

| 'X. Y. % '| 'test' | the test user can connect to the database from hosts such as x.y.net, x.y.com, and x.y.edu. | + ----------------------- + -------- + users +

| '1970. 168.1.1 '| 'test' | the test user can connect to the database from a host with the IP address 192.168.1.1 |

+ ----------------------------- + -------- + --------------------------------------------- +

| '1970. 192.% '| 'test' | the test user can connect to the database from any host in the class C subnet 192.168.1 |

+ ----------------------------- + -------- + --------------------------------------------- + | '2017. 168.1.0/255.255.0 '| 'test' | same as above |

+ ----------------------------- + -------- + -------------------------------------------- +

The SQL string wildcard % indicates matching any character. It can be 0 characters, and the wildcard _ indicates matching a character.

The permission column specifies the permissions of the user specified in the range column. This table uses the permission name of the grant statement. The names of most permission columns in the user, DB, and host tables are obviously related to the grant statement. For example, select_priv corresponds to select permission.

3) example of using the authorization table

Grant is used to add and create permissions for users, and revoke is used to delete user permissions. Here are some examples of using grant to add users and create permissions:

Mysql> grant all privileges on *. * to test
Localhost identified by 'test' with grant option;

Add a test user (Super User) with all local permissions, and the password is test. *. * In the on Clause indicates "all databases, All Tables ". With grant option indicates that it has the grant permission.

Mysql> grant select, insert, update, delete, create, drop privileges on test. * To test1
'192. 168.1.0/255.255.255.0 'identified by 'test ';

This statement adds a test1 user with the password test, but it can only be connected from the class C subnet 192.168.1. For the test database, select, insert, update, delete, create, drop operation permission.

You do not need to manually refresh the authorization table to create permissions using the grant statement because it has been automatically refreshed.

To create permissions for users, you can also directly modify the authorization table:

Mysql> insert into user values ("localhost", "test", password ("test"), "Y", "Y ", "Y ", "Y ");

Mysql> flush privileges;

These two statements have the same effect as the grant statement above, and a local test Super User is added. We can see that it is much more convenient to use grant, and there is no need to flush privileges.

Mysql> insert into user (host, user, password) values ("192.168.1.0/255.255.255.0", "test1", password ("test "));

Mysql> insert into DB values ("192.168.1.0/255.255.255.0", "test", "test1", "Y ", "Y", "Y", "n", "n ")

Mysql> flush privileges;

These three statements have the same effect as the grant statement in the above two statements. They also add a connection that can only be connected from Class C subnet 192.168.1, and have the select, insert, update, delete, create, test1 user with the drop operation permission, and the password is test. To cancel a user's permissions, use the revoke statement. The revoke syntax is very similar to the grant statement. Except to replace from with without the identified by and with grant option clauses, the following example uses Revoke to delete user permissions:

Mysql> revoke all on test. * From test1
'1970. 168.1.0/255.255.255.0 ';

This revoke revokes the permissions created by Grant in the second sentence above, but the test1 user is not deleted and must be manually deleted from the user table:

Mysql> Delete from user where user = 'test1'; mysql> flush privileges;

In this way, the test1 user is completely deleted.

These are just simple usage of the MySQL authorization table. For more information, see the manual provided by MySQL.

3. Notes for programming

No matter which programming language is used to write programs connected to the MySQL database, there is a rule that never trust the data submitted by users!

For numeric fields, we need to use the query statement: Select * from table where id = '000000'. Do not use a query statement such as select * from table where id = 234. MySQL automatically converts a string to a numeric character unless it is a numeric character. If the data submitted by the user is processed by mysql_escape_string, we can completely eliminate the SQL inject attack. For SQL inject attacks, see the following link: