MySQL Bugs: #13064: short password column - GRANT generate long (broken) hash

Bug #13064	short password column - GRANT generate long (broken) hash
Submitted:	8 Sep 2005 12:54	Modified:	20 Oct 2005 18:32
Reporter:	Carsten Wiedmann	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server	Severity:	S3 (Non-critical)
Version:	4.1.14	OS:	Windows (Windows)
Assigned to:	Jim Winstead	CPU Architecture:	Any

Description:
Is there something wrong with the server or with the documentation:
| Scenario 1: Short Password column in user table:
| - Only short hashes can be stored in the Password column. 
| - The --old-passwords option can be used but is superfluous because with a
|   short Password column, the server generates only short password hashes
|   anyway.

But, GRANT or PASSWORD generates only short hashes with "old-passwords" set in my.cnf. Without this, the server generates long hashes shorten to 16 Char --> login is not available.

How to repeat:
| mysql> use `mysql`;
| 
| mysql> show columns from `user` like 'Password';
| +----------+-------------+------+-----+---------+-------+
| | Field    | Type        | Null | Key | Default | Extra |
| +----------+-------------+------+-----+---------+-------+
| | Password | varchar(16) |      |     |         |       |
| +----------+-------------+------+-----+---------+-------+
| 1 row in set (0.00 sec)
| 
| mysql> grant all privileges on *.* to 'foo'@'localhost' identified by 'bar';
| Query OK, 0 rows affected (0.00 sec)

without old-passwords:
| mysql> select `User`, `Password` from `user` where `User`='foo';
| +------+------------------+
| | User | Password         |
| +------+------------------+
| | foo  | *E8D46CE25265E54 |
| +------+------------------+
| 1 row in set (0.00 sec)

with old-passwords:
| mysql> select `User`, `Password` from `user` where `User`='foo';
| +------+------------------+
| | User | Password         |
| +------+------------------+
| | foo  | 7c9e0a41222752fa |
| +------+------------------+
| 1 row in set (0.00 sec)

I tried to repeat the bug you described on a fresh installation of 4.1.14, but was unable (column is varchar(41) with old_passwods ON or OFF):

Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 1 to server version: 4.1.14-nt

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql> use mysql
Database changed
mysql> select `User`, `Password` from `user` where `User`='foo';
+------+-------------------------------------------+
| User | Password                                  |
+------+-------------------------------------------+
| foo  | *E8D46CE25265E545D225A8A6F1BAF642FEBEE5CB |
+------+-------------------------------------------+
1 row in set (0.01 sec)

mysql> show variables like 'old%';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| old_passwords | ON    |
+---------------+-------+
1 row in set (0.00 sec)

mysql> grant all privileges on *.* to 'foo2'@'localhost' identified by 'bar';
Query OK, 0 rows affected (0.00 sec)

mysql> select `User`, `Password` from `user` where `User` LIKE 'foo%';
+------+-------------------------------------------+
| User | Password                                  |
+------+-------------------------------------------+
| foo  | *E8D46CE25265E545D225A8A6F1BAF642FEBEE5CB |
| foo2 | 7c9e0a41222752fa                          |
+------+-------------------------------------------+
2 rows in set (0.06 sec)

mysql> exit
Bye

So, yes, the shorter password is generated ans stored, but...

C:\Documents and Settings\openxs>net stop MySQL41
...

I had changed old_passwords value, and:

C:\Documents and Settings\openxs>net start MySQL41
...

C:\Documents and Settings\openxs>mysql -ufoo2 -pbar
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 2 to server version: 4.1.14-nt

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql> show variables like 'old%';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| old_passwords | OFF   |
+---------------+-------+
1 row in set (0.00 sec)

I was able to log in with the passwod bar for user foo2, as you can see.

> , but was unable (column is varchar(41) with old_passwods ON or OFF):

Yes, with a column width of 41 all is correct. But with a column width of 16 the server must (like the documentation descripe) create short hashe, regardless of the "old-password" setting.

Please retest again with a short password column.

How to repeat

Prerequisites:
- 4.1 server with short (varchar(16)) `Password` column in mysql.user
- old-passwords=false

According to the manual (http://dev.mysql.com/doc/mysql/en/password-hashing.html, Scenario 1) MySQL should create short password hashes regardless of the old-passwords variable. However it doesn't:

~ $mysql -u root mysql
Your MySQL connection id is 5 to server version: 4.1.13a
mysql> show create table user\g
...
  `Password` varchar(16) collate utf8_bin NOT NULL default '',
...
mysql> grant all on test.* to foo@localhost identified by 'bar';
Query OK, 0 rows affected (0.03 sec)
mysql> select user, password from user where user='foo';
+------+------------------+
| user | password         |
+------+------------------+
| foo  | *E8D46CE25265E54 |
+------+------------------+
mysql> select password('bar'), old_password('bar');
+-------------------------------------------+---------------------+
| password('bar')                           | old_password('bar') |
+-------------------------------------------+---------------------+
| *E8D46CE25265E545D225A8A6F1BAF642FEBEE5CB | 7c9e0a41222752fa    |
+-------------------------------------------+---------------------+

As you can see, MySQL creates a new password hash and truncates it while inserting into the `user` table. Consequence: the created account does not work. Note: you have to FLUSH PRIVILEGES or mysqld will used cached credentials.

IMHO there are two bugs here:

1. The manual and the real behaviour differ. The manual says "short password column" -> "old_password() hash" but the server uses new password() hash instead.

2. When writing the created hash into the short password column there should be a warning about truncation. In fact I get a warning if I update the password column manually:

mysql> update user set password=password('bar') where user='foo';
Query OK, 1 row affected, 1 warning (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 1

mysql> show warnings;
+---------+------+-----------------------------------------------+
| Level   | Code | Message                                       |
+---------+------+-----------------------------------------------+
| Warning | 1265 | Data truncated for column 'Password' at row 5 |
+---------+------+-----------------------------------------------+

After some more discussion with the user the following turned out:

mysqld does not use the length of the `Password` column in mysql.user to detect an unconverted mysql database. If one tries to simulate an unconverted database by just truncating the `Password` column to 16 characters, the result is as documented in earlier comments. To get the expected behaviour (mysqld uses pre-4.1 password hashes without explicitly setting old_passwords=1) one has to alter the Password column in the following way:

ALTER TABLE mysql.user CHANGE password password CHAR(16) CHARACTER SET binary NOT NULL

However it is *strongly* discouraged to alter any table in the mysql database anyway.

Conclusion: there is no bug in the code. However the documentation should be changed to not longer suggest that the length of the Password column is is the one and only distinctive feature for the server to spot an unconverted user database. I would suggest to completely omit the discussion about "short" and "long" Password columns and instead refer to new-or-converted vs. unconverted user database (i.e. "MySQL can detect an unconverted user database. In this case old password hashes will be used, regardless of the setting of the old-passwords configuration option.")

The manual is correct in stating that the server uses the length
of the Password column in the user table to determine whether
to generate short hashes. This can be verified by examination
of sql_acl.cc.  However, it is possible to trigger the bug described
in this report as follows (in none of the steps should the server
be started with --old-passwords):

- Start a 4.1 server with 4.0 grant tables. Note that SHOW VARIABLES
LIKE '%pass%' shows old_passwords to have a value of ON.
Furthermore, SET old_passwords = 0 is ineffective. The server will not
allow it to be set to OFF. Nor will it generate long password hashes.
(So this is okay, and it's what is to be expected.)

- Upgrade the grant tables. Then use ALTER TABLE to modify the
Password column back to 16 characters. Stop the server and restart
it. At this point, something is strange, because SHOW VARIABLES
LIKE '%pass%' shows old_passwords to be OFF, which is different
than before. It's also wrong, because it should have detected from
the 16-character Password column that only old password hashes
can be used.  Using GRANT or SET PASSWORD at this point will
also generate a long password hash, which gets truncated when
stored in the user table.

So it appears that somehow, altering the Password column of a
converted-to-4.1-format user table causes some problem that
the server doesn't recognize that it needs to use short password
hashes.

Note that it's not really a good idea to use ALTER TABLE with the
grant tables.  But neither should the server generate password
hashes that get truncated when stored.  So this is an actual bug.

Note for bug reporter: It appears this problem can be triggered only
after manually altering a converted grant table with ALTER TABLE.
That's a piece of information that would well have been included in
the original report, because it's important for triggering the bug.
Thanks. (If there is another way that you know of to trigger the
problem, please report it.)

> Note that it's not really a good idea to use ALTER TABLE with the
> grant tables.

I know. It's not the normal way. Let me descripe my situation:
I want guarantee that all clients (4.1 and pre4.1) can work with the server,
even if "old-passwords" is not set. Because after installation the server or
"my.cnf/ini" is not under my control and the customer have no idea about
server administration/configuration. They want only use the server.

> Note for bug reporter: It appears this problem can be triggered only
> after manually altering a converted grant table with ALTER TABLE.

I should have referred better to this precondition.

> (If there is another way that you know of to trigger the problem, please
> report it.)

Ok, after some more tests I get these results.
After a clean new server installation (MySQL 4.1.x and 5.0.x) I make each
test with the same steps:
- install the system db with "mysql_create_system_tables"
- start the server (without old-passwords)
- shorten the password column with "ALTER TABLE..." (see tests below)
- restart the server (without old-passwords)
- add a new user with:
  GRANT ALL PRIVILEGES ON *.* TO 'foo'@'localhost' IDENTIFIED BY 'bar';
- selections of the password field to get the test result with:
  SELECT `User`, `Password` FROM `user` WHERE `User`='foo';
- stop the server
- delete the system db

Test 1:
-------
| USE `mysql`;
| ALTER TABLE `user`
|    CHANGE `Password` `Password` CHAR(16)
|    BINARY
|    DEFAULT '' NOT NULL;
Result: truncate long hash

Test 2:
-------
| USE `mysql`;
| ALTER TABLE `user`
|    CHANGE `Password` `Password` VARBINARY(16)
|    DEFAULT '' NOT NULL;
Result: correct short hash

Test 3:
-------
| USE `mysql`;
| ALTER TABLE `user`
|    CHANGE `Password` `Password` VARCHAR(16)
|    CHARACTER SET utf8 COLLATE utf8_bin
|    DEFAULT '' NOT NULL;
Result: truncate long hash

Test 4:
-------
| USE `mysql`;
| ALTER TABLE `user`
|    CHANGE `Password` `Password` VARCHAR(16)
|    CHARACTER SET latin1 COLLATE latin1_bin
|    DEFAULT '' NOT NULL;
Result: correct short hash

These tests I can verify with all versions from 4.1.10 to 5.0.7 except
of 4.1.1 (with 4.1.1 I can write and read a long hash in a short column?).

As I can see, the test result depends on the charset of the column.

So, I don't know what's correct and what's not. Is the server behavior wrong
or is somewhat missing in the manual? Thats your turn.

If is still somewhat unclear, please ask me.

Thanks for the additional feedback.

There is a bug here. The server checks the length of the
Password column when it starts, so it should not generate
long hashes when the column is short, and it should not
try to store them, resulting in truncated hash values.

Resetting from Docs category to Server.

A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/internals/30127

Fixed in 4.1.16 and 5.0.16.

Noted in 4.1.16, 5.0.16 changelogs.