Chat about the security mechanism of MySQL

MySQL, as a database of the system, has very high security requirements. If the database of a system is illegally entered or tapped, the system's data will be very serious threats, the data, password stolen, the weight of the entire system is paralyzed. Therefore, the security of the database is very important for the system.

This article will explain the MySQL security mechanism from the MySQL server startup and the client access, operation and link three aspects.

First, the MySQL server starts with client access.
1, the server startup, the security impact of starting the server is mainly to start its users. By default, MySQL does not allow the root account to be used for booting. We should create a non-interactive account that can only operate the MySQL installation directory and the data directory to run the MYSQLD server. In addition, the server is best for the specified client open, should be placed after the server iptables, if any host can be Telnet connection to the server, it will also be unsafe. Telnet is tested in Shell > telnet IP (server IP) 3306 (default port for server)
2, client access, using the Bin/mysql Client tool login can be through the configuration file and directly on the command line to add parameters two ways. When we log in, we need to use a password to access it, but MySQL does not have a password after installing the default root account. So we first need to set the password for root. If Root does not have a password set, it can be set in the following ways:
2.1 Shell > bin/mysql-user=root-host=127.0.0.1, enter the interactive interface to the MySQL client after entering.
2.2 MySQL > Set password for ' root ' @ ' 127.0.0.1 ' = password (' You set the plaintext password '). This will set the root password for 127.0.0.1. It is important to note that MySQL login account is not only the user name, but the user name +ip or domain name of the way to confirm the only connection. So, if the IP of the server is 192.168.1.5, when setting the password I only set the ' root ' @ ' 127.0.0.1 ' password, then use mysql-host=192.168.1.5-user=root-password= ' You set the clear text password ' when logged in, will prompt an error. So, if we do not log on locally, then we also need to set the password setting password for ' root ' @ ' 192.168.1.5 ' =password (' You set the plaintext password '). Another thing to note is that when setting a password, we use the password () function, which converts the plaintext password into a hash value of 41 bytes long. The security of the password is increased.
If we put the password in the configuration file, remember to set the configuration file access to 600 or 400, you do not want other users can easily read it.

Second, the MySQL Operation authorization management system security mechanism
The main function of 2.1 MySQL Rights management system is to verify the client user's login and verify the user's specific operation rights to MySQL, for example, the user may have Update permission and insert permission only for one of the tables in a database. Then the system will prevent TA from writing to other tables. However, the following things are not done by the Authority system:
2.1.1, you cannot specify a specific deny login user.
2.1.2, you cannot specify that a user creates or deletes a table in a database, but does not allow TA to create or delete the database to which the table belongs.
2.1.3, the user's password is global, you cannot set the password for the database or the table.

2.2 MySQL divides permissions into global permissions and object permissions. The so-called global permissions are related to databases, tables, views and other irrelevant permissions, such as password settings, user creation and so on. Object permissions are relative to global permissions, that is, permissions related to all objects, such as databases, tables, views, columns, indexes, and so on.
Information about permissions is stored in the user, DB, Tables_priv, Columns_priv, and Prods_priv tables in the MySQL database, and after the server is started, the program will load the permissions table into memory, and the client will verify the legality of the user based on the permission table. When a client makes a request, the legitimacy of the user action is also verified against the permission table.
Below we can look at the specific permissions we can control and the scope of these permissions and the column names of control permissions.

Permissions	Column names for control permissions	Function range
CREATE	Create_priv	databases, tables, indexes
DROP	Drop_priv	databases, tables, views
LOCK TABLES	Lock_tables_priv	Database
REFERENCES	References_priv	Database, table
EVENT	Event_priv	Database
Alter	Alter_priv	Table
DELETE	Delete_priv	Table
INDEX	Index_priv	Table
INSERT	Insert_priv	Tables, Columns
SELECT	Select_priv	Tables, Columns
UPDATE	Update_priv	Tables, 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

We can set the appropriate permissions according to the table above. To set permissions, you first need to know the current permissions of the user we set. Use the following command to know:
MySQL > Show grants for ' user ' @ ' IP ';
Again, while using show grants for ' user ', it can also be displayed, but ' user ' @ ' IP ' represents a unique user of MySQL. The following results are displayed when the command is invoked:
GRANT USAGE on *. Wangwei ' @ '% ' identified by PASSWORD ' *6bb4837eb74329105ee4568dda7dc67ed2ca2ad9 ' with max_queries_p Er_hour 20

The usage is the value of the last row in the table above, which is a global permission, but represents the ability to enter the database.
* * The first * represents the database name, the second * represents the table name.
identified by after password
With after is the permission parameter, here is limit the maximum query 20 times per hour

Then start setting up our own permissions, such as the command to set the query permission for a column of a table as follows:
MySQL > Grant select (column name) on the 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 MySQL users to set passwords once a time, the following methods are available.
mysql > Alter user ' user ' @ ' IP ' password expire; Make the user password immediately invalid
mysql > Alter user ' user ' @ ' IP ' password expire interval; 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 expiration time for a password by using the configuration file, such as:
[Mysqld]
default_password_lifetime=180

Third, link communication security. The client communicates with the server over the network and requests specific database operations in addition to the transfer of login authentication information. So the confidentiality and integrity of data between server and client is very important. Here, we use SSL to ensure the security of the data.
To use SSL, the MySQL server is required to support the SSL protocol. Use mysql > Show variables like ' Have_ssl '; you can query 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 turned on. If the other is displayed, the MySQL server does not support SSL. You need to re-download the new version of MySQL or recompile to start the SSL feature. I am using the MySQL 5.6 binary version, the default is to support SSL. The following describes the specific configuration method.
Configuring SSL requires only four parameters of SSL, SSL-CA, Ssl-cert, Ssl-key. SSL indicates that the SSL function is started. SSL-CA is the location of the CA certificate. Ssl-cert is the location of the server certificate signed by the CA. Ssl-key is the private key location for the server. If the CA and X509 are familiar with the friends should be well understood, the role of these several parameters. It's best to learn the principles of CA authentication and SSL protocol for friends who don't understand. Whether you are familiar with SSL or not. Follow these steps to configure the MySQL communication for the secure link as well.
The following are the specific implementation steps:
1. Download and install OpenSSL. For http://www.openssl.org/source/. You can download the latest version of openssl-1.0.1j.tar.gz. I'm just using 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, the commands for OpenSSL can be found under/usr/local/bin. The above is a very simple source code compilation installation method, do not need to explain in detail. Follow the steps above and you should be able to install OpenSSL.

2. Establish CA Certificate
Shell > mkdir newcerts && cd newcerts
Shell > OpenSSL genrsa 2048 > CA-KEY.PEM Establish CA private key
Shell > OpenSSL req-new-x509-nodes-days 3600-key ca-key.pem-out CA-CERT.PEM establish CA root certificate

3. Establish 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 Establish 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-ce RT.PEM Signing Server Certificate

The generated CA-CERT.PEM is used for SSL-CA parameters
The generated SERVER-CERT.PEM is used for Ssl-cert parameters
The generated SERVER-KEY.PEM is used for Ssl-key parameters

The server runs the following command
Mysqld-user=mysql-ssl-ssl-ca=/path/ca-cert.pem-ssl-cert=/path/server-cert.pem-ssl-key=/path/server-key.pem

Clients can also create SSL certificates for two-way authentication, or they can connect directly to the server without setting up. Available after run MySQL > show variables like ' Have_ssl '; If the result shows yes. Represents a successful SSL setup. Otherwise you need to debug to see what the problem is.
If there is a problem, you can first use shell> OpenSSL verify-cafile Ca-cert.pem Server-cert.pem to see if the certificate is generated correctly. If there is still a problem you need to see if the mysqld running user has permission to read the certificate.

This article is from the architect's path blog, so be sure to keep this source http://wangweiak47.blog.51cto.com/2337362/1588015

Chat about the security mechanism of MySQL