MySQL database security solution (1)

Guidance: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.MySQL databaseNo exception. The security of the MySQL database is threatened by criminals. To ensure the security of data in the database, we will introduce the solution below.
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:

Logon 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.

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.

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.