MySQL security guide_mysql

Author: as a MySQL system administrator, Yan Zi is responsible for maintaining the data security and integrity of your MySQL database system. This article mainly introduces how to build a secure MySQL system and provides you with a guide from both internal and external networks. This article focuses on the following security-related issues: why is security very important? you should be the author: Yan Zi

As a MySQL system administrator, you have the responsibility to maintain the data security and integrity of your MySQL database system. This article mainly introduces how to build a secure MySQL system and provides you with a guide from both internal and external networks.

This article focuses on the following security issues:

Why is security very important? what attacks should you guard against?
What are the risks (internal security) faced by servers?
How does one deal with the client risk (external security) of the server?
The MySQL administrator is responsible for ensuring the security of the database content, so that these data records can only be accessed by those authorized users, which involves the internal and external security of the database system.
Internal security concerns the file system level, that is, preventing MySQL data directories (DATADIR) from being attacked by persons (legal or stolen) with accounts on the server host. If the permissions on the data directory content are excessively granted so that everyone can simply replace the files corresponding to those database tables, it makes no sense to make sure that the authorized tables that control customer access over the network are correctly set.

External Security concerns customers who connect to the server from the external network, that is, to protect the MySQL server from attacks from the connection to the server through the network. You must set the MySQL authorization table so that they are not allowed to access the database content managed by the server, unless a valid user name and password are provided.

The following describes in detail how to set up the file system and the authorization table mysql to achieve two levels of MySQL security.


I. internal security-ensures the security of data directory access
The MySQL server provides a flexible permission system through the authorization table in the MySQL database. You can set the content of these tables to allow or deny access to the database. this provides a security means to prevent unauthorized network access from attacking your database, however, if other users on the host can directly access the data directory content and establish good security for accessing the database through the network, unless you know that you are the only user logging on to the MySQL server to run the host, you need to be concerned about the possibility that other users on this machine can access the data directory.

The following content should be protected:

Database files. Obviously, you need to maintain the private usage of the database managed by the server. Database owners usually consider the security of database content, even if they do not want to, they should also consider the openness of database content, rather than exposing this content through poor security of data directories.
Log file. Generally, logs must be updated to ensure security because they contain query text. Anyone with access to log files can monitor operations performed by the database.
The log file security is also documented in queries such as GRANT and set password. Generally, the log updates contain sensitive query text, including passwords (MySQL uses PASSWORD encryption, however, it is used for subsequent connection establishment only after the settings have been completed. The process of setting a PASSWORD is designed to be a query such as GRANT or set password, and these queries are recorded in the log file as common text ). If an attacker has the same read permission as a daily file, he only needs to run grep on the log file to find sensitive information by searching for words such as GRANT and PASSWORD.
Obviously, you do not want other users on the server host to have the write permission for Database Directory files, because they can rewrite your status files or database table files, but the read permission is also dangerous. If a database table file can be read and the file is stolen and MySQL itself is obtained, it is also troublesome to display the table content in plain text. why? Because you need to do the following:

Install your own "special" MySQL server on the server host, but there is a port, socket, and data directory different from the official server version.
Run mysql_install_db to initialize your data directory, which grants you the permission to access your server as the MySQL root user. Therefore, you have full control over the server access mechanism and it also creates a test database.
Copy the table files you want to steal to the test directory in the database directory of your server.
Start your server. You can access database TABLES at will. show tables from test shows that you have a copy of a stolen table, and SELECT * shows all the contents of any of them.
If you are really vicious, publish the permission to any anonymous user on your server so that anyone can connect to the server from any address to access your test database. You have now published the stolen database tables.
Consider, from the opposite perspective, do you want others to treat you like this? Of course not! You can run the ls-l command in the database directory to check whether your database contains insecure files and directories. Search for files and directories with "group" and "other users" permissions. The following is a list of insecure data directories:

　
% Ls-l
Total 10148
Drwxrwxr-x 11 mysqladm wheel 1024 May 8.
Drwxr-xr-x 22 root wheel 512 May 8 ..
Drwx ------ 2 mysqladm mysqlgrp 512 Apr 16 menagerie
Drwxrwxr-x 2 mysqladm wheel 512 Jan 25 mysql
Drwxrwxr-x 7 mysqladm wheel 512 Aug 31 1998 SQL-statements
Drwxrwxr-x 2 mysqladm wheel 1536 May 6 test
Drwx ------ 2 mysqladm mysqlgrp 1024 May 8 tmp
....


As you can see, some databases have the correct permissions, while others are not. This example is the result after a period of time. For more limited permissions, you can set them on the server of an earlier version that is less restrictive than the updated version (note that the more restrictive directories menageria and tmp both have a relatively recent date ). The current MySQL version ensures that these files can only be read by users on the running server.

Let's modify these permissions so that only server users can access them. Your primary protection tool comes from the setup file and directory owner and mode tools provided by the UNIX file system itself. The following is what we need to do:

Enter this directory
% Cd DATADIR

Set the owner of all files in the data directory to be owned by the account used to run the server (you must perform this step as root ). In this document, mysqladm and mysqlgrp are used as the user name and group name of the account. You can use one of the following commands to change the owner:
# Chown mysqladm. mysqlgrp.

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

Set the mode of your data directory and Database Directory so that they can only be read by mysqladm, which prevents other users from accessing the contents of your Database Directory. You can run the following command 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 set to mysqladm. Now you should ensure that you always run the server with the mysqladm user, because now this is the only user with the permission to access the database Directory (except 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.
Drwxr-xr-x 22 root wheel 512 May 8 ..
Drwx ------ 2 mysqladm mysqlgrp 512 Apr 16 menagerie
Drwx ------ 2 mysqladm mysqlgrp 512 Jan 25 mysql
Drwx ------ 7 mysqladm mysqlgrp 512 Aug 31 1998 SQL-statements
Drwx ------ 2 mysqladm mysqlgrp 1536 May 6 test
Drwx ------ 2 mysqladm mysqlgrp 1024 May 8 tmp
....



II. external security-ensures network access security
The MySQL security system is flexible and allows you to set user permissions in multiple ways. Generally, you can use the standard SQL statement GRANT and REVOKE to modify the authorization table for controlling customer access. However, you may be using an old MySQL version that does not support these statements (these statements did not work before 3.22.11), or you may find that user permissions do not seem to work in the way you want. In this case, it is helpful to understand the structure of the MySQL authorization table and how the server uses them to determine access permissions, this understanding allows you to add, delete, or modify user permissions by directly modifying the authorization table. It also allows you to diagnose permission issues when checking these tables.

For details about how to manage user accounts, see MySQL User management. For details about GRANT and REVOKE statements, see MySQL Reference Manual.

2.1 Structure and Content of the MySQL authorization table
Access to the MySQL database by customers who connect to the server over the network is controlled by the authorization table content. These tables are located in the mysql database and Initialized during the first MySQL installation (run the mysql_install_db script ). There are five authorization tables: user, db, host, tables_priv, and columns_priv.

Table 1 user, db, and host authorization table structure
Access range column

User db host
Host
User Db
Password User
Database/table permission columns
Alter_priv
Create_priv
Delete_priv
Drop_priv
Index_priv
Insert_priv
References_priv
Select_priv
Update_priv
File_priv Grant_priv
Grant_priv
Process_priv
Reload_priv
Shutdown_priv
　
Table 2 tables_priv and columns_priv belong to the permission table structure

Access range column
Tables_priv columns_priv
Host
Db
User
Table_name
Column_name
Permission column
Table_priv Column_priv

The authorization table has the following functions:

User table
The user table lists the users that can connect to the server and their passwords, and specifies which global (super user) permissions they have. All permissions enabled in the user table are global permissions and apply to all databases. For example, if you have enabled the DELETE permission, the users listed here can DELETE records from any table, so you should consider it carefully before doing so.
Db table
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.
Host Table
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.
Tables_priv table
The tables_priv table specifies table-level permissions. the specified permission applies to all columns in a table.
Columns_priv table
The columns_priv table specifies the column-level permission. The specified permission applies to specific columns of a table.
In the "do not GRANT user settings" section, we will discuss how the GRANT statement works for modifying these tables, and how you can express the same effect by directly modifying the authorization.

The tables_priv and columns_priv tables are introduced in MySQL 3.22.11 (the same as the GRANT statement ). If you have an earlier version of MySQL, your mysql database will only have user, db, and host tables. If you upgrade from an earlier version to 3.22.11 or update without the tables_priv and columns_priv tables, run the mysql_fix_privileges_tables script to create them.

MySQL does not have a rows_priv table because it does not provide record-level permissions. for example, you cannot restrict the rows in a table that contain specific column values. If you really need this capability, you must use application programming to provide it. If you want to execute the recommended record-level lock, you can use the GET_LOCK () function.

The authorization table contains two types of columns: the range column that determines when a permission is applied and the permission column that determines which permission is granted.

2.1.1 authorization table range column
Authorize the table range column to specify when the 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.

2.1.2 authorization table permission column
The authorization table also contains the permission column, which specifies the permissions of the user specified in the range column. The following table lists the permissions supported by MySQL. 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.

2.1.3 database and table permissions
The following permissions apply to database and table operations.

ALTER
Allow you to use the alter table Statement. this is actually a simple first-level permission. you must have other permissions. this depends on what operations you want to perform on the database.
CREATE
You can create databases and tables, but cannot create indexes.
DELETE
Allows you to delete existing records from a table.
DROP
You can delete (discard) databases and tables, but cannot delete indexes.
INDEX
Allows you to create and delete indexes.
REFERENCES
No.
SELECT
You can use the SELECT statement to retrieve data from a table. It is unnecessary for SELECT statements that do not involve tables, such as select now () or SELECT 4/2.
UPDATE
Allow you to modify existing records in the table.
2.1.4 manage permissions
The following permissions apply to administrative operations that control server or user authorization.

FILE
Allows you to tell the server to read or write files on the server host. This permission should not be granted at will. it is very dangerous. for details, see "avoiding the risk of authorization table ". The server is indeed cautious in using this permission within a certain range. You can only read files that anyone can read. The file you are writing must not be an existing file, which prevents you from forcing the server to rewrite important files, such as/etc/passwd or the data directory of another user's database.
If you authorize FILE permission, make sure that you do not run the server as a UNIX root user, because root can create new files anywhere in the FILE system. If you run the server as a non-privileged user, the server can only create files in the directories accessible to users.

GRANT
Allow you to GRANT your permissions to others, including GRANT.
PROCESS
You can use the show process statement or mysqladmin process command to view information about the running threads (processes) on the server. This permission also allows you to use the KILL statement or mysqladmin kill command to KILL the thread.
You can always see or kill your own thread. The PROCESS permission gives you the ability to perform these tasks on any thread.

RELOAD
Allows you to perform a large number of server management operations. You can issue FLUSH statements. you can also define mysqladmin's reload, refresh, flush-hosts, flush-logs, flush-privileges, and flush-tables commands.
SHUTDOWN
You can use mysqladmin shutdown to shut down the server.
In the user, db, and host tables, each permission is specified in a separate column. All these columns are declared as an ENUM ("N", "Y") type, so the default value of each permission is "N ". The permission in tables_priv and columns_priv is expressed as a SET, which allows the permission to be specified by a single column in any combination. These two tables are updated compared to the other three tables, which is why they use a more effective representation. (In the future, user, db, and host tables may also be represented by a SET type .)

The Table_priv column in The tables_priv table is defined:

SET ('select', 'insert', 'update', 'delete', 'create', 'drop', 'Grant ', 'references', 'index ', 'alter ')
The Column_priv column in The coloums_priv table is defined:

SET ('select', 'insert', 'update', 'references ')
The column permission is less than the table permission because the column-level permission is less meaningful. For example, you can create a table, but you cannot create an isolated column.

The user table contains some permissions that do not exist in other authorization tables: File_priv, Process_priv, Reload_priv, and Shutdown_priv. These permissions apply to operations that are irrelevant to any specific database or table that you allow the server to perform. It is meaningless to allow a user to shut down the database based on what the current database is.

2.2 how the server controls customer access
When you use MySQL, there are two phases of customer access control. The first phase occurs when you try to connect to the server. The server looks for the user table to see if it can find an entry that matches your name, the host you are connecting from there, and the password you provided. If no match exists, you cannot connect. If there is a match, establish the connection and continue the second stage. At this stage, for each query you send, the server checks the authorization table to see if you have sufficient permissions to execute the query. The second stage continues until the end of your conversation with the server.

This section describes in detail the principles used by the MySQL server to match the authorization table entries for incoming connection requests or queries, this includes the types of valid values in the scope column of the authorization table, the methods used in combination with the permission information in the authorization table, and the order in which entries in the table are checked.

2.2.1 range column 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. The value localhost indicates the local host, but it matches only when you use a localhost host name, instead of when you use the host name. Assume that your local Host name is pit.snke.net and there are two records for you in the user table. one has a Host value or localhost, and the other has pit.snke.net, logs with localhost will match only when you connect to localhost, and others will match only when you connect to pit.snke.net. If you want customers to connect in two ways, you need to have two records in the user table.

You can also use wildcards to specify the Host value. You can use the SQL Mode characters "%" and "_", which have the same meaning when you use the LIKE operator in a query (the regex operator is not allowed ). SQL Mode characters can be used for host names and IP addresses. For example, % wisc.edu matches any host in the wisc.edu domain, while %. edu matches any host in the School of Education. Similarly, 192.168.% matches any host in the 192.168 Class B subnet, while 192.168.3.% matches any host in the 192.168.3 Class C subnet.

The % 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 indicates "further check of the host table". This process is described in "query access verification .)

From MySQL 3.23, you can also specify an IP address with a network mask that indicates the network address, for example, 192.168.128.0/17 specifies a 17-bit network address and matches any host whose IP address is the first 17-bit of 192.168.128.

User
The user name must be text or blank. A blank value matches any user. % As a User value does not mean white space. On the contrary, it matches a literal % name, which may not be what you want.

When an incoming connection is verified through the user table and the matched record contains a blank User value, the customer is considered to be an anonymous user.

Password
The password value can be null or non-empty. wildcards are not allowed. A blank password does not match any password. it means that the user must not specify a password.

The password is stored as an encrypted value instead of a literal text. If you store a literal Password in the Password column, the user cannot connect! The GRANT statement and mysqladmin password command automatically encrypt the PASSWORD for you. However, if you use commands such as INSERT, REPLACE, UPDATE, or set password, you must use password ("new_password ") instead of simply specifying the password "new_password.

Db
In columns_priv and tables_priv tables, the Db value must be the real database name (literally). the mode and blank space are not allowed. In db and host, the Db value can be specified literally or use the SQL Mode character '%' or '_' to specify a wildcard. Either '%' or blank match any database.
Table_name, Column_name
The values in these columns must be literal tables or column names. the mode and blank spaces are not allowed.
Some range columns are considered case-sensitive by the server, while others are not. These principles are summarized in the following table. Note that the Table_name value is always case sensitive. even if the table name in the query is case sensitive, it depends on the file system of the host running on the server (in UNIX, it is case sensitive, windows is not ).

Table 3 case sensitivity of the range columns in the authorization table
Column
Host
User
Password
Db
Table_name
Column_name
Case sensitivity
No
Yes
Yes
Yes
Yes
No


2.2.2 query access verification
Each time you send a query, the server checks whether you have sufficient permissions to execute it. it checks in the order of user, db, tables_priv, and columns_priv, knowing that you have the proper access permission or you have searched all the tables makes nothing possible. More specifically:

The server checks that the user table matches the record on which you start the connection to view what global permissions you have. If you have them and they are sufficient for the query, the server executes them.
If your global permissions are insufficient, the server will search for you in the db table and add the permissions in the record to your global permissions. If the query result is sufficient, the server executes it.
If your global and database-level combinations do not have sufficient permissions, the server continues to search for them. first, in the tables_priv table, and then in the columns_priv table.
If you still do not have the permission after checking all the tables, the server rejects your attempt to execute the query.
In the term of Boolean operations, the permissions in the authorization table are used by the server as follows:

User OR tables_priv OR columns_priv

You may wonder why the previous description references only four authorization tables, but actually there are five. In fact, the server checks the access permission as follows:

User OR (db AND host) OR tables_priv OR columns_priv

The first simple 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 do not need to consider the host table. However, you should know how it works:

When the server checks the database-level permission, it queries the database 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 searches for db column values with the same records as those from the Db table in the host table. If no host record matches the client host, database-level permissions are not granted. If any of these records does have a Host column value that matches the connected client host, the database table record and the Host table record combine to generate the database-level permissions of the customer.
However, a permission is combined with a logic AND (AND), which means that the customer does not have this permission unless a given permission exists in both tables. In this way, you can grant a basic permission set in the db table, and then use the host table to selectively disable them for specific hosts. For example, you can allow all hosts in your domain to access the database, but disable the Database permissions of hosts in less secure regions.

The previous descriptions undoubtedly make access checks sound quite complicated, especially when you think that the server performs permission checks on each query you send. However, this process is very fast, because the server does not actually search for information from each query from the authorization table, instead, it reads the table content into the memory at startup, and then verifies that the query uses a copy in the memory. This greatly improves the performance of Access Check operations. But there is a very obvious side effect. If you directly modify the authorization table content, the server will not know the permission change.

For example, if you use an INSERT statement to add a new user to the user table to add a new user, the user named in the record cannot connect to the server. This is confusing for new administrators (sometimes experienced veterans). at that time, the solution was simple: after you changed them, you told the server to reload the authorization table content, you can send a flush privileges or execute mysqladmin flush-privileges (or if you have an old version that does not support flush-privileges, use mysqladmin reload .).

2.2.3 matching sequence of range columns
The MySQL server sorts the records in the authorization table in a specific way, and then matches the incoming connections by browsing the records in order. The first match is used. It is important to understand the sorting sequence used by MySQL, especially for the user table.

When the server reads the content of the user table, it sorts the records based on the values in the Host and User columns, and the Host values play a decisive role (the same Host values are listed together, and then sort by User value ). However, sorting is not a lexicographical order (sort by words), but partial. Remember that literal words take precedence over patterns. This means that if you are connecting to the server from client.your.net and the Host has two values: client.your.net and % .your.net, select first. Similarly, % .your.net takes precedence over %. net and then %. This is also true for IP address matching.

In a word, the more specific, the higher the priority. See the examples in the appendix.

2.3 Avoid table authorization risks
This section describes some preventive measures during your authorization and the risks arising from the selection of unknown values. Generally, you need to grant the superuser permission in a very stingy way, that is, do not enable the permissions in the user table entries, but use other authorization tables, to restrict user permissions to databases, tables, or columns. Permissions in the user table allow operations on your server or access any table in any database.

Do not grant permissions on the mysql database. A user with the permission to include the authorized table database may modify the table to obtain the permission for any other database. Granting the permission to allow a user to modify a mysql database table actually grants the user a global GRANT permission. If you can directly modify a table, it is equivalent to issuing any GRANT statement you can imagine.

FILE permission is especially dangerous. do not authorize it easily. The following is what a person with FILE permissions can do:

Create table etc_passwd (pwd_entry TEXT );
Load data infile "/etc/passwd" into TABLE etc_passwd;
SELECT * FROM etc_passwd;
After these statements are issued, the user has the content of your password file. In fact, the content of any publicly readable FILE on the server can be accessed by users with FILE permissions through the network.

The FILE permission can also be exploited to harm databases on the system that do not have enough permission to set FILE permissions. This is why you should set the data directory to be read only by the server