Security solutions in MySQL database

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 User rights 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 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:

• Login Verification

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

• Authorization

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.