我可以mysql -u myuser -p mydb -h localhost
通过以下方式登录:
grant all privileges on mydb.* to myuser@'%' identified by
'1234567890123456789012345678901234567890123456789012345678901234567890123456789';
但当我这样做之后就不会了:
grant all privileges on mydb.* to myuser@'%' identified by
'12345678901234567890123456789012345678901234567890123456789012345678901234567890';
数据库密码 79 个字符的硬性限制从何而来?
答案1
正如 Mircea Vutcovici 所说,密码仅在散列后存储,这意味着它在存储时将具有固定长度。
也就是说,显然不应该有这样的限制。
我认为,所遇到的情况可能恰恰是mysql
客户应用。
该get_tty_password
函数似乎将密码读入char buff[80];
,这意味着 79 个字符 + 空终止。
(如果您使用不同的客户端,是否还会存在限制?)
答案2
存储的密码基于SHA-1所提供密码的哈希字符串。它们不是加密的,而是散列的。这意味着 mysql.user 表中的所有密码都具有相同的长度。
MariaDB [(none)]> grant all privileges on mydb.* to myuser@'%' identified by '12345678901234567890123456789012345678901234567890123456789012345678901234567890';
Query OK, 0 rows affected (0.01 sec)
MariaDB [(none)]> select host, user, password from mysql.user where user='myuser';
+------+--------+-------------------------------------------+
| host | user | password |
+------+--------+-------------------------------------------+
| % | myuser | *B3E74714C91FEC20BA4D5225155437727FBFD6CE |
+------+--------+-------------------------------------------+
1 row in set (0.00 sec)
MariaDB [(none)]> select password('12345678901234567890123456789012345678901234567890123456789012345678901234567890') ;
+----------------------------------------------------------------------------------------------+
| password('12345678901234567890123456789012345678901234567890123456789012345678901234567890') |
+----------------------------------------------------------------------------------------------+
| *B3E74714C91FEC20BA4D5225155437727FBFD6CE |
+----------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)
MariaDB [(none)]> SELECT UPPER(SHA1(UNHEX(SHA1("12345678901234567890123456789012345678901234567890123456789012345678901234567890"))));
+--------------------------------------------------------------------------------------------------------------+
| UPPER(SHA1(UNHEX(SHA1("12345678901234567890123456789012345678901234567890123456789012345678901234567890")))) |
+--------------------------------------------------------------------------------------------------------------+
| B3E74714C91FEC20BA4D5225155437727FBFD6CE |
+--------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)
MariaDB [(none)]>
将存储的哈希值与上面计算的哈希值进行比较:
select host, user, password from mysql.user;
对于“localhost”,您需要添加:
grant all privileges on mydb.* to myuser@'localhost' identified by '12345678901234567890123456789012345678901234567890123456789012345678901234567890';
您也需要添加此授权,因为“%”与“localhost”连接不匹配。
要连接,您需要在命令行中提供密码,以克服 MySQL 客户端中@Håkan Lindqvist 提到的 80 个字符的限制。
mysql -u myuser -p12345678901234567890123456789012345678901234567890123456789012345678901234567890 mydb