MySQL Security Configuration

MySQL Security Configuration

MySQL_Help_Link

1. Security Policy 1.1 data security in the management sense

To access a MySQL database, you must first access a certain permission of the database, that is, to log on as a user in a permission mode. Most of the security management is implemented through the permission of the user in the mode.

The permission information of MySQL is stored in the system table of grant tables, that is, mysql. user (Global permissions), mysql. db (Database-level permissions), mysql. host (Database-level permissions), mysql. table_priv (Table-level permissions) and mysql. in the column_pr (column-level permission) Table, MySQL enters the memory at startup. GRANT, REVOKE, create user, and drop user should be used whenever possible to change users and permissions. For example, grant select. UPDATE, DELETE, INSERT, execute on test_shop. * TO 'test_guest '@ 'localhost ';

View a user's permissions, such as show grants for 'test _ guest '@ 'localhost'

1.2 fault prevention Data Security

Data files are operating system-level objects. Therefore, they are generally quite vulnerable and dependent on the performance of the operating system. The failure of individual data blocks on a large data file may result in unavailability of the entire data file, which is disastrous for a system, in addition, it is difficult and time-consuming to restore large tablespaces or data files.

Partitions of large objects are also safe in terms of performance. When a disk error makes a separate data block in a large table unreadable, the entire table may become unavailable, the entire tablespace that contains the table must be restored.

Consider the Data Warehouse issue. You can perform the following operations:

Use myisampack to generate a compressed and read-only MyISAM table for tables with a large data volume and no write operations. It can compress 40%-50% of the table file space. The procedure is as follows:

A compressed file:> myisampack ../data/music_shop/table name. MYI

B re-indexing:> myisamchk-rq -- sort-index -- analyze ../data/test_shop/table name. MYI

C force mysqld to use the new table:> mysqladmin flush-tables

To perform a write operation, you can decompress a compressed table to restore the original state and use myisamchk. For example, myisamchk -- unpack ../data/music_shop/table name. MYI

Finally, as the amount of data increases, the system will find that the disk space under the Data Directory is getting lower and lower, causing security risks. Two measures can be taken. A table for the MyISAM storage engine. When creating a table, specify the data directory And index directory to different disk spaces, which are stored in the data directory by default. Another table for the InnoDB Storage engine cannot be separated because data files are stored together with index files. When the disk space is insufficient, you can add a new data file, which is placed on a disk with sufficient space. For details, refer to the innodb_data_file_path parameter settings.

1.3 disaster recovery and backup mechanisms

Create a master-slave database cluster using MySQL Replication

Advantages of MySQL replication:

1. If a problem occurs on the master server, you can quickly switch to the slave server;

2. query operations can be performed on the slave server to reduce the access pressure on the master server;

3. Backup can be performed on the slave server to avoid affecting the master server during the backup;

Notes:

Because asynchronous replication is implemented, there is a certain gap between the master and slave servers. The data difference must be taken into account when querying from the server. Generally, only data with low real-time requirements can be queried from the server.

Regularly backs up files and data and saves files and data in various ways.

The following are some preventive measures:

Prepare a database backup/recovery plan and carefully test the plan.

Start the binary change log of the database server. The system overhead of this function is very small (about 1%). The binary log contains all the updates made after the backup. We have no reason not to do this. (Log-bin = file, which can be left unspecified)

Periodically check data tables to prevent data from being burned.

Regularly back up backup files to prevent invalid backup files.

Put the MySQL data directory And backup file in two different drives to balance disk I/O and increase data security.

2 Security Risks 2.1 correctly set Directory Permissions

The principle of setting directory permissions is to separate software from data, as follows:

1. Install mysql under a separate user

2. During installation, the installation is performed as the root user. By default, the mysql software has the root permission.

3. After installation, set the data directory permission to the user permission for running mysql, for example:

Chown-R mysql: mysql/home/mysql/data

2.2 avoid running mysql with root permission

After setting 4.1 directory permissions, you can start, stop mysql, and perform routine maintenance in

Mysql user, there is no need to su to root and then use-user = mysql to start and close mysql,

In this way, there is no need to authorize the root permission of the maintenance personnel, and the most important thing is that any user with the FILE Permission can use the root to create files.

2.3 Delete an anonymous account

In some versions of MySQL, an empty account (User = '') will be installed after installation. This account has full permissions on the test database. To prevent this account from creating a large table after login, disk space used, affecting system security. We recommend that you delete it.

Drop user ''@ 'localhost ';

Drop user ''@ 'localhost. localdomain ';

2.4 set a password for the root account

We recommend that you use a Chinese alphabet as the password. For example, set password = PASSWORD ('woshiyitiaoyu ')

Only access through localhost is allowed.

2.5 grant only account Permissions

For example, Grant select, insert, update, delete on tablename to 'username' @ 'hostname'

2.6 Except root, no user shall have access to the mysql database user table

If you have the permission to access the user table in the mysql database (select, update, insert, delete ),

You can easily add, modify, and delete other user permissions, resulting in system security risks.

For example, use mysql; delete from db where user <> 'root' and db = 'mysql'

2.7 do not grant file, process, or super permissions to accounts other than the administrator.

This vulnerability may cause leakage of confidential information, view the actions performed by administrators, and execute kill commands by common users.

The FILE permission can be abused to read any files that MySQL can read on the server host into the database table. Include anyone-readable files and files in the server data directory. You can use SELECT to access the database table and transmit its content to the client. Do not grant FILE permissions to non-administrator users.

Any user with this permission can write a file in the file system with the mysqld daemon permission! For greater security, all files generated by SELECT... into outfile are writable to everyone, and you cannot overwrite existing files.

The file Permission can also be used to read any files that can be read or accessed by Unix users who are running servers. With this permission, you can read any file into the database table. This may be abused. For example, you can load "/etc/passwd" into a database table by using LOADDATA, and then display it with SELECT. The PROCESS permission can be used to view the plain text of the currently executed query, including the query for setting or changing the password.

SUPER permission can be used to terminate other users or change the operation method of the server. For example, the kill PROCESS should not grant the PROCESS or SUPER permission to non-administrator users. The output of mysqladmin processlist shows the current query body. If another user sends an UPDATE user SETpassword = PASSWORD ('not _ secure ') query, any user allowed to execute that command may see

2.8 security issues caused by LOAD DATA LOCAL

Transmission of the MySQL server startup file from the client to the server host. Theoretically, the patched server can tell the client program to transmit the selected files from the server, instead of using the load data statement.

The specified file. In this way, the server can access any file on the client that the client has read access permissions.

In the Web environment, the customer connects from the Web server, you can use load data local to read any files that the Web server process has read access permissions (assuming that you can run any commands on the SQL Server ). In this environment, the MySQL server is actually a Web server, rather than connecting to the Web server.

The program run by the user of the server.

Solution:

You can use the -- local-infile = 0 option to start mysqld and disable all load data from the server.

LOCAL Command.

For the mysql command line client, you can enable LOAD by specifying the -- local-infile [= 1] Option.

Data local, or disable it using the -- local-infile = 0 option. Similarly, for mysqlimport, -- local or-L options, enable local data file loading. In any case, the server needs to enable related options for successful local loading.

2.9 potential security vulnerabilities with the MERGE storage engine

Merge tables may have the following security vulnerabilities in some versions:

User A grants table T permissions to user B

User B creates a merge table containing T and performs various operations.

User A revokes the T permission

Security risk: User B can still access data in Table A through the merge table

2.10 avoid using symlinks to access tables

Do not allow table symbolic links. (You can disable the -- skip-symbolic-links option ). If you

It is particularly important to run mysqld with root, because anyone who has the write access permission to the server's data directory

You can delete any file in the system!

2.11 prevent DNS Spoofing

If you do not trust your DNS, you should use IP numbers instead of host names in the authorization table. In any situation, you should be very careful to use the host name containing wildcards to create authorization table entries!

2.12 The drop table command does not revoke the previous access authorization.

When you drop a table, the permissions of other users are not revoked. As a result, when you recreate a table with the same name, the permissions of other users are automatically granted to the table, resulting in permission outflow.

Therefore, when deleting a table, you must cancel the corresponding permissions of other users on the table.

2.13 REVOKE command Vulnerability

Grant all privileges on *. * to guest @ localhost; after

Revoke all privileges on *. * from guest @ localhost; does not work. You must use revoke separately for each data.

2.14 if possible, add access IP address restrictions to all users

Adding an ip address limit to all users will deny connections from all unknown hosts, so that only trusted hosts are allowed.

To connect. For example:

Grant select on dbname. * to 'username' @ 'IP' identified by 'passwd ';

2.15 strict control of operating system accounts and permissions

Strictly control the account and permissions of the operating system on the database server, for example:

Lock mysql users

All other users log on with an independent account. The administrator can use a common user to manage mysql, or use root su to manage mysql.

Modifying any resources of a mysql user is prohibited.

2.16 Add a firewall

Purchase a firewall. This protects you against at least 50% of various types of attacks in various software. Put MySQL in the firewall or isolation zone (DMZ)

2.17 strict Mode

SQL-mode = "STRICT_TRANS_TABLES, NO_AUTO_CREATE_USER, NO_ENGINE_SUBSTITUTION"

2.18 restrict MYSQL access directories

-- Chroot

2.19 Prevent the use of TCP/IP sockets when connecting to MYSQL

-- Skip-networking

2.20 prevent the host name from being used when connecting to the MYSQL database.

-- Skip-name-resolve

2.21 prevent users without the show databases permission from using this command

-- Skip-show-database

2.22 if you do not have the INSERT permission on the user table, you can prevent these users from using the GRANT command to create users

-- Safe-user-create

3. Other security configurations

MySQL itself has some options. Using these options will make the database more secure.

3.1 Use skip-network

TCP/IP connections are not allowed on the network. All connections to the database must be performed by Named Pipes (Named Pipes), Shared Memory (Shared Memory), or unix socket files. This option is suitable for applications and databases sharing a single server. Other clients will not be able to remotely access the database through the network, which greatly enhances the database security, but also makes management and maintenance inconvenient. MySQL can only connect to and interact with clients through named pipes or shared memory (in widows) or Unix socket files (in Unix systems. The configuration example is as follows:

Skip-networking

-S is short for -- socket, such as-s/tmp/mysql. sock, and its value must be the same as that set on the server.

-- Protocol strictly specifies the connection type. If some settings use the default value, such as windows server settings -- socket = mysql (mysql is the default value ), after -- protocol = pipe is specified during connection, -- socket = mysql can be omitted.

1. Named Pipe

It is only suitable for connecting to MySQL on the local machine in Windows, and the performance is improved by 30% ~ 50%.

Server setting requirements

Enable-named-pipe # Or named_pipe = ON

Socket = MySQL

Client Connection

Mysql -- protocol = pipe -- socket = mysql

2. Shared Memory

After version 4.1, mysql also provides shared memory connections for windows systems.

Server setting requirements

Shared-memory = ON

Shared_memory_base_name = MYSQL

Client Connection

Mysql -- protocol = memory -- shared-memory-base-name = mysql

3. UNIX socket

In linux and unix environments, you can use unix domain sockets to connect to mysql on the same machine;

Server setting requirements

Socket =/tmp/mysql. sock

Client Connection

Mysql -- protocol = socket -- socket =/tmp/mysql. sock

3.2 allow-suspicious-udfs

This option controls whether user-defined functions with only xxx characters can be loaded into the main function. By default, this option is disabled and can only load udfs with at least auxiliary characters. This prevents functions from loading shared object files that never contain valid udfs.

3.3 old-passwords

Force the server to generate a short (pre-4.1) password hash for the new password. It is useful to ensure compatibility when the server must support Old Client versions.

3.4 safe-user-create

If enabled, you cannot use the GRANT statement to create a new user, unless you have an INSERT

Permission. If you want to authorize a user to create a new user, you must grant the following permissions to the user:

Mysql> grant insert (user) ON mysql. user TO 'user _ name' @ 'host _ name ';

In this way, you cannot directly change the permission column. You must use the GRANT statement to GRANT this permission to other users.

3.5 secure-auth

An account with an old (pre-4.1) password cannot be authenticated

3.6 skip-grant-tables

This option causes the server to not use the permission system at all. This gives everyone the right to access all databases! (By executing the mysqladmin flush-privileges or mysqladmin reload command, or executing the flush privileges statement, you can tell a running server to start using the authorization table again. )

3.7 skip-show-database

This option allows only users with the show databases permission to execute the show databases statement.

The statement displays the names of all databases. If this option is not used, all users are allowed to execute show databases,

Only the database names with show databases or partial database permissions are displayed. Please note that global permission

Limited to database permissions.

3.8 use SSL

SSL (Secure Socket Layer Secure Socket) is a security protocol first developed by Netscape to ensure the security of data transmission over the Internet, this ensures that data is not intercepted during network transmission.

Use Cases in master-slave database replication to provide the following service assurance.

A) authenticate users and servers to ensure that data is sent to the correct customers and servers.

B) encrypt data to prevent data theft.

C) maintain data integrity and ensure that data is not damaged during transmission.

To use SSL for secure transmission in MySql, you must set the 'ssl 'option in the command line or option file. The following is an example of how to install the SDK.

A. Install the certificate management tool

A) The required parts win32openssl-0_9_8g.exe can be downloaded from the Internet.

B) install win32openssl-0_9_8g.exe as prompted. Install it in the C: \ OpenSSL directory

C) Create the root, server, and client sub-paths under the C: \ OpenSSL \ bin directory.

D) the username and password entered during certificate creation must be properly saved.

B. Create a root certificate and sign it with self-signed

A) create a private key to Enter the DOS window, Enter the C: \ OpenSSL \ bin path, then Enter the openssl genrsa-out root/root-key.pem 1024 command, and press Enter.

B) Create a certificate request to continue entering openssl req-new-out root/root-req.csr-key root/root-key.pem, and then press Enter to Enter a series of information as needed, but CommonName: Enter the root

C) Self-Signed root Certificate continue entering openssl x509-req-in root/root-req.csr-out root/root-cert.pem-signkey root/root-key.pem-days 3650, then press Enter

D) To view the root certificate content, first Enter the certificate path example: C: \ OpenSSL \ bin \ root, then Enter the keytool-printcert-file root-cert.pem, and then press Enter.

C. Create a server certificate and use the root certificate to sign it

A) create a private key to Enter the DOS window, Enter the C: \ OpenSSL \ bin path, then Enter the openssl genrsa-out server/server-key.pem 1024 command, and press Enter.

B) Create a certificate request to continue entering openssl req-new-out server/server-req.csr-key server/server-key.pem, then press Enter to Enter a series of information that can be entered based on the actual situation, but CommonName: Enter the localhost or server domain name (if the domain name exists ).

C) sign server Certificate continue entering openssl x509-req-in server/server-req.csr-out server/server-cert.pem-signkey server/server-key.pem-CA root/root-cert.pem-CAkey root/root-key.pem-CAcreateserial-days 3650, then press Enter.

D) To view the server certificate content, first Enter the certificate path example: C: \ OpenSSL \ bin \ server, then Enter the keytool-printcert-file server-cert.pem, and then press Enter.

D. Create a customer certificate and sign it with the root certificate

A) create a private key to Enter the DOS window, Enter the C: \ OpenSSL \ bin path, then Enter the openssl genrsa-out client/client-key.pem 1024 command, and press Enter.

B) Create a certificate request to continue entering openssl req-new-out client/client-req.csr-key client/client-key.pem, then press Enter to Enter a series of information that can be entered according to the actual situation, CommonName: enter the user ID.

C) sign customer Certificate continue entering openssl x509-req-in client/client-req.csr-out client/client-cert.pem-signkey client/client-key.pem-CA root/root-cert.pem-CAkey root/root-key.pem-CAcreateserial-days 3650, then press Enter.

D) To view the client certificate content, first Enter the certificate path example: C: \ OpenSSL \ bin \ client, then Enter the keytool-printcert-file client-cert.pem, and then press Enter.

After completing the preceding steps, copy the generated root, server, and client folders to the C: \ mysll directory. Now, the related options used to start the server have been deployed to specify the Certificate file and key file. Before establishing an encrypted connection, you must prepare three files. One CA certificate is a certificate issued by a trusted third party to verify the certificate provided by the client and the server. CA certificates can be purchased from or generated by a commercial organization. The second file is the certificate file used to prove its identity to the other party during connection. The third file is the key file used to encrypt and decrypt data transmitted over the encrypted connection. The certificate files and key files on the MySQL server must be installed first, with several files in the myssl Directory: root-cert.pem (CA certificate), server-cert.pem (server certificate), and server-key.pem (server Public Key ).

The user used to create the slave database operation in the primary database, and specify that SLL authentication is required.

Create user 'test _ guest '@ 'localhost' identified by '123 ';

Grant all privileges on music_shop. * TO 'test_guest '@ '10. 12.1.42' REQUIRE ssl;

Shut down the primary database

> Mysqladmin-uroot shutdown

Restart the server to make the configuration take effect.

> Mysqld -- ssl-ca = C: \ myssl \ server \ root-cert.pem -- ssl-cert = C: \ myssl \ server \ server-cert.pem -- ssl-key = C: \ myssl \ server \ server-key.pem

Use a client program to establish an encrypted connection.

> Mysql-u test_guest -- ssl-ca = C: \ myssl \ client \ root-cert.pem -- ssl-cert = C: \ myssl \ client \ client-cert.pem -- ssl-key = C: \ myssl \ client \ client-key.pem

After the configuration is complete, call the mysql program running \ s or show status like 'ssl % 'command. If you see the SSL: information line, it indicates the encrypted connection. If you write SSL-related configurations into the option file, the connection is encrypted by default. You can also use the -- skip-ssl option of the mysql program to cancel the encrypted connection.