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

Details

Type: Bug
Status: Closed (View Workflow)
Priority: Blocker
Resolution: Fixed
Affects Version/s: 10.4(EOL), 10.5, 10.6, 10.7(EOL)
Fix Version/s: 10.4.22, 10.5.13, 10.6.5, 10.7.1
Component/s: OTHER
Labels:
- not-10.2
- not-10.3

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

Ascending order - Click to sort in descending order

Ramesh Sivaraman created issue - 2021-09-20 10:38

Ramesh Sivaraman made changes - 2021-09-20 10:38

Field	Original Value	New Value
Link		This issue relates to TODO-3118 [ TODO-3118 ]

Ramesh Sivaraman made changes - 2021-09-20 10:39

Priority

Major [ 3 ]

Blocker [ 1 ]

Ramesh Sivaraman made changes - 2021-09-20 10:40

Priority

Blocker [ 1 ]

Critical [ 2 ]

Ramesh Sivaraman made changes - 2021-09-20 12:31

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}

Ramesh Sivaraman made changes - 2021-09-21 06:24

Link

This issue is caused by ~~MDEV-9245~~ [ ~~MDEV-9245~~ ]

Ramesh Sivaraman made changes - 2021-09-21 06:32

Description

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}

Ramesh Sivaraman made changes - 2021-09-21 06:32

Summary

ALTER USER statement removes the password from the cache

Failed ALTER USER/GRANT statement removes the password from the cache

Sergei Golubchik made changes - 2021-10-01 13:04

Priority

Critical [ 2 ]

Blocker [ 1 ]

Sergei Golubchik made changes - 2021-10-01 13:04

Fix Version/s		10.4 [ 22408 ]
Fix Version/s		10.5 [ 23123 ]
Fix Version/s		10.6 [ 24028 ]

Sergei Golubchik made changes - 2021-10-01 13:04

Affects Version/s		10.5 [ 23123 ]
Affects Version/s		10.6 [ 24028 ]

Sergei Golubchik made changes - 2021-10-01 13:08

Link

This issue duplicates MDEV-26739 [ MDEV-26739 ]

Sergei Golubchik made changes - 2021-10-01 13:08

Affects Version/s

10.4 [ 22408 ]

Oleksandr Byelkin made changes - 2021-10-05 12:37

Status

Open [ 1 ]

In Progress [ 3 ]

Oleksandr Byelkin made changes - 2021-10-06 07:08

Labels

not-10.2 not-10.3

Oleksandr Byelkin made changes - 2021-10-07 09:41

Assignee	Oleksandr Byelkin [ sanja ]	Sergei Golubchik [ serg ]
Status	In Progress [ 3 ]	In Review [ 10002 ]

Sergei Golubchik made changes - 2021-10-07 16:57

Assignee	Sergei Golubchik [ serg ]	Oleksandr Byelkin [ sanja ]
Status	In Review [ 10002 ]	Stalled [ 10000 ]

Oleksandr Byelkin made changes - 2021-10-15 07:42

Assignee	Oleksandr Byelkin [ sanja ]	Sergei Golubchik [ serg ]
Status	Stalled [ 10000 ]	In Review [ 10002 ]

Sergei Golubchik made changes - 2021-10-15 10:31

Assignee	Sergei Golubchik [ serg ]	Oleksandr Byelkin [ sanja ]
Status	In Review [ 10002 ]	Stalled [ 10000 ]

Oleksandr Byelkin made changes - 2021-10-18 10:13

Assignee	Oleksandr Byelkin [ sanja ]	Sergei Golubchik [ serg ]
Status	Stalled [ 10000 ]	In Review [ 10002 ]

Sergei Golubchik made changes - 2021-10-18 17:22

Assignee	Sergei Golubchik [ serg ]	Oleksandr Byelkin [ sanja ]
Status	In Review [ 10002 ]	Stalled [ 10000 ]

Oleksandr Byelkin made changes - 2021-10-18 21:06

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 ]

Sergei Golubchik made changes - 2021-12-06 21:53

Workflow

MariaDB v3 [ 125172 ]

MariaDB v4 [ 159701 ]

People

Assignee:: Oleksandr Byelkin

Reporter:: Ramesh Sivaraman

Votes:: 0 Vote for this issue

Watchers:: 4 Start watching this issue

Dates

Created:: 2021-09-20 10:38

Updated:: 2021-10-18 21:06

Resolved:: 2021-10-18 21:06

Git Integration

Error rendering 'com.xiplink.jira.git.jira_git_plugin:git-issue-webpanel'. Please contact your Jira administrators.

MariaDB Server

Details

Description

Attachments

Issue Links

Activity

People

Dates

Git Integration