關于 MySQL 密碼你應該知道的那些事

jopen 9年前發布 | 11K 次閱讀 MySQL

本文將介紹MySQL用戶密碼相關的一些知識，以及5.6中對于安全性的一些改進

MySQL用戶密碼是如何生成和保存的

如果你已經接觸MySQL一段時間了，那么想必你一定知道MySQL把所有用戶的用戶名和密碼的密文存放在mysql.user表中。大致的形式如下：

mysql [localhost] {msandbox} (mysql) > select user,password from mysql.user;
+----------------+-------------------------------------------+
| user           | password                                  |
+----------------+-------------------------------------------+
| root           | *6C387FC3893DBA1E3BA155E74754DA6682D04747 |
| plain_password | *861D75A7F79DE84B116074893BBBA7C4F19C14FA |
| msandbox       | *6C387FC3893DBA1E3BA155E74754DA6682D04747 |
| msandbox       | *6C387FC3893DBA1E3BA155E74754DA6682D04747 |
| msandbox_rw    | *6C387FC3893DBA1E3BA155E74754DA6682D04747 |
| msandbox_rw    | *6C387FC3893DBA1E3BA155E74754DA6682D04747 |
| msandbox_ro    | *6C387FC3893DBA1E3BA155E74754DA6682D04747 |
| msandbox_ro    | *6C387FC3893DBA1E3BA155E74754DA6682D04747 |
| rsandbox       | *B07EB15A2E7BD9620DAE47B194D5B9DBA14377AD |
+----------------+-------------------------------------------+
9 rows in set (0.01 sec)*

可見MySQL在其內部是不存放用戶的明文密碼的（這個也是一般程序對于敏感信息的最基礎保護）。一般來說密文是通過不可逆加密算法得到的。這樣即使敏感信息泄漏，除了暴力破解是無法快速從密文直接得到明文的。

MySQL用的是哪種不可逆算法來加密用戶密碼的

MySQL實際上是使用了兩次SHA1夾雜一次unhex的方式對用戶密碼進行了加密。具體的算法可以用公式表示：password_str = concat('*', sha1(unhex(sha1(password))))

我們可以用下面的方法做個簡單的驗證。

mysql [localhost] {msandbox} (mysql) > select password('mypassword'),concat('*',sha1(unhex(sha1('mypassword'))));
+-------------------------------------------+---------------------------------------------+
| password('mypassword')                    | concat('*',sha1(unhex(sha1('mypassword')))) |
+-------------------------------------------+---------------------------------------------+
| *FABE5482D5AADF36D028AC443D117BE1180B9725 | *fabe5482d5aadf36d028ac443d117be1180b9725   |
+-------------------------------------------+---------------------------------------------+
1 row in set (0.01 sec)

MySQL用戶密碼的不安全性

其實MySQL在5.6版本以前，對于對于安全性的重視度非常低，對于用戶密碼也不例外。例如，MySQL對于binary log中和用戶密碼相關的操作是不加密的。如果你向MySQL發送了例如create user,grant user ... identified by這樣的攜帶初始明文密碼的指令，那么會在binary log中原原本本的被還原出來。我們通過下面的例子來驗證。

創建一個用戶：

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

用mysqlbinlog查看二進制日志：

shell> mysqlbinlog binlog.000001

at 106

150227 23:37:59 server id 1 end_log_pos 223 Query thread_id=1 exec_time=0 error_code=0

use mysql/!/; SET TIMESTAMP=1425051479/!/; SET @@session.pseudo_thread_id=1/!/; SET @@session.foreign_key_checks=1, @@session.sql_auto_is_null=1, @@session.unique_checks=1, @@session.autocommit=1/!/; SET @@session.sql_mode=0/!/; SET @@session.auto_increment_increment=1, @@session.auto_increment_offset=1/!/; /!\C latin1 //!/; SET @@session.character_set_client=8,@@session.collation_connection=8,@@session.collation_server=8/!/; SET @@session.lc_time_names=0/!/; SET @@session.collation_database=DEFAULT/!/; create user plain_password identified by 'plain_pass' /!/; DELIMITER ;

End of log file

ROLLBACK / added by mysqlbinlog /; /!50003 SET COMPLETION_TYPE=@OLD_COMPLETION_TYPE/;</pre>

MySQL5.6中對于用戶密碼的安全性加強

好在MySQL5.6開始對安全性有了一定的重視，為了杜絕明文密碼出現在binlog中的情況，MySQL引入了一系列會以密文方式記錄二進制日志的命令：

CREATE USER … IDENTIFIED BY …
GRANT … IDENTIFIED BY …
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)

細心你的也許會發現，change master to master_password=''命令不在這個范疇中。這也就意味著MySQL5.6中仍然使用這樣的語法來啟動replication時有安全風險的。這也就是為什么5.6中使用帶有明文密碼的change master to時會有warning提示，具體如下：

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; Query OK, 0 rows affected, 2 warnings (0.04 sec)

slave1 [localhost] {msandbox} ((none)) > show warnings; +-------+------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | Level | Code | Message | +-------+------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | Note | 1759 | Sending passwords in plain text without SSL/TLS is extremely insecure. | | Note | 1760 | Storing MySQL user name or password information in the master info repository is not secure and is therefore not recommended. Please consider using the USER and PASSWORD connection options for START SLAVE; see the 'START SLAVE Syntax' in the MySQL Manual for more information. | +-------+------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ 2 rows in set (0.00 sec)</pre>