MySQL database security configuration

xiaoxiao2021-03-06 73

1 Introduction

MySQL is a fully networked cross-platform relational database system, and is a distributed database management system with a client / server architecture. It has the advantages of strong function, easy to use, easy to manage, fast running speed, strong security and reliability, and users can use many languages to access the Mysql database, especially with PHP, is more extensive.

Since MySQL is a multi-platform database, its default configuration can be applied in a variety of situations, so further security reinforcements should be performed in our own use environments. As a system administrator of MySQL, we have the responsibility to maintain data security and integrity of the MySQL database system.

The security configuration of the MySQL database must start from two aspects, security and external network security, and we will also briefly introduce some of the questions that should be paid attention to when programming and some tips.

2, internal security

First briefly introduce the MYSQL database directory structure. MySQL is installed, and the data directory and initialization database are created after running mySQL_DB_INSTALL scripts. If we install with the MySQL source package, and the installation directory is / usr / local / mysql, the data directory is usually / usr / local / mysql / var. The database system consists of a series of databases, each database contains a series of database tables. MySQL is the database name to establish a database directory with the database name, and each database table is used as a file name, respectively, and the extensions are MYD, MYI, and FRM, respectively, in the database directory.

The mysql authorization form provides flexible permission control to the database, but if the local user has a read permissions on the library file, the attacker simply packs the database directory, then copy it to the local data directory. Access the stealing database. So the security of the host where MySQL is the most important issue. If the host is not safe, the attacker is controlled, then MySQL's security is not talking. Secondly, the security of the data catalog and data files is the privilege setting problem.

From a MySQL master, some old binary distributions, the properties of the data directory in the 3.21.xx version are 775, which is very dangerous, any local user can read the data directory, so the database file is unsafe. 3.22.XX version of the data directory attribute is 770, which is also dangerous, and the local group users can read and write, so data files are not safe. 3.23. The properties of the version data directory are 700, which is better, only the user who launches the database can read and write the database file to ensure the security of the local data file.

If the user who starts the MySQL database is MySQL, then the following directory and file are secure, please note the data directory and the following properties:

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 incrude

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 sharedrwrwxr-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 Column_Priv.myi

-rw ------- 1 mysql mysql 8778 Feb 27 20:08 Column_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 properties of these files are not like this, use the following two commands:

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

Shell> chmod -r go-rwx / usr / local / mysql / var with root users Start remote services have always been a security trick, because if the service program has problems, the remote attacker is very likely to get the full control of the host. MySQL has made small changes from version 3.23.15, and the service after the default installation is started with mysql users, and the root user is not allowed. If you do not use the root user to start, you must add -user = root parameters (./safe_mysqld -user = root ". Because there is a SQL statement of Load Data Infile and SELECT ... INTO OUTFILE in MySQL, if the root user launches the MySQL server, the database user has a Root user's write permission. However, MySQL still has some restrictions, such as Load Data Infile only read globally readable files, select ... INTO OUTFILE can't overwrite the existing files.

Local log files cannot be ignored, including Shell's logs and MySQL own logs. Some users are convenient for the diagram when they log in or back up the database locally, sometimes with the passwords of the database directly in the command line parameter, 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 history, such as Bash writes to the user directory. If these files are accidentally read, the database's password will leak. The SQL command executed after the user logs in the database will also be recorded in the user directory in the user directory by MySQL. If the database user modifies the database password with the SQL statement, it will also be leaked by the .mySQL_History file. So we don't add a password directly after the SHELL login and backup, but then enter the database password after prompt.

In addition, these two files should also not let it record our operations to prevent it.

Shell> rm .bash_history .mysql_history

Shell> ln -s / dev / null .bash_history

Shell> ln -s / dev / null .mysql_history

On the door, these two commands link the two files to / dev / null, then our operation will not be recorded in these two files.

External network security

After the mysql database is installed, the User Table of the UNIX platform is like this:

Mysql> USE Mysql;

Database change

Mysql> SELECT HOST, User, Password, SELECT_PRIV, GRANT_PRIV from User

-------------- ------------- ----- -------

-------------- ------------- ----- -------

| localhost | root | | Y | Y |

| redhat | root | | Y | Y |

| localhost | | | n | n || redhat | | | n | n |

-------------- ------------- ----- -------

4 rows in set (0.00 sec)

The User Table of the Windows platform is like this:

Mysql> USE Mysql;

Database change

Mysql> SELECT HOST, User, Password, SELECT_PRIV, GRANT_PRIV from User

-------------- ------------- ----- -------

-------------- ------------- ----- -------

| localhost | root | | Y | Y |

|% | Root | | Y | Y |

| Localhost | | | Y | Y |

|% | | | N | n |

-------------- ------------- ----- -------

4 rows in set (0.00 sec)

Let's first see the User table of the UNIX platform. Where redhat is just the machine name I tested, so the NIX platform's MySQL only allows this unit to connect to the database. But the default root user password is empty, so it is a matter of urgent to add a password to the root user. There are three ways to add the database user.

1) Change the root user password with mySQLADMIN command at the shell prompt.

Shell> mysqladmin -uroot password test

In this way, the Mysql database root user is changed to TEST. (TEST is only an example, the password we actually used must not use this volatilized weak password)

2) Modify the password with set password:

MySQL> set password for root @ localhost = password ('test');

At this time, the password of the root user is changed to TEST.

3) Modify the root user password of the USER table directly

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 is also changed to TEST. One of the last commands of the command flush privileges means to enforce the memory authorization form, otherwise or buffer passwords, the illegal users can also log in with the root user and empty ports until the MySQL server is restarted.

We also saw User anonymous users, although it didn't have any permissions under the UNIX platform, but we should delete it: mysql> delete from user where user = '';

The Windows version MySQL's User table has great, and we see that the Host field is% from localhost. Here% means that any host is allowed to connect to the MySQL server, which is very unsafe, causing the attacker to have a machine, we must delete the Host field record:

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

The empty password for the default root user is also modified, three modifications and UNIX platforms.

We noticed that the Host field has all the permissions for the anonymous users of localhost! That is to say, local users use empty usernames and empty passwords to log in to the MYSQL database server to get the highest permission! So anonymous users must delete!

Mysql> delete from user where user = '';

Don't forget to use Flush Privilege to force refreshing memory authorization tables after you can take effect.

The unsafe factor in the default installation of Windows Mysql is too much, we must further configure it after installation!

Mysql's 5 licensing forms: User, DB, Host, Tables_Priv and Column_Priv provide a very flexible security mechanism, starting two statements GRANT and REVOKE from Mysql 3.22.11 to create and delete user permissions, which users can limit which user You can connect the server, where to connect, and what to do after the connection. As a MySQL administrator, we must understand the meaning of the authorization form and how to create users, authorization, and withdrawal with Grant and Revoke, and delete users.

In version 3.22.11, the MySQL authorization mechanism is not perfect, and the new version has a big difference, it is recommended to upgrade to the latest version of MySQL. (The example of this book is sampled by mysql 3.23.49)

Let's first understand the structure of the authorization form.

1) The structure and content of the mysql authorization form:

Mysql> Desc user;

----------------------------------- --------- -------

----------------------------------- --------- -------

| Update_Priv | ENUM ('N', 'Y') | | | N | || Delete_Priv | ENUM ('N', 'Y') | | | N | |

----------------------------------- --------- -------

17 rows in set (0.01 sec)

The User Table is the most important one in the five authorization forms, which lists the users and encrypted passwords that can connect to the server, and it specifies which global (superuser) permission they can. Any permissions enabled in the User table are global permissions and apply to all databases. So we can't give any users to access the permissions of the mysql.user table!

Permission description:

----------- ----------------------------------- ------------------------------------------------

| Permissions Specifier | Column Name | Permission Operation |

----------- ----------------------------------- ------------------------------------------------

| Select | select_priv | Allows access to the table, the SELECT statement that is not accessed by the data sheet is not affected, such as SELECT 1 1 |

----------- ----------------------------------- ------------------------------------------------ | INSERT | INSERT_PRIV | Allows writing operations for the mesh INSERT statement. |

----------- ----------------------------------- ------------------------------------------------

| Update | Update_Priv | Allows the existing record in the table with the UPDATE statement. |

----------- ----------------------------------- ------------------------------------------------

| Delete | delete_priv | Allows existing records in the table with the DELETE statement. |

----------- ----------------------------------- ------------------------------------------------

| CREATE | CREATE_PRIV | Allow new databases and tables. |

----------- ----------------------------------- ------------------------------------------------

| DROP | DROP_PRIV | Allows you to delete existing databases and tables. |

----------- ----------------------------------- ------------------------------------------------

| INDEX | INDEX_PRIV | Allows creation, modify, or delete indexes. |

----------- ----------------------------------- ------------------------------------------------

| ALTER | ALTER_PRIV | Allow the use of the ALTER statement to modify the table structure. |

----------- ----------------------------------- ------------------------------------------------

| Grant | Grant_Priv | Allows yourself to grant your own privileges, including GRANT. |

----------- ----------------------------------- ------------------------------------------------ | Reload | Reload | Allows the overload authorization table, refresh server and other commands. |

----------- ----------------------------------- ------------------------------------------------

| Shutdown | Shudown_Priv | Allows the mysql server to be turned off with the mysqladmin shutdown command. This permission is more dangerous, |

| | | Do not be granted casually. |

----------- ----------------------------------- ------------------------------------------------

| Process | Process_Priv | Allows viewing and terminating threads (processes) that are running by the MySQL server and the query statement being executed |

| | |, Including executing a query statement that changes the password. This permission is dangerous and should not be granted casually. |

----------- ----------------------------------- ------------------------------------------------

| File | file_priv | Allows reading globally readable files and writing files from the server. This permission is dangerous and should not be granted casually. |

----------- ----------------------------------- ------------------------------------------------

Mysql> DESC DB;

----------------------------------- --------- -------

----------------------------------- --------- -------

| DB | CHAR (64) binary | | Pri | | |

INSERT_PRIV | ENUM ('N', 'Y') | | | N | || Update_Priv | ENUM ('N', 'Y') | | | N | |

----------------------------------- --------- -------

13 rows in set (0.01 sec)

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

mysql> desc host;

----------------------------------- --------- -------

----------------------------------- --------- -------

| DB | CHAR (64) binary | | Pri | | |

| CREATE_PRIV | ENUM ('N', 'Y') | | | N | || DROP_PRIV | ENUM ('N', 'Y') | | | N | |

----------------------------------- --------- -------

12 rows in set (0.01 sec)

The HOST table combined with the DB table uses access to the specific host on a better level, which may be better than using DB alone. This table is not affected by the Grant and Revoke statements, so you may find that you are not using it.

Mysql> Desc Tables_Priv;

------------- -------------------------- ----- - ----- --------- -------

------------- -------------------------- ----- - ----- --------- -------

| DB | CHAR (64) binary | | Pri | | |

| | 'Drop', 'REFERENCES', | | | || | 'Index', 'Alter') | | | | |

------------- -------------------------- ----- - ----- --------- -------

8 rows in set (0.01 sec)

TABLES_PRIV table specifies table-level permissions. One permission specified here is suitable for all columns of a table.

Mysql> Desc columns_priv;

------------- --------------------- ---- --- - --------- -------

------------- --------------------- ---- --- - --------- -------

| DB | CHAR (64) binary | | Pri | | |

------------- --------------------- ---- --- - --------- -------

7 rows in set (0.00 sec)

The columns_priv table specifies the column level permissions. The permissions specified here apply to a specific column for a table.

2) MYSQL authorization form operation mechanism

Mysql's access control divided two steps:

a) The server checks if the user is allowed.

b) If the user has the right to connect, the server will also check if each of its requests have enough permissions. For example, the user retrieves a table in the database needs to have the SELECT permission of this database, and the user deletes a table in the database needs to have DROP permissions for this database. The User, DB, and Host Table of the authorized form uses these two steps, Tables_Priv and Column_Priv tables use only the second step (check request). Each authorization table contains the range of permissions to use and determine which permission to grant which permission is granted.

The range column specifies when the permissions in the table are used. Each authorization table entry contains when the user is used to specify when the permissions are applied to a given user from a given host. Other tables contain additional range columns, such as a DB table contains a DB column indicates which database is applied to. Similarly, Tables_Priv and Column_Priv tables contain a range field, narrowing a specific column of a particular table or a table in a database.

Below is some examples of the HOST field of the User table and the USER field:

------------------------ ---------- ----------------------------------------------

| Host Value | User Value | Matching Connections |

------------------------ ---------- ----------------------------------------------

| 'x.y.z' | 'test' | TEST users 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' | TEST users can connect to the database from any host |

------------------------ ---------- ----------------------------------------------

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

------------------------ ---------- ----------------------------------------------

| '% .y.z' | 'test' | TEST users can connect to any host from Y.Z domain |

------------------------ ---------- -------------------------------------------- | 'XY % '|' Test '| TEST users can connect to hosts from XYNET, XYCOM, XYEDU |

------------------------ ---------- ----------------------------------------------

| '192.168.1.1' | 'test' | TEST users can connect from the IP address to 192.168.1.1 host connection database |

------------------------ ---------- ----------------------------------------------

| 192.168.1.% '|' Test '| TEST users can connect to any host from Class C subnet 192.168.1 |

------------------------ ---------- ----------------------------------------------

| '192.168.1.0/255.255.255.0' | 'Test' | 同上 |

------------------------ ---------- ----------------------------------------------

SQL's character string configuration% represents matching any character, can be 0 characters, wildcard _ represents matching a character.

Permissions column indicates where the user has permission specified in the range column. This table uses the authority name of the GRANT statement. There is a significant connection to the GRANT statement for the vast majority of the names in the USER, DB and HOST tables. If select_priv corresponds to SELECT permissions.

3) Example of authorization form

GRANT is used to add users and create permissions, and revoke is used to delete user permissions.

Here are some examples of using GRANT to increase users and create permissions:

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

This sentence adds a local TEST user with all permissions, the password is TEST. The *. * In the ON clause means "all databases, all tables". With grant option indicates that it has GRANT permissions.

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 sentence is a Test1 user, the password is Test, but it can only connect from Class C subnet 192.168.1, with SELECT, INSERT, UPDATE, DELETE, CREATE, DROP operation permission to the TEST library.

Creating permissions with a GRANT statement does not need to be manually refreshed, because it has been automatically refreshed. Creating permissions to users can also modify the authorization form by direct modification:

Mysql> Insert Into User Values ("Localhost", "Test", Password ("Test"), "Y", "Y", "Y", "Y", "Y", "Y", "Y", "y", "y", "y", "y", "y", "y");

mysql> flush privileges;

The two sentences are the same as the first GRANT, but also a local TEST super user. We see more convenient with GRANT, and we don't need Flush Privileges.

Mysql> INSERT INTO USER (Host, User, Password) Values ("192.168.1.055.255.255.0", "test1", password ("test"));

Mysql> Insert Into DB Values ("192.168.1.055.255.255.0", "Test", "Test1", "Y", "Y", "Y", "Y", "Y", " N "," n "," n "," n ")

mysql> flush privileges;

The effects of these three sentences and the second sentence above GRANT are also the same, it also adds a TEST1 that can only be connected from Class 19 subnet 192.168.1, SEST library with SELECT, INSERT, UPDATE, DELETE, CREATE, DROP operation permission. The user, the password is TEST.

To cancel a user's permissions, use the revoke statement. The syntax of the revoke is very similar to the GRANT statement. In addition to the use from from from, there is no Identified By and the Grant Option clause, the following is an example of deleting user permissions with Revoke:

MySQL> Revoke All on test. * from test1@'192.168.1.0/255.255.255.0 ';

This revoke will undo the permissions created above, but Test1 users are not deleted, and must be manually removed from the USER table:

Mysql> Delete from user where user = 'test1';

mysql> flush privileges;

In this way, Test1 users completely deleted.

These are just a simple use of the MySQL authorization form. For more detailed information, please see the manual provided by mysql.

3, some questions that the programming needs attention

No matter which programming language written to connect the MySQL database, there is a guideline that never believe in the data submitted by the user!

For digital fields, we want to use query statements: select * from table where id = '234', do not use SELECT * from Table WHERE ID = 234 such query statements. MySQL automatically converts the strings into numeric characters and removes numeric characters. If the data submitted by the user passes mysql_escape_string, we can completely eliminate SQL INJECT attacks, please refer to the article below for SQL INJECT Attack: http://www.spidynamics.com/papers/sqlinjectionWhitePaper.pdf

http://www.ngssoftware.com/papers/advanced_sql_injection.pdf

Various programming languages, the problem:

1) All web programs:

a) Try to test the possible errors in the web form input single quotes and dual quotes and find out the reason.

b) Modify the% 22 ('""),% 23 (' # '), and% 27 (' ') of the URL parameter belt.

c) Variables for numeric fields, our application must be strict inspections, otherwise it is very dangerous.

d) Check if the data submitted by the user exceeds the length of the field.

e) Do not give your own program to connect to the database too much access.

2) PHP:

a) Check whether the data submitted by the user is processed before the query is processed, and the MYSQL C API is provided after PHP 4.0.3, which provides mysql_escape_string ().

3) MySQL C API:

a) Check if the query string uses the mysql_escape_string () API call.

4) MySQL :

a) Check if the query string uses Escape and Quote processing.

5) Perl DBI:

a) Check if the query string uses the quote () method.

6) Java JDBC:

a) Check if the query string has used a PreparedStateMent object.

4, some tips

1) If you accidentally forget the MySQL root password, we can add the parameter -skip-grant-tables when starting the MySQL server (./safe_mysqld --skip-grant-tables ", This way we can log in directly to the MySQL server, then modify the root user password, restart MySQL can be logged in with a new password.

2) Plus -skip-show-database when starting the MySQL server, making a general database user cannot browse other databases.

3) Plus -chroot = path parameters when starting the MySQL server, allowing the MySQLD daemon to run in the Chroot environment. Such SQL statements load data infile and select ... INTO OUTFILE are limited to read files under Chroot_path. Here is to pay attention to it. After mysql starts, a mysql.sock file will be created, and the default is in the / TMP directory. After using Chroot, MySQL will create a mysql.sock file in Chroot_Path / TMP, and if there is no chroot_path / tmp directory or launching MySQL without this directory write permission, Mysql will start failed. For example, we add -chroot = / usr / local / mysql / start-up parameters, then build a / usr / local / mysql / tmp directory that starts MySQL users, of course we can also use - Socket = PATH To specify the path to the mysql.sock file, but this PATH must be in chroot_path. 4) Plus -log-slow-queries = file parameters when starting the MySQL server, so mysqld will execute the SQL command to write the File file. If you don't specify = file, MySQLD will be written to hostname-slow.log under the data directory by default. If only filename is specified, no path is specified, then MySQLD will write FileName to the data directory. We can find the execution time long query statement through this log file, then optimize it to reduce the burden on the MySQL server.

5) If we only need this machine to use MySQL service, then we can also add -skip-networking boot parameters to make MySQL misselect any TCP / IP connections, increasing security. (Very recommended)

6) MYSQL more mysqld startup options, please see mysql manual 4.16.4 mysqld Command-Line Options

References

Mysql manual

http://www.mysql.com/documentation/index.html)

子 's mysql administrator guide (

http://clyan.hongnet.com/index.html)

Access granted

http://www.devshed.com/server_side/mysql/access)

转载请注明原文地址:https://www.9cbs.com/read-92045.html

9cbs

New Post(0)