Details about the security mechanism of MySQL

As a system database, MySQL has high security requirements. If the database of a system is illegal to access or eavesdrop, the data of the system will be seriously threatened. Otherwise, the data and password will be stolen, and the system will be paralyzed. Therefore, database security is very important for the system. This article will start with the customer from the MySQL server

As a system database, MySQL has high security requirements. If the database of a system is illegal to access or eavesdrop, the data of the system will be seriously threatened. Otherwise, the data and password will be stolen, and the system will be paralyzed. Therefore, database security is very important for the system. This article will start with the customer from the MySQL server

As a system database, MySQL has high security requirements. If the database of a system is illegal to access or eavesdrop, the data of the system will be seriously threatened. Otherwise, the data and password will be stolen, and the system will be paralyzed. Therefore, database security is very important for the system.

This article describes the MySQL security mechanism from the following aspects: MySQL server startup and client access, operations, and links.

1. MySQL server startup and client access.
1. When a server is started, the main security impact of the startup server is the user who starts the server. By default, MySQL cannot be started with the root account. We should create a non-interactive account that can only operate the MySQL installation directory and data directory to run the mysqld server. In addition, it is recommended that the server be open to a specified client. After the server is placed in iptables, it is not safe if any host can be connected to the server through telnet. Telnet test mode: shell> telnet ip (Server ip) 3306 (default server port)
2. access from the client. When using the bin/mysql client tool to log on, you can use the configuration file or directly add parameters to the command line. During login, we need to use a password to access. However, after MySQL is installed, the root account does not have a password by default. Therefore, we need to set the password for the root user first. If the root user does not set a password, you can set the password as follows:
2.1 shell> bin/mysql-user = root-host = 127.0.0.1. Press enter to enter the interaction interface of the mysql client.
2.2 mysql> set password for 'root' @ '192. 0.0.1 '= password ('your plaintext password '). In this way, the root password in 127.0.0.1 is set. It should be noted that the login account of MySQL is not only the user name, but also the user name + IP or domain name to confirm the unique connection. Therefore, if the IP address of the server is 192.168.1.5, I only set 'root' @ '127 when setting the password. 0.0.1 ', an error will be prompted when you log on with mysql-host = 192.168.1.5-user = root-password = 'your plaintext password. Therefore, if we do not log on to the server locally, we also need to set the password set password for 'root' @ '192. 168.1.5 '= password ('your plaintext password '). In addition, we use the password () function when setting the password. The password function converts the plaintext password to a hash value of 41 bytes. Added the security of passwords.
If we place the password in the configuration file, remember to set the access permission of the configuration file to 600 or 400, and you do not want other users to read it easily.

Ii. Security Mechanism of the authorization management system for MySQL operations
2.1 The main function of the MySQL permission management system is to verify the login of the client user and the specific operation permissions of the user on MySQL, for example, the user may only have the update and insert permissions for a table in a database, and the system will prohibit him from writing to other tables. However, the permission system cannot do the following:
2.1.1. You cannot specify the user that rejects logon.
2.1.2. You cannot specify a user to create or delete a table in a database, but do not allow the user to create or delete the database to which the table belongs.
2.1.3. Your password is global. You cannot set a password for a database or table.

2.2 MySQL classifies permissions into global permissions and object permissions. Global permissions are irrelevant to databases, tables, views, and Other permissions, such as password settings and User Creation. Object permissions are relative to global permissions, that is, permissions related to databases, tables, views, columns, indexes, and other objects.
The permission information is stored in the user, db, tables_priv, columns_priv, and prods_priv tables of the mysql database. After the server is started, the program loads the permission table into the memory, during client connection, the user's validity is verified based on the permission table. When the client sends a request, the validity of user operations is also verified based on the permission table.
The following describes the specific permissions that we can control, the scope of these permissions, and the name of the columns that control them.

Permission	Name of the column with permission Control	Scope
CREATE	Create_priv	Databases, tables, and indexes
DROP	Drop_priv	Databases, tables, and views
LOCK TABLES	Lock_tables_priv	Database
REFERENCES	References_priv	Databases and tables
EVENT	Event_priv	Database
ALTER	Alter_priv	Table
DELETE	Delete_priv	Table
INDEX	Index_priv	Table
INSERT	Insert_priv	Tables and columns
SELECT	Select_priv	Tables and columns
UPDATE	Update_priv	Tables and columns
CREATE TEMPORARY TABLES	Create_tmp_table_priv	Table
TRIGGER	Trigger_priv	Table
CREATE VIEW	Create_view_priv	View
SHOW VIEW	Show_view_priv	View
CREATE TABLESPACE	Create_tablespace_priv	Global
CREATE USER	Create_user_priv	Global
PROCESS	Process_priv	Global
PROXY	See proxies_priv table	Global
RELOAD	Reload_priv	Global
REPLICATION CLIENT	Repl_client_priv	Global
REPLICATION SLAVE	Repl_slave_priv	Global
SHOW DATABASES	Show_db_priv	Global
SHUTDOWN	Shutdown_priv	Global
SUPER	Super_priv	Global
ALL [PRIVILEGES]		Global
USAGE		Global

You can set permissions based on the preceding table. To set permissions, you must first know the current permissions of the users we set. The following command shows that:
Mysql> show grants for 'user' @ 'IP ';
Again, although show grants for 'user'; can be used, 'user' @ 'IP' represents a unique mysql user. After the command is called, the following results are displayed:
Grant usage on *. * TO 'wangwei' @ '%' identified by password' * 6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9 'WITH MAX_QUERIES_PER_HOUR 20

USAGE is the value of the last row in the table above. It is a global permission and only indicates that the database can be accessed.
*. * The first * indicates the database name, and the second * indicates the table name.
Password after IDENTIFIED
WITH is the permission parameter, which is limited to 20 queries per hour at most

Then, set our own permissions. To set the query permission for a column in a table, run the following command:
Mysql> grant select (column name) on database name. Table name to 'username '@ 'IP ';
In this way, the user has the query permission for the specified column.

2.3 Password Expiration Policy. If you want a mysql user to set a password every other time, you can use the following methods.
Mysql> alter user 'user' @ 'IP' password expire; invalidate the user password immediately
Mysql> alter user 'user' @ 'IP' password expire interval 90 day; password needs to be reset every 90 days
Mysql> alter user 'user' @ 'IP' password expire never; password never Expires
You can also set the default password expiration time in the configuration file, for example:
[Mysqld]
Default_password_lifetime = 180

3. Link communication security. The client communicates with the server over the network. In addition to transmitting login authentication information, it also requests specific database operations. Therefore, the confidentiality and integrity of data between the server and the client are very important. Here, we use SSL to ensure data security.
To use SSL, the MySQL server must support the SSL protocol. Use mysql> show variables like 'have _ ssl 'to check whether MySQL supports SSL. If YES is displayed, the server is running with the SSL protocol. If DISABLED is displayed, the server supports SSL, but SSL is not enabled. Otherwise, the MySQL server does not support SSL. You need to re-download the new version of MySQL or re-compile to start the SSL function. I am using the binary version of MySQL 5.6, which supports SSL by default. The following describes the specific configuration methods.
To configure SSL, you only need four parameters: ssl, ssl-ca, ssl-cert, and ssl-key. Ssl indicates that the SSL function is enabled. The location where ssl-ca is the CA certificate. The location where the ssl-cert is the server certificate signed by the CA. The ssl-key is the private key of the server. If you are familiar with CA and X509, you should understand the functions of these parameters. For those who do not understand, it is best to learn about the principles of CA authentication and SSL protocol. Whether you are familiar with SSL or not. Follow these steps to configure the MySQL communication for the secure link.
The following describes the specific implementation steps:
1. Download and install openssl. Http://www.openssl.org/source /. Download the latest version of openssl-1.0.1j.tar.gz. I use the latest version.
Installation Method:
Shell> tar zxvf openssl-1.0.1j.tar.gz
Shell> cd openssl-1.0.1j &./config -- prefix =/usr/local-openssldir =/usr/local/openssl
Shell> make
Shell> make install
After installation, you can find the openssl command under/usr/local/bin. The above is a simple method for compiling and installing source code. You do not need to explain it in detail. Follow these steps to install openssl.

2. Create a CA certificate
Shell> mkdir newcerts & cd newcerts
Shell> openssl genrsa 2048> ca-key.pem creates a CA private key
Shell> openssl req-new-x509-nodes-days 3600-key ca-key.pem-out ca-cert.pem create CA root certificates

3. Create a CA-Signed server certificate and Private Key
Shell> openssl req-newkey rsa: 2048-days 3600-nodes-keyout server-key.pem-out server-req.pem build server certificate request file and server Private Key
Shell> openssl rsa-in server-key.pem-out server-key.pem encryption Private Key
Shell> openssl x509-req-in server-req.pem-days 3600-CA ca-cert.pem-CAkey ca-key.pem-set_serial 01-out server-cert.pem signature server certificate

The generated ca-cert.pem is used for ssl-ca Parameters
The generated server-cert.pem is used for the ssl-cert Parameter
The generated server-key.pem is used for the ssl-key Parameter

Run the following command on the server:
Mysqld-user = mysql-ssl-ca =/path/ca-cert.pem-ssl-cert =/path/server-cert.pem-ssl-key =/path/server-key.pem

The client can also create an SSL Certificate for two-way authentication, or directly connect to the server without setting. After running, use mysql> show variables like 'have _ ssl 'for verification. If YES is displayed. Indicates that the SSL settings are successful. Otherwise, you need to debug and see what the problem is.
If something goes wrong, first you can use the shell> openssl verify-CAfile ca-cert.pem server-cert.pem to see if the certificate is correctly generated. If the problem persists, check whether the mysqld running user has the permission to read the certificate.