Details
-
Bug
-
Status: Closed (View Workflow)
-
Major
-
Resolution: Fixed
-
10.4.15
-
None
Description
MariaDB [(none)]> show create user 'mariadb.sys'@'localhost';
|
+--------------------------------------------------------------------+
|
| CREATE USER for mariadb.sys@localhost |
|
+--------------------------------------------------------------------+
|
| CREATE USER `mariadb.sys`@`localhost` PASSWORD EXPIRE ACCOUNT LOCK |
|
+--------------------------------------------------------------------+
|
1 row in set (0.000 sec)
|
|
|
MariaDB [(none)]> CREATE USER `mariadb.sys`@`localhost` PASSWORD EXPIRE ACCOUNT LOCK;
|
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 'ACCOUNT LOCK' at line 1
|
Attachments
Activity
| Field | Original Value | New Value |
|---|---|---|
| Assignee | Robert Bindar [ robertbindar ] |
The reason why the parser only accepts "opt_account_locking opt_password_expiration" in this particular order is that, at that time, we couldn't find a solution (fast) to allow arbitrary "opt_acc_lock opt_pass_exp" ordering without allowing weird writings such as "ACCOUNT LOCK PASSWORD EXPIRE ACCOUNT LOCK ACCOUNT LOCK". So the conclusion, if that limits people in using the features properly, we can discuss a fix for that, but until then, I think it is a bug in "SHOW CREATE USER" that I should fix.
Let's wait a bit and see what others say on this so we can move in the right direction.
SHOW CREATE USER fix - bb-10.4-danielblack-MDEV-24098-show-create-user-invalid
it seemed the easiest.
Currently the documentation for both says one or the other.
The second fix on bb-10.4-danielblack-MDEV-24098-show-create-user-invalid allows either order for ACCOUNT LOCK/PASSWORD EXPIRE for CREATE USER and ALTER USER.
| Fix Version/s | 10.4 [ 22408 ] | |
| Description |
{noformat} MariaDB [(none)]> show create user 'mariadb.sys'@'localhost'; +--------------------------------------------------------------------+ | CREATE USER for mariadb.sys@localhost | +--------------------------------------------------------------------+ | CREATE USER `mariadb.sys`@`localhost` PASSWORD EXPIRE ACCOUNT LOCK | +--------------------------------------------------------------------+ 1 row in set (0.000 sec) MariaDB [(none)]> CREATE USER `mariadb.sys`@`localhost` PASSWORD EXPIRE ACCOUNT LOCK; 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 'ACCOUNT LOCK' at line 1 {noformat} |
{noformat}
MariaDB [(none)]> show create user 'mariadb.sys'@'localhost'; +--------------------------------------------------------------------+ | CREATE USER for mariadb.sys@localhost | +--------------------------------------------------------------------+ | CREATE USER `mariadb.sys`@`localhost` PASSWORD EXPIRE ACCOUNT LOCK | +--------------------------------------------------------------------+ 1 row in set (0.000 sec) MariaDB [(none)]> CREATE USER `mariadb.sys`@`localhost` PASSWORD EXPIRE ACCOUNT LOCK; 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 'ACCOUNT LOCK' at line 1 {noformat} |
| Status | Open [ 1 ] | Confirmed [ 10101 ] |
danblack (unsolicited, but I happened to have 5 mins to read it) Commented on the parser changes, grammar is ok, but names and coding style need fixing.
Is the first show create user change necessary any more, given the grammar fix? I guess if one imports the show create user dump to an older 10.4 (which one shouldn't probably do anyway).
Assuming BB is green, this look good to me.
Solicitation not required 
, thanks for the review.
The first commit could be dropped, however the test case for the second would need re-recording.
I'll relook at the style/naming of the grammar.
Having as less trouble as possible in a downgrade path sound like a good goal if its not too much trouble so I'm tempted to keep it.
| Assignee | Robert Bindar [ robertbindar ] | Daniel Black [ danblack ] |
pushed.
TODO: documentation update
create user / alter user now accept both options in either order.
previously accepted "LOCKED" ... "PASSWORD EXPIRE" order only.
| Fix Version/s | 10.4.17 [ 25024 ] | |
| Fix Version/s | 10.5.8 [ 25023 ] | |
| Fix Version/s | 10.4 [ 22408 ] | |
| Resolution | Fixed [ 1 ] | |
| Status | Confirmed [ 10101 ] | Closed [ 6 ] |
| Summary | SHOW CREATE USER generates invalid SQL (password expirty/ account lock) | SHOW CREATE USER generates invalid SQL (password expiry/ account lock) |
Hi danblack, is the patch for this MDEV pushed? Looked over the patch here and if that's the final version, please note that the yacc_ora parser wasn't updated with the new options. Also please update the naming of the options so that they are consistent with each other, i.e. opt_, option, opt.._option
Yes it was pushed. I did miss yacc_ora so bb-MDEV-24098-show-create-user-oracle-syntax submitted and I'll look at merging next week. Thanks for noticing.
Convention of opt_* is can be blank and *_option is an option of a syntax has been applied.
| Fix Version/s | 10.4.18 [ 25110 ] | |
| Fix Version/s | 10.5.9 [ 25109 ] | |
| Fix Version/s | 10.5.8 [ 25023 ] | |
| Fix Version/s | 10.4.17 [ 25024 ] |
| Workflow | MariaDB v3 [ 115331 ] | MariaDB v4 [ 158529 ] |
potential solutions:
a) change the syntax parser of CREATE USER/ALTER USER to accept both password expiry and account lock
or
b) SHOW CREATE USER outputs
{password expire option}CREATE USER ..
.
{account lock option}ALTER USER ...