[MDEV-26255] show the authentication info in SHOW CREATE USER, but not in SHOW GRANTS Created: 2021-07-27 Updated: 2023-11-28 |
|
| Status: | Confirmed |
| Project: | MariaDB Server |
| Component/s: | Server |
| Affects Version/s: | 10.5 |
| Fix Version/s: | 10.11 |
| Type: | Bug | Priority: | Major |
| Reporter: | William Wong | Assignee: | Sergei Golubchik |
| Resolution: | Unresolved | Votes: | 0 |
| Labels: | None | ||
| Environment: |
redhat 7 on x86-64 |
||
| Issue Links: |
|
||||||||
| Description |
|
An enhancement request is below. We are migrating our databases from MySQL 5.7 to MariaDB 10.5. One of our standard stored procedure in MySQL 5.7 will show privileges of other users. This helps developer to check deployment. The command used in the stored procedure is "show grants" However, in MariaDB, "show grants" will output hashed password as well. This creates a security problem. We tried to generate the same show grant sql but it is quite complex and need to update stored procedure each time of brand new privilege in mariadb. A request to hide "identified by" section in "show grants". Actually, "show create user" can "identified by" section perfectly for generating create user SQL already. In MySQL, "show grants" does not show "identified by" section. https://dev.mysql.com/doc/refman/5.7/en/show-grants.html SHOW GRANTS output does not include IDENTIFIED BY PASSWORD clauses. Use the SHOW CREATE USER statement instead. See Section 13.7.5.12, “SHOW CREATE USER Statement”.
|
| Comments |
| Comment by Sergei Golubchik [ 2021-08-07 ] |
|
The "security" part of it is covered by MDEV-23266. If you'd like, you can comment there and explain how exactly hiding a password from the user who is supposed to know it anyway affects security. But I'm not closing it as a duplicate, because the behavior you noted is indeed inconsistent. One should use CREATE USER or ALTER USER to set the password and GRANT to manage privileges. So logically SHOW CREATE USER should show the password and SHOW GRANTS should not. It does so for historical reasons, but I agree we need to change it. SHOW CREATE USER + SHOW CREATE GRANT should provide complete set of SQL statements to recreate a user account. But there is no reason why it needs to set the password twice. |
| Comment by William Wong [ 2021-08-07 ] |
|
Thanks. This issue is a bit different from MDEV-23266. Looks like MDEV-23266 needs no hashed password in "show create user" as well. Anyway, agree to keep this issue open. |