MySQL database security solution _ MySQL

With the popularization of networks, more and more network-based applications are available. Network databases are one of them. One or several servers can provide services to many customers. this method brings a lot of convenience to people, but it also gives criminals a chance to take advantage of it. Because the data is transmitted over the network, it can be intercepted during transmission or accessed through a very convenient means. For the above reasons, database security is very important. Therefore, this article discusses some functions of the MySQL database in terms of network security. Account Solution

With the popularization of networks, more and more network-based applications are available. Network databases are one of them. One or several servers can provide services to many customers. this method brings a lot of convenience to people, but it also gives criminals a chance to take advantage of it. Because the data is transmitted over the network, it can be intercepted during transmission or accessed through a very convenient means. For the above reasons, database security is very important. Therefore, this article discusses some functions of the MySQL database in terms of network security.

　　Account Security

Account is the simplest security measure for MySQL. Each account consists of the user name, password, and location (generally composed of the server name, IP address, or wildcard. If john logs on from server1, the logon permission may be different from that of john logs on from server2.

The user structure of MySQL is user name/password/location. This does not include the database name. The following two commands set SELECT user permissions for database1 and database2.

Grant select on database1. * to 'ABC' @ 'server1' identified by 'password1 ';

Grant select on database2. * to 'ABC' @ 'server1' identified by 'password2 ';

The first command sets User abc to use password1 when connecting to database database1. The second command sets User abc to use password2 when connecting to database database2. Therefore, the passwords of user abc connecting to database database1 and database2 are different.

The above settings are very useful. If you only want users to have limited access to a database but cannot access other databases, you can set different passwords for the same user. If this is not done, it will cause trouble when the user finds that the user name can access other databases.

MySQL uses many authorization tables to track different permissions of users and these users. These tables are MyISAM tables in the mysql database. It is very meaningful to save the security information in MySQL. Therefore, we can use standard SQL to set different permissions.

Generally, three different types of security checks can be used in MySQL databases:

· Login verification

That is, the most common user name and password verification. Once you enter the correct user name and password, the verification will pass.

· Authorization

After successful login, you need to set specific permissions for this user. For example, whether tables in the database can be deleted.

· Access control

This security type is more specific. It involves the operations that a user can perform on a data table, such as whether the database can be edited or whether data can be queried.

Access control is composed of several privileges related to how to use and operate data in MySQL. They are both Boolean, that is, they are either allowed or not allowed. The following is a list of these privileges:

· SELECT

SELECT is to set whether the user can use SELECT to query data. If you do not have this privilege, you can only execute some simple SELECT commands, such as computing expressions (SELECT 1 + 2) or date conversion (SELECT Unix_TIMESTAMP (NOW.

· INSERT

· UPDATE

· INDEX

The INDEX determines whether you can set the INDEX of a table. If you do not have this permission, you cannot set indexes in the table.

· ALTER

· CREATE

· GRANT

If a user has this GRANT permission, he can GRANT his or her permissions to other users. That is to say, this user can share his or her own permissions with other users.

· REFERENCES

With the REFERENCES permission, you can use a field of another table as a foreign key constraint of a table.

In addition to the above permissions, MySQL also has some permissions to operate on the entire MySQL.

· Reload

This permission allows you to execute various FLUSH commands, such as flush tables and flush status.

· Shutdown

This permission allows you to disable MySQL

· Process

With this permission, you can run the show processlist and KILL commands. These commands can be used to view the MySQL processing process. you can view the SQL execution details in this way.

· File

This permission determines whether the user can execute the load data infile command. Exercise caution when giving users this permission, because users with this permission can load arbitrary files to tables, which is very dangerous for MySQL.

· Super

This permission allows the user to terminate any queries (these queries may not be executed by this user ).

The preceding permissions are very dangerous. you must be cautious when granting permissions to users.

[NextPage]

　　SSL in MySQL

The above account security is only for data transmission using a common Socket, which is very insecure. Therefore, MySQL 4.1 and later provide support for SSL (Secure Scokets Layer. MySQL uses a free OpenSSL library.

Because MySQL Linux versions are generally released along with Linux itself, SSL is not used by default for data transmission. To enable the SSL function, you need to set the hava_openssl variable:

OpenSSL has been added to the Windows version of MySQL. The preceding command is to check whether SSL is enabled for your MySQL instance.

Show variables like 'have _ openssl ';

+ --------------- + ------- +

| Variable_name | Value |

+ --------------- + ------- +

| Have_openssl | NO |

+ --------------- + ------- +

1 row in set (0.00 sec)

If NO is returned, you need to compile OpenSSL into your MySQL

Sometimes you may need to encrypt the user name and password for transmission. In this case, you can use the following GRANT command:

Grant all privileges on ssl_only_db. * to 'ABC' @ '%' identified by "password! "Require ssl;

You can also use the REQUIRE x509 option for SSL transmission:

Grant all privileges on ssl_only_db. * to 'ABC' @ '%' identified by "password! "REQUIRE x509;

You can also use require subject to specify a specific client certificate to access the database.

Grant all privileges on ssl_only_db. * to 'ABC' @ '%'

Identified by "password! "

Require subject "/C = US/ST = New York/L = Albany/O = Widgets Inc./CN = client-ray.

Example.com/emailAddress=raymond@example.com ";

Maybe you don't care about what customer licenses you are using, but only about your certificates. Then you can use require issuer to implement:

Grant all privileges on ssl_only_db. * to 'ABC' @ '%' identified by "password! "

Require issuer "/C = US/ST = New + 20 York/L = Albany/O = Widgets Inc./CN = cacert. example.

Com/emailAddress = admin@example.com ";

SSL can also be encrypted directly with a password. You can use require cipher to set the password.

Grant all privileges on ssl_only_db. * to 'ABC' @ '%' identified by "password! "

Require cipher "EDH-RSA-DES-CBC3-SHA ";

The GRANT command is used to set user permissions. The information is stored in the authorization table, which is the heart of the security system. The permissions of each user and client are saved in these tables. Correct operations on these tables will play a positive role in database security, and it will be very dangerous if you accidentally use these tables.

Next let's take a look at the five most required authorization tables in MySQL.

User

The user table stores user permissions and encrypted passwords. This table determines which users and clients can connect to the server.

Host

This table assigns permissions to each client, regardless of the user permissions. When determining whether to accept or reject a connection, MySQL first considers the user table. However, using the GRANT or REVOKE command does not affect the host table. you can manually modify the content in this table.

Db

The database table stores the permissions of the database layer.

Tables_priv

This table stores the table permission information.

Columns_priv

This table stores the permission information for individual columns. With this table, you can grant the permission to operate a column to a user.

[NextPage]

　　Hash Encryption

If the database stores sensitive data, such as bank card passwords and customer information, you may want to save the data in an encrypted form in the database. In this way, even if someone enters your database and sees the data, it is difficult to obtain the real information.

In the large amount of information in the application, you may only want to encrypt a small part, such as the user's password. These passwords should not be stored in plain text. they should be stored in the database in encrypted form. In general, most systems, including MySQL, use hash algorithms to encrypt sensitive data.

Hash Encryption is one-way encryption, that is, the encrypted string cannot obtain the original string. This method is very limited, generally only used in password verification or other places to verify. In comparison, the encrypted string is not decrypted, but the input string is encrypted in the same way, and then compared with the encrypted string in the database. In this way, even if the algorithm is known and the encrypted string is obtained, the original string cannot be restored. The bank card password is encrypted in this way.

MySQL provides four functions for Hash Encryption: PASSWORD, ENCRYPT, SHA1, and MD5. Next let's try these four functions to see what the results will be. The following uses the encrypted string "pa55word" as an example:

Let's take a look at the MD5 function.

SELECT MD5 ('pa55word ');

+ ---------------------------------- +

| MD5 ('pa55word') |

+ ---------------------------------- +

| A17a41337551d6542fd005e18b43afd4 |

+ ---------------------------------- +

1 row in set (0.13 sec)

The following is the PASSWORD function.

Select password ('pa55word ');

+ ---------------------- +

| PASSWORD ('pa55word') |

+ ---------------------- +

| 1d35c6556b8cab45 |

+ ---------------------- +

1 row in set (0.00 sec)

The following is the ENCRYPT function.

Select encrypt ('pa55word ');

+ --------------------- +

| ENCRYPT ('pa55word') |

+ --------------------- +

| Up2Ecb0Hdj25A |

+ --------------------- +

1 row in set (0.17 sec)

Each of the above functions returns an encrypted string. To distinguish the case sensitivity of an encrypted string, it is best to define this field as a char binary type when using ENCRYPT to generate an encrypted string.

Three encryption methods are listed above, but I think using MD5 encryption is the best. This is because the plaintext password can be displayed in the processing list or query logs for easy tracking. The following INSERT statement inserts a record, and the password uses MD5 for encryption:

Insert into table1 (user, pw) VALUE ('user1', MD5 ('password1 '))

You can use the following statement to verify the password:

SELECT * FROM table1 WHERE user = 'user1' AND pw = MD5 ('password1 ')

The hash encryption method can be used to encrypt the password. with this method, the password cannot be restored to plain text.