[#MDEV-26650] Failed ALTER USER/GRANT statement removes the password from the cache

10.7.0-opt>INSTALL SONAME 'password_reuse_check';

Query OK, 0 rows affected (0.000 sec)

10.7.0-opt>show grants for test_user@localhost ;

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

| Grants for test_user@localhost                                                                                            |

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

| GRANT ALL PRIVILEGES ON *.* TO `test_user`@`localhost` IDENTIFIED BY PASSWORD '*66C77D72F32DC78E989434B9F9057B0C6D50464F' |

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

1 row in set (0.000 sec)

10.7.0-opt>ALTER USER  test_user@localhost identified by 'dummypass';

ERROR 1396 (HY000): Operation ALTER USER failed for 'test_user'@'localhost'

10.7.0-opt>show grants for test_user@localhost ;

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

| Grants for test_user@localhost                         |

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

| GRANT ALL PRIVILEGES ON *.* TO `test_user`@`localhost` |

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

1 row in set (0.000 sec)

10.7.0-opt>

$ ./bin/mysql -utest_user -S/test/mtest/MD160921-mariadb-10.7.0-linux-x86_64-opt/socket.sock -pdummypass

ERROR 1045 (28000): Access denied for user 'test_user'@'localhost' (using password: YES)

10.7.0-opt>show grants for test_user@localhost;

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

| Grants for test_user@localhost                                                                                            |

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

| GRANT ALL PRIVILEGES ON *.* TO `test_user`@`localhost` IDENTIFIED BY PASSWORD '*BCF4F28E525ED7EE4664FFFF4DAE13EC14A6ABE1' |

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

1 row in set (0.000 sec)

10.7.0-opt>grant all on *.* to test_user@localhost identified by 'Test@123';

ERROR 1819 (HY000): Your password does not satisfy the current policy requirements

10.7.0-opt>show grants for test_user@localhost;

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

| Grants for test_user@localhost                         |

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

| GRANT ALL PRIVILEGES ON *.* TO `test_user`@`localhost` |

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

1 row in set (0.000 sec)

10.7.0-opt>

10.7.0-opt>UNINSTALL SONAME 'password_reuse_check';

Query OK, 0 rows affected (0.009 sec)

10.7.0-opt>

10.7.0-opt>show grants for test_user@localhost ;

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

| Grants for test_user@localhost                                                                                            |

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

| GRANT ALL PRIVILEGES ON *.* TO `test_user`@`localhost` IDENTIFIED BY PASSWORD '*00E247AC5F9AF26AE0194B41E1E769DEE1429A29' |

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

1 row in set (0.000 sec)

10.7.0-opt>ALTER USER  test_user@localhost identified by 'testpass';

Query OK, 0 rows affected (0.012 sec)

10.7.0-opt>show grants for test_user@localhost ;

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

| Grants for test_user@localhost                                                                                            |

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

| GRANT ALL PRIVILEGES ON *.* TO `test_user`@`localhost` IDENTIFIED BY PASSWORD '*00E247AC5F9AF26AE0194B41E1E769DEE1429A29' |

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

1 row in set (0.000 sec)

10.7.0-opt>

create user foo1@localhost identified by '<GDFH:3ghj';

show grants for foo1@localhost;

install soname "simple_password_check";

--error ER_CANNOT_USER

ALTER USER foo1@localhost identified by 'foo1';

show grants for foo1@localhost;

flush privileges;

show grants for foo1@localhost;

drop user foo1@localhost;

uninstall plugin simple_password_check;

commit dcbdec608935ea2590d88c565b928865e3a61024 (HEAD -> bb-10.4-MDEV-26650, origin/bb-10.4-MDEV-26650)

Author: Oleksandr Byelkin <sanja@mariadb.com>

Date:   Thu Oct 7 11:39:02 2021 +0200

    MDEV-26650: Failed ALTER USER/GRANT statement removes the password from the cache

    Starting from 10.4 AUTH is not part of ACL_USER so have to be safely copied.

    Patch made in line with preriouse code ideas.

    Can be also such variant:

    @@ -2306,7 +2326,8 @@ bool acl_init(bool dont_read_acl_tables)

     static void push_new_user(const ACL_USER &user)

    -  push_dynamic(&acl_users, &user);

    +  ACL_USER new_user(&acl_memroot, user);

    +  push_dynamic(&acl_users, &new_user);

       if (!user.host.hostname ||

           (user.host.hostname[0] == wild_many && !user.host.hostname[1]))

         allow_all_hosts=1;                  // Anyone can connect

    @@ -4493,7 +4514,7 @@ static int replace_user_table(THD *thd, const User_table &user_table,

           my_error(ER_PASSWORD_NO_MATCH, MYF(0));

           goto end;

    -    new_acl_user= old_row_exists ? *old_acl_user :

    +    new_acl_user= old_row_exists ? ACL_USER(thd->mem_root, *old_acl_user) :

                       ACL_USER(thd, *combo, lex->account_options, rights);

         if (acl_user_update(thd, &new_acl_user, nauth,

                             *combo, lex->account_options, rights))

commit 6ec64caec5d70467a8a9690f0ea58ea8bfb53dc4 (HEAD -> bb-10.4-MDEV-26650-2, origin/bb-10.4-MDEV-26650-2)

Author: Oleksandr Byelkin <sanja@mariadb.com>

Date:   Thu Oct 14 16:19:09 2021 +0200

    MDEV-26650: Failed ALTER USER/GRANT statement removes the password from the cache

    Starting from 10.4 AUTH is not part of ACL_USER so changes have to be done

    over a copy, and bring in the cache only in case of success.

commit c9a9ae65544e03f9585a65db9c0e6d729616a40c (HEAD -> bb-10.4-MDEV-26650-2, origin/bb-10.4-MDEV-26650-2)

Author: Oleksandr Byelkin <sanja@mariadb.com>

Date:   Thu Oct 14 16:19:09 2021 +0200

    MDEV-26650: Failed ALTER USER/GRANT statement removes the password from the cache

    Starting from 10.4 AUTH is not part of ACL_USER so changes have to be done

    over a copy, and bring in the cache only in case of success.

[MDEV-26650] Failed ALTER USER/GRANT statement removes the password from the cache Created: 2021-09-20 Updated: 2021-10-18 Resolved: 2021-10-18
Status:	Closed
Project:	MariaDB Server
Component/s:	OTHER
Affects Version/s:	10.4, 10.5, 10.6, 10.7
Fix Version/s:	10.4.22, 10.5.13, 10.6.5, 10.7.1