Security Settings in the MySQL database

With the popularity of the network, more and more applications are based on the network. 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 criminals created an opportunity. Because the data is transmitted over the network, this can be intercepted in the process of transmission, or through extraordinary means into the database. For the above reasons, database security appears to be 10 points important. Therefore, this article discusses some of the features of MySQL database in network security.

Account security

Accounts are MySQL's simplest form of security. Each 1 account consists of a user name, password, and location (usually server name, IP, or wildcard). For example, user john may have different permissions to log in from server1 than john does from server2.

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

User permissions.

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 the database database1. The second command sets user abc to use password2 when connecting to the database database2. Therefore, user abc does not have the same password when connecting to database database1 and database2.

The above Settings are very useful. If you only want the user to have limited access to one database and no access to other databases, you can set a different password for the same user. If you don't, it can cause problems when the user finds out that the user name has access to other databases.

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

1 There are three different types of security checks that can be used in MySQL databases generally:

Login authentication

This is the most commonly used username and password authentication. But if you enter the correct username and password, the verification will pass.

authorization

After a successful login, this user is required to set its specific permissions. If you can delete tables in the database, etc.

Access control

This security type is more specific. It involves what the user can do with the table, such as whether the database can be edited, whether the data can be queried, and so on.
Access control consists of 1 privileges relating to how the data in MySQL is used and manipulated. They are booleans, which means they are either allowed or not allowed. Here is a list of these privileges:

SELECT

SELECT sets whether the user can use SELECT to query data. If the user does not have this privilege, they can only execute 1 simple SELECT command, such as evaluate an expression (SELECT 1+2), or date conversion (SELECT Unix_TIMESTAMP(NOW())), etc.

· 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 will not be set.

· ALTER
· CREATE
· GRANT
If a user has this GRANT privilege, he can grant his privilege to another user. That is, the user can share his or her permissions with other users.

· REFERENCES
With REFERENCES permissions, a user can use a field from another table as a foreign key constraint for a particular table.
In addition to the above permissions, MySQL has one more permission to operate on MySQL as a whole.

· Reload
This permission gives the user the right to execute various FLUSH commands, such as FLUSH TABLES, FLUSH STATUS, etc.

· Shutdown
This permission allows the user to turn off MySQL

· Process
With this permission, the user can execute the SHOW PROCESSLIST and KILL commands. These commands allow you to see the processing of MySQL, which allows you to see the details of the execution of SQL.

· File
This permission determines whether the user can execute the LOAD DATA INFILE command. It is prudent to give users this permission, as users with this permission can load arbitrary files into the table, which is 10 points dangerous to MySQL.

· Super
This permission allows the user to terminate any queries that may not have been executed by the user.
These types of permissions can be very dangerous, and you need to be very careful about the limits you give users.

The SSL MySQL

The above account security is only in the ordinary Socket for data transmission, which is very insecure. As a result, MySQL provides support for SSL (Secure Scokets Layer) after version 4.1. MySQL USES the free OpenSSL library.

Since MySQL's Linux version 1 is generally released with Linux itself 1, they do not transfer data using SSL by default. To turn on the SSL function, the hava_openssl variable needs to be set:

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

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 own MySQL

In some cases you may need to encrypt the user name and password. At this point, you can use the GRANT command:

GRANT ALL PRIVILEGES ON ssl_only_db.* to 'abc'@'%' IDENTIFIED BY "password!" REQUIRE SSL; 

SSL transmission is also available via the REQUIRE x509 option:

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 what customer license you're using, but only about your certificate. Then you can use REQUIRE ISSUER to do this:

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 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 was used above to set user permissions. This information is stored in authorization tables, which are the heart of the security system. The permissions that each user and client has are stored in these tables. If these tables are manipulated correctly, they can have a positive effect on database security and can be very dangerous if used carelessly.

This is the end of this article, I hope you enjoy it.