MySQL Security Guide (1) (redirected)

MySQL Security Guide
Author: Yanzi


As a MySQL system administrator, you are responsible for maintaining the data security and integrity of your MySQL database system. This article mainly introduces how to build a secure MySQL system, providing you with a guide from both the internal and external network perspectives of the system.

This article mainly considers the following security-related issues:

Why is security important and what attacks should you guard against?
Risks faced by the server (internal security), how to deal with them?
Client risk of connecting to the server (external security), how to deal with it?
MySQL administrators are responsible for ensuring the security of database contents so that these data records can only be accessed by correctly authorized users, which involves the internal security and external security of the database system.
Internal security is concerned with file system level issues, that is, preventing the MySQL data directory (DATADIR) from being attacked by people with accounts on the server host (legitimate or stolen). There is no point in ensuring that the authorization tables that control client access over the network are set up correctly if the contents of the data directory are over-granted so that everyone can simply replace the files corresponding to those database tables.

External security is concerned with the problems of clients connecting to the server from the outside through the network, that is, protecting the MySQL server from attacks coming from connections to the server through the network. You must set up the MySQL grant table (grant table), disallowing them from accessing the contents of the server-managed database unless a valid username and password are provided.

The following will introduce in detail how to set up the file system and authorization table mysql to achieve the two-level security of MySQL.


1. Internal Security - Ensure the security of data directory access
MySQL server provides a flexible permission system through the authorization table in the MySQL database. You can set the contents of these tables to allow or deny client access to the database. This provides you with a security measure to prevent unauthorized network access from attacking your database. However, if other users on the host can directly access the contents of the data directory, establishing a Good security for database access over the network will not help you unless you know that you are the only user logged into the host machine where the MySQL server is running, and you need to be concerned about the possibility of other users on this machine gaining access to the data directory.

Here’s what you should protect:

Database files. Obviously, you want to maintain the privacy of the server-managed database. Database owners generally, and should, consider the security of database contents, even if they don't want to, to make database contents public rather than exposing them through poor data directory security.
Log files. General and change logs must be kept secure as they contain query text. Anyone with access to the log files can monitor operations performed on the database.
The security of log files that should be considered more importantly is such as GRANT and SET Queries such as PASSWord are also logged, and generally and update logs contain the text of sensitive queries, including passwords (MySQL uses password encryption, but it is not applied to subsequent connection establishments until it has been set. The process of setting a password is designed like GRANT or SET PASSWORD and other queries, and these queries are recorded in the log file in plain text). If an attacker gains read access to a file, they can simply run grep on the log file looking for words such as GRANT and PASSWORD to discover sensitive information.
Obviously, you don't want to give other users on the server host write access to the database directory files because they can overwrite your state files or database table files, but read access is also dangerous. If a database table file can be read, it would be cumbersome to steal the file and get to MySQL itself and display the contents of the table in plain text. Why? Because you have to do the following:

Install your own "special" MySQL server on the server host, but with a different port, socket and data directory than the official server version.
Run mysql_install_db to initialize your data directory, which gives you the role of MySQL The root user has access to your server, so you have full control over the server access mechanism. It also creates a test database.
Copy the table file corresponding to the table file you want to steal to the test directory under the database directory of your server.
Start your server. You can access the database tables at will, SHOW TABLES FROM test shows that you have a copy of the stolen table, SELECT * shows the entire contents of any of them.
If you are really malicious, expose the permissions to any anonymous user on your server, so that anyone can connect to the server from anywhere and access your test database. You have now made the stolen database table public.
Think about it, from the opposite perspective, do you want others to do this to you? of course not! You can log in the database by executing ls The -l command determines whether your database contains unsafe files and directories. Look for files and directories that have "Group" and "Other Users" permissions set. Below is a partial listing of the unsafe data directory:

　
% 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 correct permissions, while others do not. The situation in this example is the result after a period of time. Less restrictive permissions are set by older servers that are less restrictive in their permission settings than newer versions (note that the more restrictive directories menageria and tmp both have more recent dates). Current versions of MySQL ensure that these files can only be read by the user running the server.

Let’s fix these permissions so that only the server user can access them. Your primary protection tools come from the tools provided by the UNIX file system itself for setting file and directory ownership and modes. Here’s what we have to do:

Go to the directory
% cd DATADIR

Sets the ownership of all files in the data directory to be owned by the account used to run the server (you must be root to perform this step). In this article, mysqladm and mysqlgrp are used as the user name and group name of the account. You can change the owner using one of the following commands:
# 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. This prevents other users from accessing the contents of 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 The owner and mode of the go-rwx

data directory content are set for mysqladm. Now you should ensure that you always run the server as the mysqladm user, as this is now the only user (other than root) who has access to the database directory.
After completing these settings, you should end up with 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
....



2. External security - ensuring the security of network access
MySQL's security system is very flexible and allows you to set user permissions in many different ways. Generally, you can do this using the standard SQL GRANT and REVOKE statements, which modify the grant tables that control client access for you. However, you may be stuck with an older version of MySQL (prior to 3.22.11) that does not support these statements. doesn't work), or you find that user permissions don't seem to work the way you want. For this situation, it is helpful to understand the structure of the MySQL authorization tables and how the server uses them to determine access permissions. Such an understanding allows you to add, delete, or modify user permissions by directly modifying the authorization tables. It also allows you to inspect these tables. when diagnosing permission issues.

For information on how to manage user accounts, see "MySQL User Management". For a detailed description of the GRANT and REVOKE statements, see the "MySQL Reference Manual".

2.1 The structure and contents of the MySQL authorization table
Access to the MySQL database by clients connected to the server through the network is controlled by the contents of the authorization table. These tables are located in the mysql database and are initialized during the first installation of MySQL (running the mysql_install_db script). There are 5 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 Host Host
User Db Db
Password User
Database/table permission column
Alter_priv Alter_priv Alter_priv
Create_priv Create_priv Create_priv
Delete_priv Delete_priv Delete_priv
Drop_priv Drop_priv Drop_priv
Index_priv Index_priv Index_priv
Insert_priv Insert_priv Insert_priv
References_priv References_priv References_priv
Select_priv Select_priv Select_priv
Update_priv Update_priv Update_priv
File_priv Grant_priv Grant_priv
Grant_priv
Process_priv
Reload_priv
Shutdown_priv

Table 2 tables_priv and columns_priv ownership table structure

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

The contents of the authorization table serve the following purposes:

user table
The user table lists the users who can connect to the server and their passwords, and it specifies what global (superuser) permissions they have. Any permissions enabled on the user table are global permissions and apply to all databases. For example, if you have DELETE permissions enabled, users listed here can delete records from any table, so think carefully before you do this.
db table
db table lists the databases and users have permission to access them. The permissions specified here apply to all tables in a database.
Host table
The host table is used in combination with the db table to control the access rights of a specific host to the database at a better level, which may be better than using db alone. This table is not affected by GRANT and REVOKE statements, so you may find that you are not using it at all.
tables_priv table
tables_priv table specifies table-level permissions. A permission specified here applies to all columns of a table.
columns_priv table
columns_priv table specifies column-level permissions. The permissions specified here apply to specific columns of a table.
In the "Setting up users without GRANT" section, we discuss how the GRANT statement works for modifying these tables, and how you can achieve the same effect by directly modifying the authorization table.

tables_priv and columns_priv tables in MySQL Introduced in version 3.22.11 (at the same time as the GRANT statement). If you have an earlier version of MySQL, your mysql database will only have the user, db, and host tables. If you upgraded from an older version to 3.22.11 or newer and do not have 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 users to rows in the table that contain specific column values. If you really need this capability, you have to use application programming to provide it. If you want to perform the recommended record-level locking, you can do so with the GET_LOCK() function.

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

2.1.1 Authorization table scope column
Authorization table scope column specifies when the permissions in the table are applied. Each authorization table entry contains User and Host columns to specify when permissions apply to a given user's connection from a given host. Other tables contain additional range columns, such as the db table containing a Db column indicating which database the permission applies to. Similarly, the tables_priv and columns_priv tables contain range fields that narrow the scope to a specific table in a database or to a specific column of a table.

2.1.2 Authorization Table Permission Columns
The authorization table also contains permission columns, which indicate what permissions the user specified in the scope column has. The permissions supported by MySQL are shown in the following table. The table uses the permission name of the GRANT statement. For most of the permission column names in the user, db and host tables, there is an obvious connection between the names in the GRANT statement. For example, Select_priv corresponds to SELECT permission.

The above is the content of MySQL Security Guide (1) (reprint). For more related articles, please pay attention to the PHP Chinese website (www.php.cn)!