MySQL Database security solution

MySQL Database security solution
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.

[NextPage]

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:

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

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

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.

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.

Let's look at the most wanted 5 authorization tables in MySQL.

User

The user table holds the user's permissions and the encrypted password. This table is responsible for determining which users and clients can connect to the server.

Host

This table assigns permissions to each client, regardless of the user's permissions. When MySQL determines whether to accept or reject a connection, the first consideration is the user table. While using the grant or REVOKE command does not affect the host table, we can modify the contents of this table by hand.

Db

The DB table holds the permissions information for the database tier.

Tables_priv

This table stores the permissions information for the table.

Columns_priv

This table holds the permission information for the individual columns. With this table, you can grant a user the right to manipulate a column.

[NextPage]

Hash encryption

If the database holds sensitive data, such as a bank card password, customer information, and so on, you may want to keep the data in an encrypted form in the database. So even if someone enters your database and sees the data, it's hard to get the real information.

In the vast amount of information in your application, you may want to make a small part of the encryption, such as the user's password. These passwords should not be saved in plaintext and should be stored in encrypted form in the database. In general, most systems, including MySQL itself, encrypt sensitive data using hashing algorithms.

Hash encryption is one-way encryption, that is, a string that is encrypted cannot get the original string. This approach is very limited and is generally used only in password validation or other areas where verification is required. Instead of decrypting the encrypted string, the input string is encrypted using the same method, and then compared to the encrypted string in the database. 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 used in this way to encrypt.

MySQL provides 4 functions for hash encryption: PASSWORD, ENCRYPT, SHA1, and MD5. Now let's try these 4 functions to see what happens. We use the encrypted string "Pa55word" as an example to illustrate:

Let's take a look at the MD5 function first.

SELECT MD5 (' Pa55word ');

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

| MD5 (' Pa55word ') |

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

| A17a41337551d6542fd005e18b43afd4 |

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

1 row in Set (0.13 sec)

Here is the password function

SELECT PASSWORD (' Pa55word ');

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

| PASSWORD (' Pa55word ') |

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

| 1d35c6556b8cab45 |

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

1 row in Set (0.00 sec)

Here 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 differentiate the case of an encrypted string, it is best to define this field as a char binary type when generating an encrypted string using encrypt.

There are 3 encryption methods listed above, but I think it's best to use MD5 encryption. This is because doing so makes it easier to trace the plaintext password in the processing list or in the query log. As the following INSERT statement uses a record inserted, the password is encrypted using MD5:

INSERT into table1 (user, PW) VALUE (' User1 ', MD5 (' Password1 '))

You can verify the password by using the following statement:

SELECT * FROM table1 WHERE user = ' user1 ' and pw = MD5 (' Password1 ')

Hash encryption is a good way to encrypt passwords, using this method of encryption, the password will not be restored to plaintext.