[MDEV-26363] Passwords incorrectly expiring after MySQL5.7 -> MariaDB10.3 -> 10.4+ upgrades. Created: 2021-08-14  Updated: 2021-10-20  Resolved: 2021-10-20

Status: Closed
Project: MariaDB Server
Component/s: Authentication and Privilege System
Affects Version/s: 10.5.12
Fix Version/s: 10.4.22, 10.5.13, 10.6.5

Type: Bug Priority: Major
Reporter: Hans Borresen Assignee: Daniel Black
Resolution: Fixed Votes: 0
Labels: None
Environment:

OS: CentOS Linux release 7.9.2009
Kernel: 3.10.0-1160.25.1.el7.x86_64
Arch: x86_64
Environment: kvm
CPU: Intel Xeon Processor (Skylake, IBRS) w/ 2 core(s)
MySQL: mysql Ver 15.1 Distrib 10.5.12-MariaDB, for Linux (x86_64) using readline 5.1

Attachments: Text File log.txt     File maria_103_datadir.tar.xz    

 Description   
Details

Filing this per the discussion in zulip chat (https://mariadb.zulipchat.com/#narrow/stream/118759-general/topic/Password.20issues.20after.205.2E7.20-.3E.2010.2E3.20-.3E.2010.2E5.20upgrade.20path)

Password expiration existed in MySQL 5.7, but not in MariaDB 10.3.
On servers that upgraded from 5.7 to 10.3, there are leftover "cruft" fields in mysql.user relating to password expiry that do not get used.
When creating users on a 10.3 system, the "password_last_changed" field is initialized as 0000-00-00 00:00:00

However, password expiration was added in 10.4 – and on such systems, users who had 0000-00-00 00:00:00 as their password_last_changed get their passwords expired – even if password expiration is not enabled.

I've attached a sample affected 10.3 datadir to this case.

Step-by-Step Instructions

The basic premise is to start on MySQL 5.7, upgrade to 10.3, create a user, then upgrade to 10.4+.

However, I have a sample affected datadir ready.

1. Extract the contents of maria_103_datadir.tar.xz to your datadir.

tar -xf maria_103_datadir.tar.xz

2. Start up MariaDB 10.4+

3. Run mysql_upgrade

4. Try to authenticate as the sql103 user, observe error:

[root@10-1-33-101 ~]# mysql -u sql103 -p'TestPassPleaseIgnore' -e 'SHOW DATABASES;'
 
ERROR 1820 (HY000) at line 1: You must SET PASSWORD before executing this statement

Just for additional info, here is the global priv entry for the user post-upgrade:

[root@10-1-33-101 ~]# mysql -BNe 'SELECT Priv from mysql.global_priv WHERE User="sql103" AND Host="localhost";' | python -m json.tool
 
{
 
    "access": 0,
 
    "account_locked": false,
 
    "authentication_string": "*CA8C7DC8B54EDC492104531EA6612FC243D816C3",
 
    "default_role": "",
 
    "is_role": false,
 
    "max_connections": 0,
 
    "max_questions": 0,
 
    "max_statement_time": 0.0,
 
    "max_updates": 0,
 
    "max_user_connections": 0,
 
    "password_last_changed": 0,
 
    "password_lifetime": -1,
 
    "plugin": "mysql_native_password",
 
    "ssl_cipher": "",
 
    "ssl_type": 0,
 
    "x509_issuer": "",
 
    "x509_subject": ""
 
}

Suggested Resolution / Expected Behavior

In this specific use case (where password expiration was not configured to occur), I would not expect the user's password to expire after upgrading to MariaDB 10.4+.

Known work around(s)

Edit: a better workaround fix:

mysql> update mysql.global_priv set Priv=JSON_SET(Priv, '$.password_last_changed', UNIX_TIMESTAMP()) WHERE JSON_VALUE(Priv, '$.password_last_changed') = '0';
 
Query OK, 25 rows affected (0.01 sec)
 
Rows matched: 25  Changed: 25  Warnings: 0
 
 
 
mysql> flush privileges;
 
Query OK, 0 rows affected (0.00 sec)



 Comments   
Comment by Hans Borresen [ 2021-08-14 ]

Just to note – I tested only on 10.5.12, not 10.4.x. However, I suspect you'd encounter the issue on 10.4 as well.

Comment by Daniel Black [ 2021-08-14 ]

Confirmed. In commit 59e6d14c47aa the 'MODIFY IF EXISTS password_last_changed timestamp DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP AFTER max_statement_time' has absolutely no effect made worse by the default value ending up as 0000-00-00 00:00:00 as described.

Correct 10.2, 10.3 behavior to actually have the value as described.

10.4+ have the mysql_upgrade detect this false behavior and just give the user a valid password_last_changed like

patch against 10.4

 
diff --git a/scripts/mysql_system_tables_fix.sql b/scripts/mysql_system_tables_fix.sql
 
index 9dd775aaf30..5623d73f93f 100644
 
--- a/scripts/mysql_system_tables_fix.sql
 
+++ b/scripts/mysql_system_tables_fix.sql
 
@@ -815,7 +815,7 @@ IF 'BASE TABLE' = (select table_type from information_schema.tables where table_
 
                     'max_statement_time', max_statement_time,
 
                     'plugin', if(plugin>'',plugin,if(length(password)=16,'mysql_old_password','mysql_native_password')),
 
                     'authentication_string', if(plugin>'' and authentication_string>'',authentication_string,password),
 
-                    'password_last_changed', if(password_expired='Y', 0, UNIX_TIMESTAMP(password_last_changed)),
 
+                    'password_last_changed', if(password_expired='Y', 0, if(password_last_changed, UNIX_TIMESTAMP(password_last_changed), NOW())),
 
                     'password_lifetime', ifnull(password_lifetime, -1),
 
                     'account_locked', 'Y'=account_locked,
 
                     'default_role', default_role,

Tested the above and it prevents mysql_upgrade from locking the users created.

Comment by Daniel Black [ 2021-08-16 ]

Sergei,

Can I get a review on both bb-10.2-danielblack-MDEV-26363 and bb-10.4-MDEV-26363-danielblack-zero_last_password_changed

The 10.2 change (not to be merged to 10.4, similar exists there) implements that password expired account in 5.7 get the chance to be unlocked by set password= in 10.2,10.3 (without passwords being enforced), so by the time a 10.4 mysql_upgrade is done, the password_last_changed and password_expired reflect current values.

The MySQL-5.7 behavior leaves password_last_changed as NULL on account creation, and will change its value on GRANT ... IDENTIFIED BY/VIA, ALTER USER .. IDENTIFIED which I currently haven't implemented strictly. As both CREATE USER and GRANT both utilize the sql/sql_acl.cc:replace_user_table method it becomes harder to implement both consistently without more restructuring.

Guidance as to how far to take this 10.2 implementation (if any) appreciated.

10.4 mysql_upgrade checks, fairly basic - a 0 value is treated as it was updated/created sometime. I don't think its possible to distinguish which, so assume it was an update and record the current time and give users some extra time.

Comment by Sergei Golubchik [ 2021-10-19 ]

commit 2fad51d7915 in 10.4 is ok to push.

10.2 fix — I don't think it's needed, 10.2 and 10.3 don't update fields they don't know about, it's by no means a bug. Adding a new feature to 10.2 and 10.3 is kind of late now, I'd say

Comment by Daniel Black [ 2021-10-20 ]

Thanks serg.

Generated at Thu Feb 08 09:44:44 UTC 2024 using Jira 8.20.16#820016-sha1:9d11dbea5f4be3d4cc21f03a88dd11d8c8687422.