Mysql Security Guide

xiaoxiao2021-03-06 67

Author: Yan Zi Source: Yan Zi studio MySQL as a system administrator, you are responsible for maintaining data security and integrity of your MySQL database system. This paper mainly introduces how to establish a secure Mysql system, from both perspectives within the system and external networks, to provide you with a guide. This article mainly considers the following security:

· Why is security important, should you prevent those attacks?

· Risk facing the server (internal security), how to deal with?

· Connect the client's client risk (external security), how to deal?

MySQL administrators are responsible for ensuring the security of database content, allowing these data records that can only be accessed by those properly authorized, which involves internal security and external security of the database system. Internal security is concerned about the file system level, that is, prevents the mysql data directory (DATADIR) attacking people (legal or stealing) that have accounts in the server host. If the permissions of the data directory content are over-granted, each person can simply replace files corresponding to those database tables, then ensure that the authorization table settings accessed through the network are correct, which is meaningless. External security is concerned about the customer's client from the outside through the network, ie, protect the MySQL server from an attack from the connection to the server through the network. You have to set the MySQL authorization table such that they do not allow access to the server-managed database content unless the valid username and password are provided. Let's take a detail how to set up the file system and authorization menu mysql to implement the two-level security of MySQL. I. Internal Security - Guaranteed that the security MySQL server accessed by the data directory provides a flexible permission system by an authorization table in the mysql database. You can set the contents of these tables, allow or reject customers to access the database, which provides a security means that you prevent unauthorized network access to your database attack, however, if other users on the host can directly access the data directory content, establish a pair Good security from the network access to you unless, unless you know that you are the only user who logs in to the MySQL server to run the host, you need to care about the possibility of access to the data directory on this machine. The following is what you should protect:

· Database file. Obviously, you have to maintain the private use of the database of the server. The database owner usually and should consider the security of the database content, even if they don't want, the database content will be exposed when the database content is exposed, not the security of the poor data directory.

· Log file. General and update logs must be secure because they contain query text. Anyone who has access to the log file can monitor the operations through the database.

The security of log files must be considered is, such as

GRANT and

Set Password et al. Also recorded, the general and update logs contain texts with sensitive queries, including passwords (MySQL use password encryption, but it is used for future connection after completion settings. Set a password process design Icon

GRANT or

SET Password and other queries, and these queries are recorded in the log file in normal text). If an attacker is like the read permissions of the day file, just run on the log file.

GREP is looking for

GRANT and

Password et al. Discover sensitive information.

Obviously, you don't want other users on the server host to have a copy of the database directory file because they can override your status files or database table files, but read permissions are also very dangerous. If a database table file can be read, steal the file and get the mysql itself, it is very troublesome, why? Because you have to do the following: • Install your own "special" MySQL server on the server host, but there is a port, socket, and data directory that is different from the official server version.

·run

MySQL_INSTALL_DB initializes your data directory, which gives you permission to access your server as a MySQL root user, so you have full control over the server access mechanism, which also creates a TEST database.

· Will correspond to the Test directory you want to steal the table file to copy to the database directory of your server.

· Start your server. You can access the database table at will, show tables from test displays the copy of your stolen table, select * displays all of them all of the content.

· If you are really vicious, disclose permissions to any anonymous users of your server so that anyone can connect to the server from anywhere to access your TEST database. You will now publish the stolen database table.

Consideration, from the opposite angle, do you want others to do this? of course not! You can determine if your database contains unsafe files and directories by performing the ls -l command under the database. Find files and directories with Groups and "Other User" permissions. Below is a part of an unsafe data directory list:

% ls -l

Total 10148

DRWXRWXR-X 11 mysqladm Wheel 1024 May 8 12:20.

DRWXR-XR-X 22 root Wheel 512 May 8 13:31..

DRWX ------ 2 mysqladm mysqlgrp 512 Apr 16 15:57 menagerie

DRWXRWXR-X 2 mysqladm wheel 512 Jan 25 20:40 MySQL

Drwxrwxr-x 7 mysqladm Wheel 512 Aug 31 1998 SQL-BENCH

DRWXRWXR-X 2 mysqladm Wheel 1536 May 6 06:11 TEST

DRWX ------ 2 mysqladm mysqlgrp 1024 May 8 18:43 TMP

....

As you can see, some databases have the right permissions, while others are not. This case is the result after a period of time. Less limitations are set by the earlier versions of the updated version than the updated version (note more restricted directory Menageria and TMP have a closer date). MySQL Current Version Make sure these files can only be read by users running the server. Let us fix these privileges so that only server users can access them. Your main protection tool comes from the tools for setting files and directory belongings and patterns provided by UNIX file system itself. Here is what we have to do:

· Enter this directory

% CD DATADIR

• Set all file owners in the data directory to have the account for running the server (you must do this with root). Use mysqladm and mysqlgrp as the username and group name of the account using Mysqladm and MySQLGRP. You can change one of the following commands:

# chown mysqladm.mysqlgrp.

# Find. -follow -type d -print | xargs chown mysqladm.mysqlgrp

· Set your data directory and database directory to make them only read by MySQLADM, which prevents other users from accessing your database directory. You can use one of the following commands to run as root or mysqladm.

% chmod -r GO-RWX.

% Find. -follow -Type D -Print | Xargs Chmod Go-RWX

· The owner and mode of the data directory content are mysqladm settings. Now you should guarantee that you always run the server at a mysqladm user, because it is now uniquely available users (in addition to root).

After completing these settings, you should eventually get the following data directory permissions:

% ls -l

Total 10148

DRWXRWX --- 11 mysqladm mysqlgrp 1024 May 8 12:20.

DRWXR-XR-X 22 root Wheel 512 May 8 13:31..

DRWX ------ 2 mysqladm mysqlgrp 512 Apr 16 15:57 menagerie

DRWX ------ 2 mysqladm mysqlgrp 512 Jan 25 20:40 mysql

DRWX ------ 7 mysqladm mysqlgrp 512 Aug 31 1998 SQL-Bench

DRWX ------ 2 mysqladm mysqlgrp 1536 May 6 06:11 TEST

DRWX ------ 2 mysqladm mysqlgrp 1024 May 8 18:43 TMP

....

Second, external security - to ensure that the security system security system accessed by network is flexible, which allows you to set user permissions in a variety of different ways. In general, you can use standard SQL statements Grant and Revoke statements, they modify you to control customer access, however, you might consist of Mysql that does not support these statements (before 3.22.11 these statements Careless to work), or you find that user permission does not work in the way you want. For this situation, understand how the Mysql authorization form is helpful, how to use them to determine access, such an understanding allows you to increase, delete or modify user rights by direct modifying an authorization table, it also allows you to check these tables Diagnostic permission problem. For how to manage user account, see "Mysql User Management". For the Detailed Description of Grant and Revoke statements, see "MySQL Reference Manual". 2.1 The structure of the mysql authorization table is controlled by the client of the MySQL database through the network connection server. These tables are located in the MySQL database and initialize (run the mysql_install_db script) during the first installation of MySQL. The license list has 5 tables: User, DB, Host, Tables_Priv and Column_Priv.

Table of contents 1 user, db, and host configuration access authorization table scope columns userdbhostHostHostHostUserDbDbPasswordUser database / table columns Alter_privAlter_privAlter_privCreate_privCreate_privCreate_privDelete_privDelete_privDelete_privDrop_privDrop_privDrop_privIndex_privIndex_privIndex_privInsert_privInsert_privInsert_privReferences_privReferences_privReferences_privSelect_privSelect_privSelect_privUpdate_privUpdate_privUpdate_privFile_privGrant_privGrant_privGrant_privProcess_privReload_privShutdown_priv Table 2 tables_priv permission and columns_priv table structure to access the lien authority scope columns tables_privcolumns_privHostHostDbDbUserUserTable_nameTable_nameColumn_name Table_privColumn_priv authorization table columns have the following purposes:

User table

The USER table lists users who can connect to the server and their passwords, and it specifies which global (superuser) permission they can. Any permissions enabled in the User table are global permissions and apply to all databases. For example, if you enable Delete permissions, users listed here can delete records from any table, so we must carefully consider before you do this.

· DB table

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.

Host table

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.

· TABLES_PRIV table

Tables_priv table Specifies a table-level permissions where a permission specified here is suitable for all columns of a table.

· Column_priv table

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

In the "No GRANT Set User" section, we discuss how the GRANT statement works to modify these games, and how you can express the same effect by directly modifying authorization. Tables_priv and columns_priv tables are introduced in MySQL 3.22.11 (at the same time as the GRANT statement). If you have an earlier version of MySQL, your MySQL database will have only USER, DB and HOST tables. If you upgrade from the old version to 3.22.11 or update, there is no TABLES_PRIV and Column_Priv table, run the mysql_fix_privileges_tables script to create them. MySQL doesn't have a ROWS_PRIV table because it does not provide record-level permissions, for example, you can't limit users who contain a particular column value in the table. If you do need this ability, you must be programmed with application. If you want to perform a suggested record-level lock, you can use the get_lock () function. The authorization table contains two columns: determines where a range of permissions uses the range and determines which permission to grant. 2.1.1 Authorization Table Scope List Delegation Table 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. 2.1.2 Authorization Table Right Liege Authorization Table also includes the right limit column, which points to the user who specifies the user specified in the range column. The permissions supported by MySQL are shown in the following table. 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. 2.1.3 Database and Significance The following permissions are applied to the database and table operations. · Alter

Allows you to use the ALTER TABLE statement, which is actually a simple first level, you must be made by other permissions, this way you want to do what to do with the database.

· Create

Allows you to create a database and table, but you don't allow you to create an index.

· Delete

Allows you to delete existing records from the table.

· DROP

Allow you to delete (discard) databases and tables, but do not allow deletion of indexes.

INDEX

Allows you to create and delete an index.

· References

Not currently.

· SELECT

Allow you to retrieve data from the table using the SELECT statement. The SELECT statement that does not involve the table is unnecessary, such as select now () or SELECT 4/2.

· Update

Allows you to modify existing records in the table.

2.1.4 Administration Permissions The following permissions are used to control manageable operations for the operation of the server or user authorization capabilities.

· File

Allow you to tell the server to read or write the files on the server host. This permission should not be awarded, it is very dangerous, see "avoiding the risk of authorization form". The server is really carefully to maintain this permission within a certain range. You can only read the files that anyone can read. The file you are writing must not be an existing file, which prevents you from overriding the server to override important files, such as / etc / passwd, or data directory that belongs to others.

If you authorize File permissions, make sure you don't run the server with UNIX's root user, because root can create new files anywhere in the file system. If you run the server with a non-privileged user, the server can only create a file in a directory that users can access.

· GRANT

Allows you to grant your own permissions, including Grant.

· Process

Allows you to view information about the thread (process) in the server by using the Show Process statement or the mysqladmin process command. This permission also allows you to kill threads with a kill statement or mysqladmin kill command.

You can always see or kill your own thread. Process permissions give you the ability to do these things to any thread. · Reload

Allows you to perform a large number of server management operations. You can send a flush statement, you can also refer to commands of mysqladmin's Reload, Refresh, Flush-hosts, flush-logs, flush-privileges, and flush-tables.

· Shutdown

Allows you to close the server with MySQLADMIN Shutdown.

In the User, DB, and Host tables, each permission is specified in a separate column. These columns are declared as an enum ("n", "y") type, so the default value of each right is "N". Permissions in Tables_Priv and Columns_priv are represented in a set, which allows permissions to be specified in any combination with a single column. These two agents are updated more than three tables, which is why they use more effective reasons. (It is possible in the future, the user, the db, and the host table is also expressed in a set type.) Table_priv column in the Tables_PRIV table is defined: set ('select', 'INSERT', 'UPDATE', 'DELETE', ' CREATE ',' DROP ',' GRANT ',' REFERENCES ',' INDEX ',' ALTER ') COLUMN_PRIV columns in coloums_priv tables are defined: set (' select ',' INSERT ',' UPDATE ',' References ') The permissions permission specific to the table is much meaningful because there is less authority. For example, you can create a table, but you can't create an isolated column. The User table contains some columns: file_priv, process_priv, reload_priv, shutdown_priv, in other authorization forms. These permissions are used to allow the server to perform an operation with any particular database or table. If you allow a user to turn off the database according to what the current database is turned off. 2.2 How to Control Customer Access When you use MySQL, customer access control has two phases. The first phase occurs when you try to connect the server. The server finds the User table to see if it can find an entry to match your name, you are being connected to the host and the password you provide. If there is no match, you cannot connect. If there is a match, establish a connection and continue the second phase. At this stage, for each query you sent, the server checks the license table see if you have enough permissions to perform the query, and the second phase continues to your end with the server dialogue. This section details the principle of the MYSQL server to match the connection request or query of the authorization table entry, which includes the type of legal value in the authorized table range column, combined with the authority information in the form of the authorization table and the entry The order of the inspection. 2.2.1 Score content Some range columns require text values, but most of them allow wildcards or other special values.

Host

A host column value can be a host name or an IP address. Value Localhost means the local host, but it only matches when you use a localhost hostname, not when you are using the host name. If your local host name is pit.snake.net and there is two records to you in the USER table, one has a host value or localhost, and the other has pit.snake.net, which has a record of localhost. You match when you connect to LocalHost, which matches only when connecting pit.snake.net. If you want customers to connect in two ways, you need to have two records in the User table.

You can also specify the Host value with wildcard. You can use SQL mode characters "%" and "_" and have the same meaning as the LIKE operator is used in a query (not allowing the regex operator). SQL mode characters can be used for host names and IP addresses. If the% Wisc.edu matches the host in any Wisc.edu domain, and% .edu matches the host of any education college. Similarly, 192.168.% Matches the host of 192.168 B net network, and 192.168.3.% Matches any host in 192.168.3 C. subnet. % Value matches all hosts and can be used to allow a user to connect from anywhere. A blank Host value is equivalent to%. (Exception: In the DB table, a blank Host value is "further check the HOST table", which is introduced in "Query Access Verification".)

From MySQL 3.23, you can also specify a IP address that indicates network mask that is used for network addresses, such as 192.168.128.0/17 Specifies a 17-bit network address and match its IP address to be 192.168.128 first 17 Any host.

User

The username must be text or blank. A blank value matches any user. % As a USER value does not mean blank, the opposite matches the% name on a literal, which may not be what you want.

When a coming connection is verified by the USER table, the record contains a blank user value, and the customer is considered an anonymous user.

· Password

The password value can be empty or non-empty, and the wildcard is not allowed. An empty mouth order does not mean matching any password, which means that the user must not specify a password.

The password is stored in a crypt value, not a literal text. If you store a password on the PASSWORD column, the user will not be able to connect! The GRANT statement and the mysqladmin password command automatically encrypts the password, but if you use a command such as INSERT, Replace, Update, or Set Password, you must use Password ("new_password") instead of simple "new_password" to specify your password.

· DB

In columns_priv and tables_priv tables, the DB value must be a real database name (on the field), not allowed to mode and blank. In DB and Host, the DB value can specify or use SQL mode character '%' or '_' to specify a wildcard. One '%' or blank matches any database.

· Table_name, column_name

The values in these columns must be a table or column name, not allowed to mode and blank.

Some range columns are considered sensitive by the server, and the rest is not. These principles are summarized in the table below. Special note that the table_name value is always considered sensitive, even if the case sensitive sensitivity of the table name in the query is dependent on the file system of the host running (UNIX is sensitive, and Windows is not).

Table 3 Samples of Sensitive Sensitivity in Deep Items HostuserPasswordDbtable_nameColumn_name Sensitive Noyesyesyesyesno

2.2.2 Query Access Verification Every time you send a query, the server checks if you have enough permissions to execute it, it checks in the order of User, DB, TABLES_PRIV, and Column_Priv, know that it makes appropriate access or all Table and nothing. More specifically:

· Server Check the User Table Match the record you start to connect to see what global permissions you have. If you have enough and they are enough, the server performs it.

· If your global permissions are not enough, the server is looking for you in the DB table and adds the permissions in the record to your global permissions. If the result is sufficient to check the query, the server executes it. · If your global and database-level combination is not enough, the server continues to find, first in the Tables_PRIV table, then columns_priv table.

• If you still have no permissions after checking all the tables, the server rejects you attempts to perform queries.

Use Boolean computing terms, authorization lists are used by servers: user or tables_priv or columns_priv You may doubt what the previous description only references 4 authorization forms, but actually 5. In fact, the server is checking access: user or (db and host) or Tables_Priv or columns_priv The first simpler expression is because the Host table is not affected by the GRANT and REVOKE statements. If you always use Grant and Revoke to manage user permissions, you never need to consider the Host table. But your working principle you use it:

· When the server checks the database level permissions, it looks for the DB table for the customer. If the Host column is empty, it means "check the Host table to find which host can access the database."

• The server finds the same DB column value as the record from the DB table in the HOST table. If there is no Host Record Matching the client host, the database level permissions are not granted. If any of these records do have a HOST column value of the connected client host, the DB table record, and the HOST table record combine to generate customer database level permissions.

However, the permission is combined with a logical and (with), which means that the customer does not have this permission unless a given permissions are in both tables. In this way, you can grant a basic permissions set in the DB table and then use the Host table to selectively disable them for a particular host. If you can allow all hosts in your domain to access the database, but close the database permissions of the hosts in the unsafe zone.

The previous description has no doubt that the access check sounds a fairly complicated process, especially if you think the server permission check, however, this process is very fast, because the server is actually not from the authorization table The query lookup information. Instead, it reads the contents of the table in the launch, and then verifies that the query is a copy of the memory. This greatly enhances the performance of access check operations. But there is a very obvious side effect. If you directly modify the content of the authorization form, the server will not know the change in privileges. For example, if you add a new user to the USER table with a INSERT statement to add a new user, the user named in the record will not connect to the server. This is a very confused thing for administrators (sometimes the experienced older). The solution is very simple: tell the server overload authorization form after you change them, you can send a Flush Privilege or execute mysqladmin flush- Privileges (or if you have an old version that doesn't support flush-privilege, with mysqladmin reload.). 2.2.3 Range Column Match Sequence The MYSQL server is a record in a specific way to authorize the logs, and then browsing the records from the sequential browsing. The first match found determines the records used. Understanding the ordering order of mysql use is important, especially for the USER table. When the server reads the contents of the USER table, it sorts the record according to the value in the Host and USER columns, the Host value determines the effect (the same Host value is rowed together, and then sorted according to the USER value). However, sorting is not a step (sorted by words), it is just part of. It is necessary to keep in mind that the words on the literal are preferred. This means that if you are connecting to the server from the client.your.net connection server and Host has both values, the first first election. Similarly,%. Your.NET is preferred in% .NET, then%. This is also like this to match the IP address. In short, the more specific, the more preferred. See the instance of the appendix of this article. 2.3 Avoiding Authorization Table Risks This session This section introduces some of the prevention measures in your authorization, as well as the risk of optional choices. In general, you have to "" to grant superuser privileges, ie not enabled permissions in the entries in the USER table, and use other licensing forms to limit user rights to databases, tables, or columns. Permissions in the User table allow you to affect your server operation or access any table in any database. Don't grant permissions to the MySQL database. A user with authorized meter database privilege may modify the table to get permissions to any other database. Granting allowing a user to modify the permissions of the mysql database table, also actually give the user with a global GRANT permission. If the user can modify the table directly, this is also equivalent to any GRANT statement that you can imagine. File permissions are particularly dangerous, don't authorize it easily. The following is something that people who have File permissions can be done: create Table etc_passwd (pwd_entry text); load data infile "/ etc / passwd" INTO TABLE ETC_PASSWD;

SELECT * from ETC_PASSWD;

After issuing these statements, the user already has the content of your password file. In fact, any public readable files on the server can be accessed through users with users with file permissions. File privileges can also be utilized to hazard the database on systems that do not set sufficient file permissions. This is why you should set the data directory that can only be read by the server. If the file corresponding to the database table can be read by anyone, not only the user of the user server account readable, any user with file permissions can also be connected to the network and read them. Below demonstrate this process:

· Create a table with a longblob column: user test;

Create Table TMP (B Longblob);

· Use this table to read each content corresponding to the database table file you want to steal, then write the table content into a file in your own database:

Load Data Infile "./ Porther_db/x.frm" INTO TABLE TMP