What do you know about MySQL passwords?

What do you know about MySQL passwords?

This article will introduce some MySQL user password-related knowledge and some security improvements in 5.6.

How is the MySQL user password generated and saved?

If you have been in touch with MySQL for a while, you must know that MySQL stores the ciphertext of all user usernames and passwords inmysql.userTable. The general form is as follows:

1. mysql [localhost]{msandbox}(mysql)>select user,password from mysql.user;
2. +----------------+-------------------------------------------+
3. | user | password |
4. +----------------+-------------------------------------------+
5. | root |*6C387FC3893DBA1E3BA155E74754DA6682D04747|
6. | plain_password |*861D75A7F79DE84B116074893BBBA7C4F19C14FA|
7. | msandbox |*6C387FC3893DBA1E3BA155E74754DA6682D04747|
8. | msandbox |*6C387FC3893DBA1E3BA155E74754DA6682D04747|
9. | msandbox_rw |*6C387FC3893DBA1E3BA155E74754DA6682D04747|
10. | msandbox_rw |*6C387FC3893DBA1E3BA155E74754DA6682D04747|
11. | msandbox_ro |*6C387FC3893DBA1E3BA155E74754DA6682D04747|
12. | msandbox_ro |*6C387FC3893DBA1E3BA155E74754DA6682D04747|
13. | rsandbox |*B07EB15A2E7BD9620DAE47B194D5B9DBA14377AD |
14. +----------------+-------------------------------------------+
15. 9 rows inset(0.01 sec)*

It can be seen that MySQL does not Store Users' Plaintext Passwords inside it (this is also the most basic protection for sensitive information by General programs ). In general, ciphertext is obtained through irreversible encryption algorithms. In this way, even if sensitive information is leaked, the plaintext cannot be obtained directly from the ciphertext without brute-force cracking.

Which irreversible algorithm does MySQL use to encrypt user passwords?

MySQL uses two SHA1 and one unhex to encrypt the user password. The specific algorithm can be represented by a formula:password_str = concat('*', sha1(unhex(sha1(password))))

We can use the following method for simple verification.

1. mysql [localhost]{msandbox}(mysql)>select password('mypassword'),concat('*',sha1(unhex(sha1('mypassword'))));
2. +-------------------------------------------+---------------------------------------------+
3. | password('mypassword')| concat('*',sha1(unhex(sha1('mypassword'))))|
4. +-------------------------------------------+---------------------------------------------+
5. |*FABE5482D5AADF36D028AC443D117BE1180B9725 |*fabe5482d5aadf36d028ac443d117be1180b9725 |
6. +-------------------------------------------+---------------------------------------------+
7. 1 row inset(0.01 sec)

Insecure MySQL user passwords

In fact, MySQL versions earlier than MySQL 5.6 have a very low emphasis on security, and the user password is no exception. For example, MySQL does not encrypt operations related to user passwords in binary logs. If you send an examplecreate user,grant user ... identified byThe command carrying the initial plaintext password will be restored in binary log. We can use the following example to verify.

Create a user:

1. mysql [localhost]{msandbox}(mysql)> create user plain_password identified by'plain_pass';
2. Query OK,0 rows affected (0.00 sec)

Use mysqlbinlog to view binary logs:

1. shell> mysqlbinlog binlog.000001
2. # at 106
3. #150227 23:37:59 server id 1 end_log_pos 223 Query thread_id=1 exec_time=0 error_code=0
4. use mysql/*!*/;
5. SET TIMESTAMP=1425051479/*!*/;
6. SET @@session.pseudo_thread_id=1/*!*/;
7. SET @@session.foreign_key_checks=1,@@session.sql_auto_is_null=1,@@session.unique_checks=1,@@session.autocommit=1/*!*/;
8. SET @@session.sql_mode=0/*!*/;
9. SET @@session.auto_increment_increment=1,@@session.auto_increment_offset=1/*!*/;
10. /*!\C latin1 *//*!*/;
11. SET @@session.character_set_client=8,@@session.collation_connection=8,@@session.collation_server=8/*!*/;
12. SET @@session.lc_time_names=0/*!*/;
13. SET @@session.collation_database=DEFAULT/*!*/;
14. create user plain_password identified by'plain_pass'
15. /*!*/;
16. DELIMITER ;
17. # End of log file
18. ROLLBACK /* added by mysqlbinlog */;
19. /*!50003 SET COMPLETION_TYPE=@OLD_COMPLETION_TYPE*/;

Enhanced User Password Security in MySQL5.6

Fortunately, MySQL has paid some attention to security. To prevent plaintext passwords from appearing in binlog, MySQL introduces a series of commands that record binary logs in ciphertext mode:

• Create user... Identified...
• GRANT... Identified...
• Set password...
• Slave start... PASSWORD =... (As of 5.6.4)
• Create server... OPTIONS (... PASSWORD ...) (As of 5.6.9)
• Alter server... OPTIONS (... PASSWORD ...) (As of 5.6.9)

You may find that,change master to master_password=''Commands are not in this category. This means that MySQL5.6 still uses this syntax to start replication with security risks. This is why 5.6 uses a plaintext Passwordchange master toThere will be a warning prompt, as shown below:

1. slave1 [localhost]{msandbox}((none))> change master to master_host='127.0.0.1',master_port =21288,master_user='rsandbox',master_password='rsandbox',master_auto_position=1;
2. Query OK,0 rows affected,2 warnings (0.04 sec)
3. slave1 [localhost]{msandbox}((none))> show warnings;
4. +-------+------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
5. |Level|Code|Message|
6. +-------+------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
7. |Note|1759|Sending passwords in plain text without SSL/TLS is extremely insecure.|
8. |Note|1760|StoringMySQL user name or password information in the master info repository isnot secure andis therefore not recommended.Please consider using the USER and PASSWORD connection options for START SLAVE; see the 'START SLAVE Syntax'in the MySQLManualfor more information.|
9. +-------+------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
10. 2 rows inset(0.00 sec)

This article permanently updates the link address: