MySQL security guide (1)

The ORACLE tutorial is: MySQL security guide (1) (). MySQL Security Guide
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

[1] [2] Next page

The ORACLE tutorial is: MySQL security guide (1) (). 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.

Previous Page [1] [2]