Shulou(Shulou.com)05/31 Report--

This article introduces the relevant knowledge of "how to understand MySQL security". In the operation of actual cases, many people will encounter such a dilemma, so let the editor lead you to learn how to deal with these situations. I hope you can read it carefully and be able to achieve something!

MySQL security

When multiple users access the MySQL server at the same time, especially when these users connect through Internet, the MySQL server will have a security risk. Not only is the MySQL server at risk, but the entire server host may also be compromised. There are many types of security attacks: eavesdropping, modification, playback, denial of service

Data stored in a MySQL database must be kept secure so that data that MySQL users expect to keep secret will not be disclosed. MySQL uses security based on access control lists (access control list, ACL). MySQL uses ACL-based security for all connections, queries, and other operations. ACL also supports SSL encrypted connections between MySQL clients and servers.

The most common installation security risks are:

U Web security: information about client accounts is stored in the mysql database. The permissions set for each account should provide access only to the data that the account needs to view or modify. Specifying an account password makes it difficult for users to connect to the server using someone else's account. For example, the MySQL root account has full permissions to perform any database operation, so it is necessary to assign it a password that is not easy to guess.

U operating system security: a dedicated login account is usually used to manage MySQL. However, there may be other login accounts on the host where the account is located. Minimizing the number of accounts not associated with MySQL minimizes this risk.

U file system security: directories and files are part of the file system, so you need to protect them from direct access by other users with login accounts on the server host. The MySQL installation also includes programs and scripts for managing and accessing databases. Users need to be able to run some of them (such as client programs), but they should not be able to modify or replace them.

It can be considered that cyber security is the highest defense level. It includes operating system security. The latter includes file system security and deeper user security. Although each security level has flaws (or vulnerabilities), when they are used together, each level forms an almost indestructible fortress.

1.1. network security

Network security risk prevention tasks:

-invest in setting up firewalls.

-ensure access is available only to authorized clients.

-restricts the network interface used by the server.

-use the secure installation script: mysql_secure_installation

-comply with general permissions and security matters.

-plaintext (unencrypted) data is not transmitted over Internet.

The MySQL server runs in a client / server environment and provides inherently network-oriented services. It is important to ensure that only authorized clients can connect to the server to access their databases. It is also important to ensure that your MySQL account is protected with a password and that you do not set unnecessary permissions for it. In addition, consider limiting the network interfaces used by the server.

There are many good free and open source firewalls. MySQL is not intended for Internet. Be careful if the MySQL is not placed in the DMZ. In fact, if MySQL is running on the same computer as an Internet-oriented application, you should probably use only file sockets.

Unencrypted data can be accessed by any user who has the time and ability to intercept the data and use it for their own purposes. Therefore, you should use encryption protocols such as SSL or SSH.

1.2. Password security

Password security risk prevention tasks:

-protect the initial MySQL account with a strong password.

-No plain text passwords are stored in the database. The mysql database stores passwords in the user table. It is best to use an one-way hash to store these passwords.

-do not select a password from the dictionary.

If you use plain text passwords, after your computer is compromised, the intruder can obtain a complete list of passwords and use them. Use MD5 (), SHA1 (), SHA2 (), or other one-way hash functions to store hash values instead.

This cautious approach is used to store other passwords on the server. There are some special programs to decipher passwords. One way to deal with these programs is to use a password consisting of the first character of each word in a sentence (for example, "Mary had a little lamb" can generate the password "Mhall"). This kind of password is easy to remember and enter, but it is difficult for people who do not know the sentence to guess.

1.3. Operating system security

Operating system security is related to the complexity of configuration

Minimize the number of server host tasks that are not directly related to running MySQL. When there are fewer tasks configured for a host, the previous host is easier to secure than a host running a complex configuration to support multiple services. It is preferable that the assigned MySQL server is used primarily for or dedicated to MySQL, rather than for other purposes such as Web hosting or mail processing, or a computer that hosts login accounts for general interactive use.

If other users can log in, there may be a risk that the private database information of the MySQL installation and its administrative accounts will be exposed. For example, incorrect file system permissions may cause data files to be exposed. Users can run the ps command to view information about the process and its execution environment.

When the computer is used only for MySQL, there is no need to set up a login account except for the system administration account and any other accounts that may be needed to manage MySQL itself, such as the mysql user's account.

In addition, if there are fewer network services running on the server host, there are fewer network ports that need to be kept open. Closing the port minimizes the number of possible attacks on the host. Minimizing the number of non-MySQL services means that more system resources can be dedicated to MySQL, thus benefiting from performance.

1.4. File system security

MySQL installation (directories and files) risk prevention tasks:

L change ownership and access before starting the server.

Set the ownership of the multi-user system to an account with administrative privileges. Set the ownership of MySQL-related directories and files and user and group tables to mysql, including: MySQL programs, database directories and files, logs, status and configuration files

L do not set the password before protecting the file. Otherwise, unauthorized users will be allowed to replace files.

Set up an account dedicated to MySQL administration.

Do not grant access to the user table in the mysql database to anyone other than the MySQL root account. Place the MySQL behind the firewall or in the quarantine (demilitarized zone, DMZ).

The password for the MySQL root account can be set only when the server is running. Therefore, before starting the server and setting the password, take any necessary steps to protect the MySQL-related parts of the file system. If a password is set before protecting the corresponding files, users who have direct access to the file system on the server host may replace those files. This can compromise the effect of MySQL installation and undo password settings.

For multi-user systems such as Linux, set ownership of all components installed by MySQL to a dedicated login account with correct administrative privileges. This will protect the installation from users who are not responsible for database administration. Another benefit of setting up this account is that you can use this account to run the MySQL server instead of running the server from the Linux root account. Servers with root login privileges have unnecessary file system access, which becomes a security risk.

Note: this section assumes that there is an administrative account with a user name and group name of mysql. However, the details of creating a login account vary depending on the version of Linux and are beyond the scope of discussion. Refer to the documentation for your operating system.

1.5. Data security

Users can corrupt data in a variety of ways. You must take steps to protect your data from attacks such as SQL injection.

First, do not trust any data entered by the application user. Users can use characters with special meaning, such as quotation marks or escape characters, to obtain the application code. If the user enters something similar to DROP DATABASE mysql;, make sure that the application remains secure.

Second, protect the values of numeric and string data. Otherwise, the user can gain access to secure data and then submit queries that can destroy the data or overload the server.

Finally, you even need to protect publicly available data, such as Web forms, URL names, special characters, and so on, because attacks can waste server resources.

If the application generates a query similar to SELECT * FROM table WHERE ID=234 when the user enters a value of 234, the user can enter a value of 234 OR 1 to cause the application to generate the query SELECT * FROM table WHERE ID=234 OR 1. Therefore, the server will retrieve each row in the table. This will expose each line and cause the server to be overloaded. To prevent this type of attack, use stored procedures or preprocessing statements that do not interpret values as SQL expressions.

You might think that if the database contains only publicly available data, the database does not need to be protected. This is wrong, and even if any row in the database is allowed to be displayed, you should prevent DdoS attacks or attempts to modify data by injecting INSERT, UPDATE, REPLACE, or DELETE statements. Otherwise, the data will not be available to legitimate users.

Here are some tips for detecting this problem:

-enter single or double quotes ("'" and "") in all Web forms.

-modify dynamic URL by adding% 22 (""),% 23 ("#") and% 27 ("'") to it.

-enter characters, spaces, and special symbols instead of numbers in numeric fields. The application should delete this content and pass it to MySQL; otherwise an error will be generated.

1.6. Secure connection

By default, MySQL uses unencrypted client / server connections. However, unencrypted connections may not meet the requirements for secure data transfer over the network, because unencrypted connected network traffic is vulnerable to monitoring and attacks, and the data transferred between the client and the server may be changed.

Most threats are protected against by using encryption algorithms, which make all types of data unreadable, which protects against many types of attacks. If your application requires the security provided by encrypted connections, additional computing resources must be considered for these connections. As you all know, data encryption is a CPU-intensive operation that requires the computer to perform additional work, so other MySQL tasks may be delayed.

1.6.1. SSL protocol

MySQL supports SSL (secure sockets layer, secure Sockets layer) connections between MySQL clients and servers. The SSL connection protocol has the following characteristics:

Using different encryption algorithms to ensure the security of data on the public network

* detect any data changes, losses, or replays

Z. combines an algorithm that uses the X509 standard to provide authentication

MySQL can enable encryption for a single connection. Depending on the needs of each application, you can choose a regular unencrypted connection or a secure encrypted SSL connection.

Secure connections are based on OpenSSL API and are available through MySQL C API. C API is used for replication, so you can use a secure connection between master and slave.

X509 makes it possible to identify on Internet. This is mainly used in e-commerce applications. Basically, there should be a trusted certification authority (Certificate Authority, CA) that assigns e-certificates to anyone who needs them. The certificate relies on an asymmetric encryption algorithm that contains two encryption keys, the public key and the private key.

The certificate owner can provide the other party with a certificate as proof of identity. The certificate contains the public key and other details of its owner and is signed by a trusted CA. Any data encrypted with this public key can only be decrypted with the corresponding private key, which is held by the certificate owner.

For example, when you visit a secure (HTTPS) e-commerce site, the site provides its certificate to your browser. The browser validates the certificate against its trusted CA list and uses the public key it contains to create encrypted session information that can only be decrypted by the originating server. The browser and the server can then communicate securely.

1.6.2. Using SSL with a MySQL server

Requirements for using SSL for MySQL servers:

The system must support either yaSSL (already provided with MySQL) or OpenSSL. To facilitate the use of secure connections, yaSSL has been included with MySQL. (MySQL and yaSSL use the same licensing model, but OpenSSL uses an Apache-style license. )

The version of MySQL that is being used must include SSL support.

To obtain a secure connection to use MySQL and SSL, you must first do the following:

Load OpenSSL (if you are not using a precompiled MySQL).

Configure SSL support for MySQL. To get OpenSSL, visit http://www.openssl.org. Building MySQL with OpenSSL requires a shared OpenSSL library; otherwise, a linker error will occur. To configure the MySQL source code distribution to use SSL, call CMake:

Shell > cmake. -DWITH_SSL=bundled

This operation configures the distribution to use the included yaSSL library. To use the system SSL library instead, specify the appropriate option as-DWITH_SSL=system.

Make sure that the user table in the mysql database contains columns related to SSL (ssl_* and x509 columns *). If there are no SSL-related columns in the user table (starting with ssl_* and x509 columns *), you must upgrade them using the mysql_upgrade program.

Use the-- ssl option to check whether the server binaries are compiled with SSL support. If the server does not support SSL, an error occurs:

Shell > mysqld-- ssl-- help

060525 14:18:52 [ERROR] mysqld: unknown option'--ssl'

Finally, start the server using the option of SSL.

1.6.3. Start the MySQL server using SSL

Enable the mysqld server to connect using SSL with the following options:

Angular-- ssl-ca: determine which certificate authority (Certificate Authority, CA) certificate to use (encryption required)

Get-- ssl-key: determine the server public key

Get-- ssl-cert: determine the server private key

Shell > mysqld-ssl-ca=ca-cert.pem-ssl-cert=server-cert.pem-ssl-key=server-key.pem

You can send-- ssl-cert to the client and validate against the CA certificate it is using.

1.6.4. SSL encrypted connection

To use SSL support to establish a secure connection to a MySQL server, the client options you must specify depend on the SSL requirements of the user account used by the client. In addition to common authentication based on usernames and passwords, MySQL can also check X509 certificate properties. To specify SSL-related options for the MySQL account, use the REQUIRE clause of the GRANT statement. Use the REQUIRE SSL option of the GRANT statement to allow only an account to use SSL encrypted connections:

Mysql > GRANT ALL PRIVILEGES ON test.* TO 'root'@'localhost' IDENTIFIED BY' goodsecret' REQUIRE SSL

An encrypted connection can be obtained by starting the mysql client with the-- ssl-ca option. You can also specify-- ssl-key and-- ssl-cert options for X509 connections:

Shell > mysql-ssl-ca=ca-cert.pem-ssl-cert=server-cert.pem-ssl-key=server-key.pem

There are many ways to limit the connection type for a given account. Only three options are introduced this time:

Angular REQUIRE NONE: indicates that the account has no SSL or X509 requirements. This is the default option if the SSL-related REQUIRE option is not specified. If the user name and password are valid, unencrypted connections are allowed. However, if the client has the corresponding certificate and key file, the client can also request an encrypted connection by specifying an option. That is, the client does not need to specify any SSL command options, in which case the connection is unencrypted.

Angular REQUIRE SSL: instructs the server to allow only accounts to use SSL encrypted connections.

Angular REQUIRE X509: the client must have a valid certificate, but the specific certificate, issuer, and subject matter are irrelevant. The only requirement is that one of the CA certificates should be able to verify its signature.

1.6.5. Check SSL status

Use the value of the have_ssl system variable to check whether the running mysqld server supports SSL:

Mysql > SHOW VARIABLES LIKE 'have_ssl'

+-+ +

| | Variable_name | Value |

+-+ +

| | have_ssl | YES |

+-+ +

Use the value of the ssl_cipher state variable to check whether the current server connection uses SSL:

Mysql > SHOW STATUS LIKE 'ssl_cipher'

+-+ +

| | Variable_name | Value |

+-+ +

| | ssl_cipher | DHE-RSA-AES256-SHA |

+-+ +

If the value of have_ssl is YES, the server supports SSL connections. If the value is DISABLED, the server supports SSL connections, but the corresponding-- ssl-* option is not provided at startup.

For mysql clients, you can use the STATUS or\ s command and check the SSL line:

Mysql >\ s

...

SSL: Cipher in use is DHE-RSA-AES256-SHA

...

1.6.6. Advantages and disadvantages of using SSL

Advantages:

It improves the security of applications with requirements.

Z. can be enabled for a single connection

Z. can be used for replication operations

Disadvantages:

It takes up a lot of CPU resources

It slows down the speed of client / server protocols

@ may postpone other SQL tasks

1.6.7. MySQL secure remote connection

MySQL supports SSH (secure shell, secure shell) connections to remote MySQL servers. This feature requires:

There is a SSH client on the java client

Port forwarding from client to server through SSH tunnel

There is a client application on a computer with a SSH client

Once set up, there will be a local port hosting the SSH connection to MySQL and encrypting it using SSH.

This is the end of "how to understand MySQL Security". Thank you for reading. If you want to know more about the industry, you can follow the website, the editor will output more high-quality practical articles for you!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.