Bug #70744 SHOW GRANTS should indicate a authentication plugin

• Submitted: 27 Oct 2013 20:23
• Modified: 9 Mar 2015 13:38
• Reporter: Daniël van Eeden (OCA)
• Status: Closed
• Category: MySQL Server: Pluggable Authentication
• Severity: S2 (Serious)
• Version: 5.6.14, 5.6.21
• OS: Any
• CPU Architecture: Any
• Tags: authentication, plugin, Security, usability, User Management

[27 Oct 2013 20:23] Daniël van Eeden

Description:
The SHOW GRANTS output does not show the authentication plugin, and copying this to another server could result in a user without password.

How to repeat:
mysql> CREATE USER 'testuser'@'localhost' IDENTIFIED WITH authentication_pam;
Query OK, 0 rows affected (0.00 sec)

mysql> SHOW GRANTS FOR 'testuser'@'localhost'\G
*************************** 1. row ***************************
Grants for testuser@localhost: GRANT USAGE ON *.* TO 'testuser'@'localhost'
1 row in set (0.00 sec)

mysql> GRANT USAGE ON *.* TO 'testuser2'@'localhost' IDENTIFIED WITH authentication_pam;
Query OK, 0 rows affected (0.00 sec)

mysql> SHOW GRANTS FOR 'testuser2'@'localhost'\G
*************************** 1. row ***************************
Grants for testuser2@localhost: GRANT USAGE ON *.* TO 'testuser2'@'localhost'
1 row in set (0.00 sec)

Suggested fix:
Add IDENTIFIED WITH to SHOW GRANTS output.

[28 Oct 2013 5:03] MySQL Verification Team

Hello Daniel,

Thank you for the bug report.
Verified as described.

Thanks,
Umesh

[15 Oct 2014 10:58] Daniël van Eeden

This is a serious bug as executing the generated statement on another server to copy the user will result in a user without password.

create user ''@'' identified with authentication_pam as 'mysql, dbgrp=dbusr';
create user 'dbusr'@'localhost' identified by 'XXXXXXXX';
grant proxy on 'dbusr'@'localhost' to ''@'';

This results in:
mysql> show grants for ''@'';
+---------------------------------------------+
| Grants for @                                |
+---------------------------------------------+
| GRANT USAGE ON *.* TO ''@''                 |
| GRANT PROXY ON 'dbusr'@'localhost' TO ''@'' |
+---------------------------------------------+
2 rows in set (0.00 sec)

There should be a command to create the correct create user/grant. This should include:
- authentication plugin (mysql.user.plugin)
- authentication string (mysql.user.authentication_string)
- password expired? (mysql.user.password_expired)

Maybe create a 'SHOW CREATE USER <user>' syntax?

[15 Oct 2014 11:02] Daniël van Eeden

added tags and version

[3 Feb 2015 14:59] Daniël van Eeden

If RFE Bug #27761 gets implemented the results of this bug would be less severe.

[15 Feb 2015 9:37] Daniël van Eeden

In 5.7.6 this seems to be fixed.
https://dev.mysql.com/doc/refman/5.7/en/show-create-user.html

[9 Mar 2015 13:38] Paul DuBois

I'll modify
http://dev.mysql.com/doc/refman/5.7/en/show-grants.html
to point out that, although SHOW GRANTS does not display
the authentication plugin, you can use SHOW CREATE USER
to get that information.
(http://dev.mysql.com/doc/refman/5.7/en/show-grants.html)