mysql| Security | Security MySQL Security Guide
Author: Yan Zi
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:
% 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:
% 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 MySQL
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
User DB Host
Host host Host
User DB DB
Password User
Database/table Permission columns
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_PR
