The security setting scheme in Mysql database _mysql

With the popularization of network, more and more web-based applications are used. The network database is one of them. Through one or several servers can provide services for many customers, this way to bring people a lot of convenience, but also to the outlaws created an opportunity. Because the data is transmitted over the network, this can be intercepted in the process of transmission, or through the very means of access to the database. Because of the above reasons, database security is very important. Therefore, this paper discusses some functions of MySQL database in network security.

Account security

The account is the simplest security measure for MySQL. Each account consists of a user name, a password, and a location (typically a server name, IP, or wildcard character). If user John logs on from Server1, it may be different from John's permission to log on from Server2.

MySQL's user structure is username/password/location. This does not include the name of the database. The following two commands set the Select for Database1 and Database2

User rights.

GRANT SELECT on database1.* to ' abc ' @ ' Server1 ' identified by ' password1 ';
GRANT SELECT on database2.* to ' abc ' @ ' Server1 ' identified by ' password2 ';

The first command sets the user ABC to use PASSWORD1 when connecting to the database Database1. The second command sets the user ABC to use PASSWORD2 when connecting to the database Database2. As a result, user ABC has a different password to connect to the database Database1 and Database2.

The settings above are very useful. If you only want users to have limited access to one database and not access to other databases, you can set a different password for the same user. If this is not done, it can cause problems when the user discovers that the user name has access to other databases.

MySQL uses a number of authorization tables to track the different permissions of users and those users. These tables are the MyISAM tables in the MySQL database. It makes sense to keep these security messages in MySQL. Therefore, we can use standard SQL to set different permissions.

3 different types of security checks are generally available in the MySQL database:

Logon authentication

This is the most common user name and password validation. Once you have typed in the correct username and password, this validation is available.

Authorized

After the login is successful, the user is required to set its specific permissions. If you can delete tables in the database, and so on.

Access control

This security type is more specific. It involves what the user can do with the datasheet, such as whether the database can be edited, whether data can be queried, and so on.
Access control consists of a number of privileges that involve the use and operation of data in MySQL. They are both Boolean, either allowed or disallowed. The following is a list of these privileges:

SELECT

Select is to set whether a user can query data using a SELECT. If the user does not have this privilege, then only a few simple select commands, such as a calculation expression (select 1+2), or date conversion (select Unix_timestamp (now)), can be executed.

· INSERT
· UPDATE
· INDEX
Index determines whether the user can set the index of the table. If the user does not have this permission, the index in the table cannot be set.

· Alter
· CREATE
· GRANT
If a user has this grant permission, he can delegate his or her permissions to another user. In other words, this user can share his or her own permissions with other users.

· REFERENCES
With references permissions, a user can use a field in another table as a foreign key constraint on a table.
In addition to the above permissions, MySQL has some permissions to operate on the entire MySQL.

· Reload
This permission allows the user to execute various FLUSH commands, such as FLUSH TABLES, FLUSH status, and so on.

· Shutdown
This permission allows the user to shut down MySQL

· Process
With this permission, the user can perform the show processlist and kill commands. These commands allow you to view the process of MySQL, in this way, to see the details of SQL execution.

· File
This permission determines whether the user can execute the load DATA infile command. Give the user this permission to be cautious, because users with this permission can load arbitrary files into the table, which is very dangerous for MySQL.

· Super
This permission allows the user to terminate any queries (these may not be performed by this user).
Several of these permissions are very dangerous, and you should be very cautious when granting a user authorization limit.

SSL in MySQL

The above account security is only a normal socket for data transmission, this is very unsafe. As a result, MySQL provides support for SSL (Secure scokets Layer) after version 4.1. MySQL uses a free OpenSSL library.

Because the Linux version of MySQL is typically published with Linux itself, they do not use SSL to transmit data by default. If you want to turn on the SSL feature, you need to set the HAVA_OPENSSL variable:

The Windows version of MySQL has OpenSSL added. Also the command is to see if your MySQL turned on the SSL feature.

Show VARIABLES like ' Have_openssl '; 
+---------------+-------+ 
| variable_name | Value | 
+---------------+-------+ 
| have_openssl | NO | 
+---------------+-------+ 
1 row in Set (0.00 sec) 

If you return no, then you need to compile the OpenSSL into your MySQL

At times you may need to encrypt the user name and password. At this point you can use the following Grant command:

Copy Code code as follows:

GRANT all privileges on ssl_only_db.* to ' abc ' @ '% ' identified by ' password! ' REQUIRE SSL;

You can also transfer SSL via the REQUIRE x509 option:

Copy Code code as follows:

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.

Copy Code code as follows:

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 what client license you are using, but only the one you care about is your certificate. Then you can use require issuer to achieve:

Copy Code code as follows:

GRANT all privileges on ssl_only_db.* to ' abc ' @ '% ' identified by ' password! '
REQUIRE issuer "/c=us/st=new+20york/l=albany/o=widgets inc./cn=cacert.example.
Com/emailaddress=admin@example.com ";

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

Copy Code code as follows:

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. This information is stored in the authorization table, which is the heart of the security system. The permissions that each user and client have are saved in these tables. If you operate these tables correctly, you will have a positive effect on the security of your database, which is very dangerous if you use them carelessly.

The above mentioned is the entire content of this article, I hope you can enjoy.