Details
-
Bug
-
Status: Closed (View Workflow)
-
Major
-
Resolution: Fixed
-
10.5.12
-
None
Description
DetailsFiling 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 InstructionsThe 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 BehaviorIn 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)
|
Attachments
Issue Links
- causes
-
-
- Closed
-
Activity
| Field | Original Value | New Value |
|---|---|---|
| Description |
{panel:title=(i) 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, and the result post-upgrade to this case. {panel} ---- {panel:title=(+) 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. {code} tar -xf maria_103_datadir.tar.xz {code} 2. Start up MariaDB 10.4+ 3. Run mysql_upgrade 4. Try to authenticate as the sql103 user, observe error: {code} [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 {code} Just for additional info, here is the global priv entry for the user post-upgrade: {code} [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": "" } {code} {panel} ---- {panel:title=(?) 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+. {panel} ---- {panel:title=(/) Known work around(s)} Update the user's password -- even to what it currently is. {panel} |
{panel:title=(i) 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, and the result post-upgrade to this case. {panel} ---- {panel:title=(+) 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. {code} tar -xf maria_103_datadir.tar.xz {code} 2. Start up MariaDB 10.4+ 3. Run mysql_upgrade 4. Try to authenticate as the sql103 user, observe error: {code} [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 {code} Just for additional info, here is the global priv entry for the user post-upgrade: {code} [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": "" } {code} {panel} ---- {panel:title=(?) 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+. {panel} ---- {panel:title=(/) Known work around(s)} Update the user's password -- even to what it currently is. I used the following bash one-liner which seemed to work, but it's maybe a bit risky (take backups and double-check what queries will get run): {code} mysql -BNe 'SELECT User,Host,Password FROM mysql.user WHERE password_expired = "Y" AND Password != "";' | while read user host password; do mysql -e "SET PASSWORD FOR '$user'@'$host' = '$password';" ; done {code} {panel} |
| Description |
{panel:title=(i) 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, and the result post-upgrade to this case. {panel} ---- {panel:title=(+) 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. {code} tar -xf maria_103_datadir.tar.xz {code} 2. Start up MariaDB 10.4+ 3. Run mysql_upgrade 4. Try to authenticate as the sql103 user, observe error: {code} [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 {code} Just for additional info, here is the global priv entry for the user post-upgrade: {code} [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": "" } {code} {panel} ---- {panel:title=(?) 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+. {panel} ---- {panel:title=(/) Known work around(s)} Update the user's password -- even to what it currently is. I used the following bash one-liner which seemed to work, but it's maybe a bit risky (take backups and double-check what queries will get run): {code} mysql -BNe 'SELECT User,Host,Password FROM mysql.user WHERE password_expired = "Y" AND Password != "";' | while read user host password; do mysql -e "SET PASSWORD FOR '$user'@'$host' = '$password';" ; done {code} {panel} |
{panel:title=(i) 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. {panel} ---- {panel:title=(+) 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. {code} tar -xf maria_103_datadir.tar.xz {code} 2. Start up MariaDB 10.4+ 3. Run mysql_upgrade 4. Try to authenticate as the sql103 user, observe error: {code} [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 {code} Just for additional info, here is the global priv entry for the user post-upgrade: {code} [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": "" } {code} {panel} ---- {panel:title=(?) 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+. {panel} ---- {panel:title=(/) Known work around(s)} Update the user's password -- even to what it currently is. I used the following bash one-liner which seemed to work, but it's maybe a bit risky (take backups and double-check what queries will get run): {code} mysql -BNe 'SELECT User,Host,Password FROM mysql.user WHERE password_expired = "Y" AND Password != "";' | while read user host password; do mysql -e "SET PASSWORD FOR '$user'@'$host' = '$password';" ; done {code} {panel} |
| Assignee | Daniel Black [ danblack ] |
| Attachment | log.txt [ 58552 ] |
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.
| Fix Version/s | 10.2 [ 14601 ] | |
| Fix Version/s | 10.4 [ 22408 ] |
| Status | Open [ 1 ] | In Progress [ 3 ] |
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.
| Assignee | Daniel Black [ danblack ] | Sergei Golubchik [ serg ] |
| Status | In Progress [ 3 ] | In Review [ 10002 ] |
| Description |
{panel:title=(i) 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. {panel} ---- {panel:title=(+) 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. {code} tar -xf maria_103_datadir.tar.xz {code} 2. Start up MariaDB 10.4+ 3. Run mysql_upgrade 4. Try to authenticate as the sql103 user, observe error: {code} [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 {code} Just for additional info, here is the global priv entry for the user post-upgrade: {code} [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": "" } {code} {panel} ---- {panel:title=(?) 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+. {panel} ---- {panel:title=(/) Known work around(s)} Update the user's password -- even to what it currently is. I used the following bash one-liner which seemed to work, but it's maybe a bit risky (take backups and double-check what queries will get run): {code} mysql -BNe 'SELECT User,Host,Password FROM mysql.user WHERE password_expired = "Y" AND Password != "";' | while read user host password; do mysql -e "SET PASSWORD FOR '$user'@'$host' = '$password';" ; done {code} {panel} |
{panel:title=(i) 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. {panel} ---- {panel:title=(+) 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. {code} tar -xf maria_103_datadir.tar.xz {code} 2. Start up MariaDB 10.4+ 3. Run mysql_upgrade 4. Try to authenticate as the sql103 user, observe error: {code} [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 {code} Just for additional info, here is the global priv entry for the user post-upgrade: {code} [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": "" } {code} {panel} ---- {panel:title=(?) 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+. {panel} ---- {panel:title=(/) Known work around(s)} -Update the user's password -- even to what it currently is. I used the following bash one-liner which seemed to work, but it's maybe a bit risky (take backups and double-check what queries will get run): {code} mysql -BNe 'SELECT User,Host,Password FROM mysql.user WHERE password_expired = "Y" AND Password != "";' | while read user host password; do mysql -e "SET PASSWORD FOR '$user'@'$host' = '$password';" ; done {code} - Edit: a better workaround fix: {code} mysql> update mysql.global_priv set Priv=JSON_SET(Priv, '$.password_last_changed', NOW()) 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) {code} {panel} |
| Description |
{panel:title=(i) 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. {panel} ---- {panel:title=(+) 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. {code} tar -xf maria_103_datadir.tar.xz {code} 2. Start up MariaDB 10.4+ 3. Run mysql_upgrade 4. Try to authenticate as the sql103 user, observe error: {code} [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 {code} Just for additional info, here is the global priv entry for the user post-upgrade: {code} [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": "" } {code} {panel} ---- {panel:title=(?) 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+. {panel} ---- {panel:title=(/) Known work around(s)} -Update the user's password -- even to what it currently is. I used the following bash one-liner which seemed to work, but it's maybe a bit risky (take backups and double-check what queries will get run): {code} mysql -BNe 'SELECT User,Host,Password FROM mysql.user WHERE password_expired = "Y" AND Password != "";' | while read user host password; do mysql -e "SET PASSWORD FOR '$user'@'$host' = '$password';" ; done {code} - Edit: a better workaround fix: {code} mysql> update mysql.global_priv set Priv=JSON_SET(Priv, '$.password_last_changed', NOW()) 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) {code} {panel} |
{panel:title=(i) 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. {panel} ---- {panel:title=(+) 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. {code} tar -xf maria_103_datadir.tar.xz {code} 2. Start up MariaDB 10.4+ 3. Run mysql_upgrade 4. Try to authenticate as the sql103 user, observe error: {code} [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 {code} Just for additional info, here is the global priv entry for the user post-upgrade: {code} [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": "" } {code} {panel} ---- {panel:title=(?) 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+. {panel} ---- {panel:title=(/) Known work around(s)} Edit: a better workaround fix: {code} mysql> update mysql.global_priv set Priv=JSON_SET(Priv, '$.password_last_changed', NOW()) 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) {code} {panel} |
| Description |
{panel:title=(i) 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. {panel} ---- {panel:title=(+) 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. {code} tar -xf maria_103_datadir.tar.xz {code} 2. Start up MariaDB 10.4+ 3. Run mysql_upgrade 4. Try to authenticate as the sql103 user, observe error: {code} [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 {code} Just for additional info, here is the global priv entry for the user post-upgrade: {code} [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": "" } {code} {panel} ---- {panel:title=(?) 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+. {panel} ---- {panel:title=(/) Known work around(s)} Edit: a better workaround fix: {code} mysql> update mysql.global_priv set Priv=JSON_SET(Priv, '$.password_last_changed', NOW()) 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) {code} {panel} |
{panel:title=(i) 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. {panel} ---- {panel:title=(+) 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. {code} tar -xf maria_103_datadir.tar.xz {code} 2. Start up MariaDB 10.4+ 3. Run mysql_upgrade 4. Try to authenticate as the sql103 user, observe error: {code} [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 {code} Just for additional info, here is the global priv entry for the user post-upgrade: {code} [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": "" } {code} {panel} ---- {panel:title=(?) 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+. {panel} ---- {panel:title=(/) Known work around(s)} Edit: a better workaround fix: {code} 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) {code} {panel} |
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
| Assignee | Sergei Golubchik [ serg ] | Daniel Black [ danblack ] |
| Status | In Review [ 10002 ] | Stalled [ 10000 ] |
| Component/s | Authentication and Privilege System [ 13101 ] | |
| 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.2 [ 14601 ] | |
| Fix Version/s | 10.4 [ 22408 ] | |
| Resolution | Fixed [ 1 ] | |
| Status | Stalled [ 10000 ] | Closed [ 6 ] |
| Workflow | MariaDB v3 [ 124288 ] | MariaDB v4 [ 159583 ] |
| Link |
This issue causes |
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.