[MDEV-19057] PASSWORD EXPIRE in multi-user CREATE or ALTER works inconsistently with other user options Created: 2019-03-27  Updated: 2019-03-27  Resolved: 2019-03-27

Status: Closed
Project: MariaDB Server
Component/s: Authentication and Privilege System, Parser
Affects Version/s: 10.4
Fix Version/s: N/A

Type: Bug Priority: Major
Reporter: Elena Stepanova Assignee: Sergei Golubchik
Resolution: Not a Bug Votes: 0
Labels: None


 Description   

When PASSWORD EXPIRE ... option is provided for the last user in multi-user CREATE or ALTER, it applies to all users in that query:

CREATE OR REPLACE USER u1 IDENTIFIED BY 'foo', u2 PASSWORD EXPIRE;
 
 
 
+---------------------------------------------------------------------------------------------------------+
 
| CREATE USER for u1@%                                                                                    |
 
+---------------------------------------------------------------------------------------------------------+
 
| CREATE USER 'u1'@'%' IDENTIFIED BY PASSWORD '*F3A2A51A9B0F2BE2468926B4132313728C250DBF' PASSWORD EXPIRE |
 
+---------------------------------------------------------------------------------------------------------+
 
+--------------------------------------+
 
| CREATE USER for u2@%                 |
 
+--------------------------------------+
 
| CREATE USER 'u2'@'%' PASSWORD EXPIRE |
 
+--------------------------------------+

When it's provided for a non-last user, it causes a parser error:

CREATE OR REPLACE USER u1 PASSWORD EXPIRE, u2 IDENTIFIED BY 'foo';
 
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near ' u2 IDENTIFIED BY 'foo'' at line 1

Other (older) options work per user, so it appears to be inconsistent.

 Comments   
Comment by Sergei Golubchik [ 2019-03-27 ]

Not quite. The syntax is (https://mariadb.com/kb/en/library/create-user/):

CREATE [OR REPLACE] USER [IF NOT EXISTS] 
 user_specification [,user_specification] ...
 
  [REQUIRE {NONE | tls_option [[AND] tls_option] ...}]
 
  [WITH resource_option [resource_option] ...]
 
  [password_option] 
 
 
user_specification:
 
  username [authentication_option]
 
 
 
authentication_option:
 
  IDENTIFIED BY 'password' 
  | IDENTIFIED BY PASSWORD 'password_hash'
 
  | IDENTIFIED {VIA|WITH} authentication_plugin
 
  | IDENTIFIED {VIA|WITH} authentication_plugin {USING|AS} 'authentication_string'
 
  | IDENTIFIED {VIA|WITH} authentication_plugin {USING|AS} PASSWORD('password')

That is, user name and the password can be specified many times, but all options, that is, ssl requirements, resource limits, password expiration and account locking — all are specified once per statement and affect all specified users.

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