MySQL Security Guide (1)

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

This article mainly considers the following security-related issues:

Why is security so important that you should guard against those attacks?

The risks that the server faces (internal security), how do you handle it?

Client risk (external security) for connecting to the server, how do I handle it?

The MySQL administrator has the responsibility to secure the contents of the database so that the data records can only be accessed by those users who are properly authorized, which involves internal security and external security of the database system.

Internal security is concerned with file system-level issues, that is, preventing MySQL data directories (DATADIR) from being attacked by people (legitimate or stolen) who have an account on the server host. If the permissions of the data directory content are excessively granted so that everyone can simply replace the files that correspond to those database tables, it makes no sense to make sure that the control of the client's authorization table for access through the network is set correctly.

External security is concerned with customers who connect to the server from the outside through the network, that is, to protect the MySQL server from attacks from connections to the server over the network. You must set up the MySQL authorization Table (grant table) so that they do not allow access to server-managed database content unless a valid username and password are provided.

Here is a detailed description of how to set up file system and authorization table MySQL, to achieve MySQL two security.

I. Internal security-Securing Data Directory Access

The MySQL server provides a flexible permission system through the authorization table in the MySQL database. You can set the contents of these tables, allow or deny customer access to the database, which provides you with the security to prevent unauthorized network access against your database, but if other users on the host have direct access to the contents of the data directory, establishing good security over the network access to the database is no help to you, Unless you know that you are the only user who is logged on to the MySQL server running the host, you need to be concerned about the likelihood that other users on this machine will gain access to the data directory.

Here are the things you should protect:

Database files. Obviously, you want to maintain the private nature of the server-managed database. Database owners often and should consider the security of database content, even if they do not want to, should also consider when the content of the database open, rather than through poor data directory security to expose the content.

Log files. General and update logs must be secured because they contain the query text. Anyone who has access to the log file can monitor what the database has been doing.

The more important consideration of log file security is that queries such as Grant and set password are also documented, and the general and update log contains text with sensitive queries, including passwords (MySQL uses password encryption, but it is not applied to subsequent connections after it has been set up). The process of setting a password is designed like a query such as grant or set password, and these queries are recorded in plain text in a log file. If an attacker has read access to a daily file, simply run grep on the log file to find the words such as grant and password to discover sensitive information.

Obviously, you don't want other users on the server host to have write access to the database directory files, because they can rewrite your state files or database table files, but Read permissions are also dangerous. If a database table file can be read, steal files and get MySQL itself, in plain text to display the contents of the table is also very troublesome, why? Because you have to do the following things:

Install your own "custom" MySQL server on the server host, but there is a different port, socket, and data directory from the official server version.

Running mysql_install_db initializes your data directory, which gives you access to your server as a MySQL root user, so you have full control over the server access mechanism, and it also creates a test database.

The test directory will correspond to the database directory where you want to steal the copy of the table file to your server.

Start your server. You are free to access the database tables, show table from test shows that you have a copy of the stolen table, and SELECT * Displays the entire contents of any one of them.
If you're really vicious, expose the permissions to any anonymous user on your server so anyone can connect to the server from anywhere to access your test database. You will now be posting the stolen database tables to the public.

Considering, from the opposite point of view, do you want someone to do this to you? Of course not! You can determine whether your database contains unsafe files and directories by executing the LS-L command in the database record. Find files and directories that have the group and other user permission settings. The following is a list of unsafe data directories:

The following are the referenced contents: % Ls-l Total 10148 Drwxrwxr-x MYSQLADM Wheel 1024 May 8 12:20 Drwxr-xr-x root Wheel 8 13:31. DRWX------2 mysqladm mysqlgrp APR 15:57 Menagerie Drwxrwxr-x 2 mysqladm Wheel 20:40 MySQL Drwxrwxr-x 7 Mysqladm Wheel AUG 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 of a period of time. Less restrictive permissions are set by older versions of servers that have less stringent permissions than newer versions (note that more restrictive directories Menageria and TMP have more recent dates). The current version of MySQL ensures that these files can only be read by users running the server.

Let's fix these permissions so that only the server users can access them. Your primary protection tool comes from the Setup files and the directory owner and mode tools provided by the UNIX file system itself. Here's what we're going to do:

Enter the directory

% CD DataDir

Set all files in the data directory owner is owned by the account used to run the server (you must perform this step with root). Use Mysqladm and mysqlgrp as the user name and group name for this account in this article. You can use one of the following commands to change the owner:

# chown Mysqladm. Mysqlgrp.

# Find. -follow-type D-print | Xargs chown Mysqladm. Mysqlgrp

The pattern of setting up your data directory and database directory allows them to be read only by Mysqladm, which prevents other users from accessing the contents of your database directory. You can run as root or mysqladm with one of the following commands.

% 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 make sure that you always run the server as a mysqladm user, because now this is the only user (except root) that accesses the database directory permissions.

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

The following are the referenced contents: % Ls-l Total 10148 DRWXRWX---mysqladm mysqlgrp 1024 May 8 12:20. Drwxr-xr-x root Wheel 8 13:31. DRWX------2 mysqladm mysqlgrp APR 15:57 Menagerie DRWX------2 Mysqladm mysqlgrp 20:40 mysq DRWX------7 Mysqladm mysqlgrp Aug 1998 Sql-bench DRWX------2 Mysqladm mysqlgrp 1536 May 6 06:11 test DRWX------2 Mysqladm mysqlgrp 1024 May 8 18:43 tmp ....

Ii. External Security-Securing network access

The MySQL security system is flexible and allows you to set user permissions in many different ways. Generally, you can use the standard SQL statements GRANT and REVOKE statements, and they modify the authorization form for you to control client access, however, You may have an older version of MySQL that does not support these statements (these statements do not work before 3.22.11), or you find that user permissions do not seem to work the way you want them to. In this case, it is helpful to understand the structure of the MySQL authorization table and how the server can use them to determine access rights, which allows you to add, delete, or modify user permissions by directly modifying the authorization table, and it also allows you to diagnose permissions issues when checking those tables.

For information on how to manage user accounts, see "MySQL User management." For a detailed description of GRANT and REVOKE statements, see the MySQL reference manual.

2.1 The 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 contents of the authorization table. These tables are located in the MySQL database and initialized (run the mysql_install_db script) during the first installation of MySQL. There are 5 tables in the authorization table: User, DB, host, Tables_priv, and Columns_priv.

Table 1 user, DB, and host authorization table structures

Access Scope columns

The following are the referenced contents: User DB Host Host host Host User DB DB Password User

Database/table Permission columns

The following are the referenced contents: 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 structure of genus Tables_priv and Columns_priv

Access Scope columns

The following are the referenced contents: Tables_priv Columns_priv Host Host DB db User User TABLE_NAME TABLE_NAME column_name

Permission columns

Table_priv Column_priv

The contents of the authorization table are used for the following purposes:

User table

The user table lists the users and their passwords that can connect to the server, and it specifies what global (superuser) permissions they have. Any permissions that are enabled in the user table are global permissions and apply to all databases. For example, if you have the Delete permission enabled, the users listed here can delete records from any table, so think carefully before you do so.

Db

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

Host table

The host table is used in conjunction with the DB table to control the access of a particular host to the database at 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 at all.

Tables_priv table

The Tables_priv table specifies table-level permissions, and one of the permissions specified here applies to all columns of a table.

Columns_priv table

The COLUMNS_PRIV table specifies column-level permissions. The permissions specified here apply to a specific column of a table.

In the "Do Not grant users" section, we discuss how the grant statement works with modifying these tables, and how you can achieve the same effect by directly modifying the authorization table.

Tables_priv and Columns_priv tables are introduced in the MySQL 3.22.11 version (along with 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 older versions to 3.22.11 or updates without tables_priv and Columns_priv tables, run mysql_fix_privileges_tables scripts to create them.

MySQL does not have a rows_priv table because it does not provide record-level permissions, for example, you cannot limit the number of rows in a table that contain a specific column value. If you really need this ability, you have to use application programming to provide it. If you want to perform the recommended record-level locking, you can do so using the Get_lock () function.

An authorization table contains two columns: a range column that determines when a permission is used, and a permission column that determines which permissions are granted.

2.1.1 Authorization Table Range column

The Authorization Table scope column specifies when the permissions in the table are to be applied. Each authorization table entry contains the user and the host column specifies when permissions are applied to a given user's connection from a given host. Other tables contain additional scope columns, such as a DB table that contains a DB column that indicates which database the permissions are applied to. Similarly, the Tables_priv and Columns_priv tables contain range fields, narrowing down to specific columns in a particular table or table within a database.

2.1.2 Authorization Table Permission column

Authorization tables also contain permission columns that 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. There is a clear correlation between the names of most rights columns in the user, DB, and host tables and the GRANT statement. such as Select_priv corresponds to the SELECT permission.