Details
Description
Failed "ALTER USER/GRANT" statement removes the password from cache. We need to forcefully flush privileges to reload the password in 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>
|
This issue is not present if we UNINSTALL password_reuse_check plugin
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>
|
Attachments
Issue Links
- duplicates
-
MDEV-26739 Login allowed after ERROR 1396
-
- Open
-
- is caused by
-
MDEV-9245 password "reuse prevention" validation plugin
-
- Closed
-
Activity
Field | Original Value | New Value |
---|---|---|
Link | This issue relates to TODO-3118 [ TODO-3118 ] |
Priority | Major [ 3 ] | Blocker [ 1 ] |
Priority | Blocker [ 1 ] | Critical [ 2 ] |
Description |
ALTER USER statement removes the password from the cache. Failed "ALTER USER" statement also remove the password from cache. We need to forcefully flush privileges to reload the password in cache.
{noformat} 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) $ {noformat} This issue is not present if we UNINSTALL password_reuse_check plugin {noformat} 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> {noformat} |
Failed "ALTER USER" statement removes the password from cache. We need to forcefully flush privileges to reload the password in cache.
{noformat} 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) $ {noformat} This issue is not present if we UNINSTALL password_reuse_check plugin {noformat} 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> {noformat} |
Description |
Failed "ALTER USER" statement removes the password from cache. We need to forcefully flush privileges to reload the password in cache.
{noformat} 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) $ {noformat} This issue is not present if we UNINSTALL password_reuse_check plugin {noformat} 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> {noformat} |
Failed "ALTER USER/GRANT" statement removes the password from cache. We need to forcefully flush privileges to reload the password in cache.
{noformat} 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> {noformat} This issue is not present if we UNINSTALL password_reuse_check plugin {noformat} 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> {noformat} |
Summary | ALTER USER statement removes the password from the cache | Failed ALTER USER/GRANT statement removes the password from the cache |
Priority | Critical [ 2 ] | Blocker [ 1 ] |
Fix Version/s | 10.4 [ 22408 ] | |
Fix Version/s | 10.5 [ 23123 ] | |
Fix Version/s | 10.6 [ 24028 ] |
Affects Version/s | 10.5 [ 23123 ] | |
Affects Version/s | 10.6 [ 24028 ] |
Link | This issue duplicates MDEV-26739 [ MDEV-26739 ] |
Affects Version/s | 10.4 [ 22408 ] |
Status | Open [ 1 ] | In Progress [ 3 ] |
Labels | not-10.2 not-10.3 |
Assignee | Oleksandr Byelkin [ sanja ] | Sergei Golubchik [ serg ] |
Status | In Progress [ 3 ] | In Review [ 10002 ] |
Assignee | Sergei Golubchik [ serg ] | Oleksandr Byelkin [ sanja ] |
Status | In Review [ 10002 ] | Stalled [ 10000 ] |
Assignee | Oleksandr Byelkin [ sanja ] | Sergei Golubchik [ serg ] |
Status | Stalled [ 10000 ] | In Review [ 10002 ] |
Assignee | Sergei Golubchik [ serg ] | Oleksandr Byelkin [ sanja ] |
Status | In Review [ 10002 ] | Stalled [ 10000 ] |
Assignee | Oleksandr Byelkin [ sanja ] | Sergei Golubchik [ serg ] |
Status | Stalled [ 10000 ] | In Review [ 10002 ] |
Assignee | Sergei Golubchik [ serg ] | Oleksandr Byelkin [ sanja ] |
Status | In Review [ 10002 ] | Stalled [ 10000 ] |
Component/s | OTHER [ 10125 ] | |
Fix Version/s | 10.4.22 [ 26031 ] | |
Fix Version/s | 10.5.13 [ 26026 ] | |
Fix Version/s | 10.6.5 [ 26034 ] | |
Fix Version/s | 10.7.1 [ 26120 ] | |
Fix Version/s | 10.4 [ 22408 ] | |
Fix Version/s | 10.5 [ 23123 ] | |
Fix Version/s | 10.6 [ 24028 ] | |
Fix Version/s | 10.7 [ 24805 ] | |
Resolution | Fixed [ 1 ] | |
Status | Stalled [ 10000 ] | Closed [ 6 ] |
Workflow | MariaDB v3 [ 125172 ] | MariaDB v4 [ 159701 ] |
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;