[#MDEV-9835] Valid password is not working after server restart.

Comment by Elena Stepanova [ 2016-03-30 ]

Is it one particular user, or any user?
If it's any user, please provide the full unabridged output from the console with an example (use some artificial passwords and user names):

start server
create a user
connect as this user
run SELECT CURRENT_USER()
set a new password
restart the server
try to connect again

Comment by Don [ 2016-03-30 ]

This is particular user. (Actually, there was two users, but for second one problem doesn't reappear after I did `SET PASSWORD` to the same password). Other users does not experience problems. I believe, if I create new user, as of your checklist, there will be no problem (and no new info gained from that test), as this problem is not general, but intermittent. First week I did not even know it's related to server restart, it was just "magically" user can not login to database anymore (and hist site stop working which she complains), but password was not changed. Now, when I found more detail and even workaround I report it here. I still have one "invalid" account and can run tests for it if you wish (user is given other account for his site).

Comment by Don [ 2016-03-31 ]

What feedback do you need please specify.

Comment by Elena Stepanova [ 2016-04-01 ]

Okay then, please do a similar test, but with your existing problematic user account

# As a superuser, run

set global log_warnings = 2;

# hash for password {{'test'}} (4 chars, without quote marks)

set password for <problematic account> = '*94BDCEBE19083CE2A1F959FD02F964C7AF4CFC29';

select user, host, plugin, authentication_string, password_expired, is_role from mysql.user where user = '<problematic_account_name>';

# Connect as <problematic account>

mysql -u<problematic account name> -ptest

# Run

select current_user();

# Restart the server

# As a superuser, run again

set global log_warnings = 2;

select user, host, plugin, authentication_string, password_expired, is_role from mysql.user where user = '<problematic_account_name>';

select @@log_error;

# Try to connect with the problematic account

mysql -u<problematic account name> -ptest

After you get the error, paste the whole output of the above connections, SQL queries and output, check the last lines of the error log (from @@log_error or, if it's empty, from the system log) and paste them as well.
Please also attach your cnf file(s).
Thanks.

Comment by Don [ 2016-04-05 ]

Elena,

I plan to reinstall the server some time soon, do you need any more experiments from me?

Best regards,

Comment by Elena Stepanova [ 2016-04-05 ]

do11,

No, thanks, please go ahead, no other experiments necessary. With the data you provided I can reproduce the behavior that you described.
The culprit here is mysql_old_password plugin.

When you run SET PASSWORD FOR..., on some reason the server temporarily "forgets" to use mysql_old_password plugin for the client (this part is most likely a bug), which is why the client can connect to the server. When you restart the server, it again "remembers" to use the old plugin, so the client cannot connect anymore, as the hash is interpreted not the way you expect it to. It would have been the same if you ran FLUSH PRIVILEGES instead of restarting the server.

When you run GRANT USAGE ..., the plugin field gets updated, set to an empty string, so the password starts working as you expect.

If you manually update mysql.user to remove mysql_old_password and run FLUSH PRIVILEGES afterwards, it should help, too.

I'll double-check documentation for the plugin to make sure which parts of the described above are documented and which are bugs, but you can proceed with your reinstallation. Thanks for your help.

Comment by Don [ 2016-04-05 ]

Elena,

Thank you very much!

Comment by Elena Stepanova [ 2016-04-06 ]

To some extent, all of 5.5, 10.0 and 10.1/10.2 are affected, but in a different way. I'm setting the 'Fix version/s' field to all so that it would be considered, but it does not mean all of them need to be fixed. It might even turn out to be a documentation issue.

5.5

5.5 version is affected the most. Things go wrong even with mysql_native_password.
There are comments in the test pointing at problems.
The problems might be caused by a conflict between password and authentication_string.

5.5 test
-- enable_connect_log

# The hash is for 'test'

create user foo@localhost identified via 'mysql_native_password' using '*94BDCEBE19083CE2A1F959FD02F964C7AF4CFC29';
create user bar@localhost identified by password '*94BDCEBE19083CE2A1F959FD02F964C7AF4CFC29';
create user qux@localhost identified via 'mysql_native_password';
set password for qux@localhost = '*94BDCEBE19083CE2A1F959FD02F964C7AF4CFC29';
-- query_vertical select user, host, password, plugin, authentication_string from mysql.user where user in ('foo','bar','qux');

-- echo #
-- echo # For now the behavior for all accounts is the same -- the password works
-- echo #

-- connect(con_foo,localhost,foo,test,)
select current_user();
-- disconnect con_foo

-- connect(con_bar,localhost,bar,test,)
select current_user();
-- disconnect con_bar

-- connect(con_qux,localhost,qux,test,)
select current_user();
-- disconnect con_qux

-- echo #
-- echo # Problem 1:
-- echo # After FLUSH PRIVILEGES, it stops working for qux.
-- echo # It can connect WITHOUT a password, but can't connect with it.
-- echo # For foo and bar it still works as expected.
-- echo #
-- echo # Problem 2:
-- echo # Also note the warning in the error log, which only appeared
-- echo # after the FLUSH. If the password is ignored, there should have been
-- echo # warning right away, and it should be returned to the client, to
-- echo #

-- echo #################################
-- echo ##### Last log lines before FLUSH
-- exec tail -n 2 $MYSQLTEST_VARDIR/log/mysqld.1.err
-- echo #################################

-- connection default
flush privileges;

-- echo #################################
-- echo ##### Last log lines after FLUSH
-- exec tail -n 2 $MYSQLTEST_VARDIR/log/mysqld.1.err
-- echo #################################

-- connect(con_foo,localhost,foo,test,)
select current_user();
-- disconnect con_foo

-- connect(con_bar,localhost,bar,test,)
select current_user();
-- disconnect con_bar

-- connect(con_qux,localhost,qux,,)
select current_user();
-- disconnect con_qux
-- error ER_ACCESS_DENIED_ERROR
-- connect(con_qux,localhost,qux,test,)

-- echo #
-- echo # SET PASSWORD for all three accounts to a new value 'test2',
-- echo #

--connection default
set password for foo@localhost = PASSWORD('test2');
set password for bar@localhost = PASSWORD('test2');
set password for qux@localhost = PASSWORD('test2');
-- query_vertical select user, host, password, plugin, authentication_string from mysql.user where user in ('foo','bar','qux');

-- echo #
-- echo # The new password works for all three accounts at the moment:
-- echo #

-- connect(con_foo,localhost,foo,test2,)
select current_user();
-- disconnect con_foo

-- connect(con_bar,localhost,bar,test2,)
select current_user();
-- disconnect con_bar

-- connect(con_qux,localhost,qux,test2,)
select current_user();
-- disconnect con_qux

-- echo #
-- echo # Problem 3:
-- echo # But FLUSH PRIVILEGES again - now it does not work
-- echo # - for foo: it wants the old 'test' password;
-- echo # - for qux: it wants the empty password
-- echo #

--connection default
flush privileges;

-- error ER_ACCESS_DENIED_ERROR
-- connect(con_foo,localhost,foo,,)
-- connect(con_foo,localhost,foo,test,)
select current_user();
-- disconnect con_foo

-- error ER_ACCESS_DENIED_ERROR
-- connect(con_qux,localhost,qux,test2,)
-- connect(con_qux,localhost,qux,,)
select current_user();
-- disconnect con_qux

-- connect(con_bar,localhost,bar,test2,)
select current_user();
-- disconnect con_bar


# Cleanup

--connection default
drop user foo@localhost, bar@localhost, qux@localhost;

10.0

In 10.0 problems with mysql_native_password and warnings in the error log seem to be gone; but there is a problem with mysql_old_password: if a user authenticated via this plugin gets a password in the new format, this password temporarily works, until the next FLUSH PRIVILEGES or server restart. That's exactly what was happening in this bug report, and while might be not dangerous, it's confusing. Maybe some warning is due when someone attempts to set a new password to a user identified via the old plugin.

10.0 test
-- enable_connect_log

# The hash is for 'test'

create user foo@localhost identified with 'mysql_old_password' using '378b243e220ca493';
create user bar@localhost identified by password '378b243e220ca493';
create user qux@localhost identified with 'mysql_old_password';
set password for qux@localhost = '378b243e220ca493';
-- query_vertical select user, host, password, plugin, authentication_string from mysql.user where user in ('foo','bar','qux');

-- echo #
-- echo # For now the behavior for all accounts is the same -- the password works
-- echo #

-- connect(con_foo,localhost,foo,test,)
select current_user();
-- disconnect con_foo

-- connect(con_bar,localhost,bar,test,)
select current_user();
-- disconnect con_bar

-- connect(con_qux,localhost,qux,test,)
select current_user();
-- disconnect con_qux

-- echo #
-- echo # And after FLUSH PRIVILEGES, it still works.
-- echo #

-- connect(con_foo,localhost,foo,test,)
select current_user();
-- disconnect con_foo

-- connect(con_bar,localhost,bar,test,)
select current_user();
-- disconnect con_bar

-- connect(con_qux,localhost,qux,test,)
select current_user();
-- disconnect con_qux

-- echo #
-- echo # SET PASSWORD for all three accounts to a new value 'test2',
-- echo # in the new hash format
-- echo #

--connection default
set password for foo@localhost = PASSWORD('test2');
set password for bar@localhost = PASSWORD('test2');
set password for qux@localhost = PASSWORD('test2');
-- query_vertical select user, host, password, plugin, authentication_string from mysql.user where user in ('foo','bar','qux');

-- echo #
-- echo # Problem:
-- echo # The new password works for all three accounts at the moment,
-- echo # although it should not work for foo and for qux:
-- echo #

-- connect(con_foo,localhost,foo,test2,)
select current_user();
-- disconnect con_foo

-- connect(con_bar,localhost,bar,test2,)
select current_user();
-- disconnect con_bar

-- connect(con_qux,localhost,qux,test2,)
select current_user();
-- disconnect con_qux

-- echo #
-- echo # FLUSH PRIVILEGES again - now it does not work for foo and qux:
-- echo #

--connection default
flush privileges;

-- error ER_ACCESS_DENIED_ERROR
-- connect(con_foo,localhost,foo,test2,)

-- connect(con_bar,localhost,bar,test2,)
select current_user();
-- disconnect con_bar

-- error ER_ACCESS_DENIED_ERROR
-- connect(con_qux,localhost,qux,test2,)

# Cleanup

--connection default
drop user foo@localhost, bar@localhost, qux@localhost;

10.1/10.2

Finally, in 10.1 the behavior is also different. Upon creation of a user with explicit native or old plugin the plugin field is not populated at all. I suspect it's intentional, so that it always works automatically, but then it would be good to mention it in documentation.
However, this value can be brought from a previous version, upgrade does not change it. In this case it works like 10.0 – the password is accepted until the next FLUSH or restart.

10.1 test
set global secure_auth = 0;

-- enable_connect_log

# The hash is for 'test'

create user foo@localhost identified via 'mysql_old_password' using '378b243e220ca493';
create user bar@localhost identified by password '378b243e220ca493';
create user qux@localhost identified via 'mysql_old_password';
set password for qux@localhost = '378b243e220ca493';
update mysql.user set plugin = 'mysql_old_password' where user in ('foo','qux');
flush privileges;
-- query_vertical select user, host, password, plugin, authentication_string from mysql.user where user in ('foo','bar','qux');

-- echo #
-- echo # For now the behavior for all accounts is the same -- the password works
-- echo #

-- connect(con_foo,localhost,foo,test,)
select current_user();
-- disconnect con_foo

-- connect(con_bar,localhost,bar,test,)
select current_user();
-- disconnect con_bar

-- connect(con_qux,localhost,qux,test,)
select current_user();
-- disconnect con_qux

-- echo #
-- echo # SET PASSWORD for all three accounts to a new value 'test2',
-- echo # in the new hash format
-- echo #

--connection default
set password for foo@localhost = PASSWORD('test2');
set password for bar@localhost = PASSWORD('test2');
set password for qux@localhost = PASSWORD('test2');
-- query_vertical select user, host, password, plugin, authentication_string from mysql.user where user in ('foo','bar','qux');

-- echo #
-- echo # The new password works for all three accounts at the moment,
-- echo # although it should not work for foo and for qux:
-- echo #

-- connect(con_foo,localhost,foo,test2,)
select current_user();
-- disconnect con_foo

-- connect(con_bar,localhost,bar,test2,)
select current_user();
-- disconnect con_bar

-- connect(con_qux,localhost,qux,test2,)
select current_user();
-- disconnect con_qux

-- echo #
-- echo # FLUSH PRIVILEGES - now it does not work for foo and qux:
-- echo #

-- connection default
flush privileges;

-- error ER_ACCESS_DENIED_ERROR
-- connect(con_foo,localhost,foo,test2,)

-- error ER_ACCESS_DENIED_ERROR
-- connect(con_qux,localhost,qux,test2,)

-- connect(con_bar,localhost,bar,test2,)
select current_user();
-- disconnect con_bar

# Cleanup

--connection default
drop user foo@localhost, bar@localhost, qux@localhost;
set global secure_auth = DEFAULT;

Comment by Sergei Golubchik [ 2016-04-17 ]

Let's talk 5.5.

What should the behavior be? mysql.user table is inherently redundant. If the plugin value is, say, mysql_native_password and both password and auth_string are set, where should the password be taken from? Current behavior is to respect the auth_string and ignore the password with a warning. I can make SET PASSWORD to fail, but one can still use UPDATE to set both columns to contradicting values.

Comment by Elena Stepanova [ 2016-04-17 ]

I don't really think anything should be changed in 5.5, it's just too scary. Apart from maybe FLUSH PRIVILEGES oddity, there should be no difference before and after the FLUSH, since we are not updating the table manually, but using the proper SET PASSWORD command instead.

But speaking about 5.5 hypothetically, there should be no need to specify old/native plugin at all, and neither to use auth_string for them, ever. Since old and new password formats are unmistakenly different, they should be recognized automatically.

Comment by Sergei Golubchik [ 2016-04-17 ]

There is no need to specify the old/native plugin at all, and neither to use auth_string for them, ever.

But a user can do that still, a plugin is just a plugin and can be specified using the standard plugin auth syntax.

And when a plugin is specified, MariaDB assumes that the password is in auth_string (MariaDB doesn't even know it's a password, it's simply opaque plugin specific authentication data).

To fix the “FLUSH PRIVILEGES oddity”, I can make SET PASSWORD to have no effect both before and after the FLUSH. If that's what you mean?

Comment by Elena Stepanova [ 2016-04-17 ]

To fix the “FLUSH PRIVILEGES oddity”, I can make SET PASSWORD to have no effect both before and after the FLUSH. If that's what you mean?

Yes, that's what I meant. I don't expect we'll break any user setups this way, because it's hard to imagine somebody seriously relies on the password working only till server restart, or even less. But if you sense any hidden danger at all in this change, please ignore it, it's not so important, just confusing.

For the other part, need vs can, yes, I wish a user actually couldn't do that, because it doesn't really make sense and doesn't add any flexibility, only room for human errors. But for example in 10.1, even though a user can say create user ... identified with 'mysql_native_password' (or mysql_old_password), the server does not care about it at all, does not put it into the {mysql.user.plugin}} field, hence no further problem (although, of course, it is in itself strange that the server ignores the instruction without as much as a warning).

And of course, a user can always update mysql.user table, but I'm much less worried about this scenario, as long as we document what is taken from where in which case (when password is used, when authentication_string is used, etc.) – and we should get it documented anyway unless it's already somewhere in the KB; then we can claim that users who want to tamper with such an important table, at the very least should read the documentation.

Comment by Sergei Golubchik [ 2016-04-18 ]

Not quite, 10.1 does not ignore the instruction. Privilege tables are ambiguous, there are two ways to specify the password and native/old plugin: with the password or with the plugin/auth_str pair. 10.1 normalizes the input to always use the same variant, while 5.5 and 10.0 did not do that.

I'm now fixing 5.5 to do the same. SET PASSWORD will clear plugin/auth_str fields if the plugin is native/old.

Comment by Elena Stepanova [ 2016-04-18 ]

That's not what I mean by "ignore", though.

MariaDB [test]> select @@version;

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

| @@version             |

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

| 10.1.13-MariaDB-debug |

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

1 row in set (0.00 sec)

MariaDB [test]> create user 'foo'@'localhost' identified with 'mysql_old_password';

Query OK, 0 rows affected (0.00 sec)

MariaDB [test]> select user, host, password, plugin, authentication_string from mysql.user where user='foo';

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

| user | host      | password | plugin | authentication_string |

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

| foo  | localhost |          |        |                       |

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

1 row in set (0.00 sec)

MariaDB [test]> select @@version;

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

| @@version             |

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

| 10.0.24-MariaDB-debug |

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

1 row in set (0.00 sec)

MariaDB [test]> create user 'foo'@'localhost' identified with 'mysql_old_password';

Query OK, 0 rows affected (0.00 sec)

MariaDB [test]> select user, host, password, plugin, authentication_string from mysql.user where user='foo';

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

| user | host      | password | plugin             | authentication_string |

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

| foo  | localhost |          | mysql_old_password |                       |

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

1 row in set (0.00 sec)

It doesn't matter whether we provide the USING <password hash> clause with the command or not – 10.1 keeps plugin field empty, while 10.0 puts a value in it.

Comment by George L [ 2016-12-19 ]

is this still a problem in mariadb 10.1.19 and 10.1.20 https://community.centminmod.com/posts/41792/ ?

[MDEV-9835] Valid password is not working after server restart. Created: 2016-03-30 Updated: 2016-12-19 Resolved: 2016-04-20
Status:	Closed
Project:	MariaDB Server
Component/s:	Authentication and Privilege System
Affects Version/s:	10.0.24, 5.5, 10.0, 10.1
Fix Version/s:	5.5.49, 10.0.25, 10.1.14

[MDEV-9835] Valid password is not working after server restart. Created: 2016-03-30 Updated: 2016-12-19 Resolved: 2016-04-20

5.5

10.0

10.1/10.2