MySQL Bugs: #78888: SHOW GRANTS requires the SELECT privilege for the mysql DB is not fully correct

Bug #78888	SHOW GRANTS requires the SELECT privilege for the mysql DB is not fully correct
Submitted:	20 Oct 2015 16:33	Modified:	18 Feb 2016 18:07
Reporter:	Mariella Di Giacomo	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server: Documentation	Severity:	S3 (Non-critical)
Version:	Verion 5.6.19	OS:	CentOS
Assigned to:	Paul DuBois	CPU Architecture:	Any

Description:
Everyone,

I would like to let you know that the documentation 
https://dev.mysql.com/doc/refman/5.6/en/show-grants.html shares the following paragraph, that does not match fully the reality :

.......
This statement lists the GRANT statement or statements that must be issued to duplicate the privileges that are granted to a MySQL user account. SHOW GRANTS requires the SELECT privilege for the mysql database, except to see the privileges for the current user. 
.........
 

The paragraph above can be translated in SQL as :

GRANT USAGE ON *.* TO 'user_ro'@'localhost' IDENTIFIED BY PASSWORD '*9......C29';
GRANT SELECT ON `mysql`.* TO 'user_ro'@'localhost';

The grant entitlement does not allow the dump of the hashed password. 
It does allow the dump of the entitlements, without having the information related to the hashed password.

E.g.
The output of using the SHOW GRANT for the SQL users, using a user with the suggested entitlements is something like:

GRANT USAGE ON *.* TO 'user_ro'@'localhost' IDENTIFIED BY PASSWORD <secret>;
.........

Would it be possible to have the documentation modified with more details on it and share also with are the minimal entitlements that are required to dump also the hashed password ? Is there any workaround, to extract also the hashed password and avoiding  the full set of privileges to a SQL account to achieve that ?

Thanks in advance for your help.

I look forward to hearing from you,

Mariella

How to repeat:

GRANT USAGE ON *.* TO 'user_ro'@'localhost' IDENTIFIED BY PASSWORD '*9......C29';
GRANT SELECT ON `mysql`.* TO 'user_ro'@'localhost';

SHOW GRANTS FOR ..............

Hi,

Thanks for the bug submission. Yes, the documentation could use some more detailed description.

In order to see the hash you can easily:

mysql>  GRANT USAGE ON *.* TO 'user_ro'@'localhost' IDENTIFIED BY '123456';
Query OK, 0 rows affected (0.00 sec)

mysql> GRANT SELECT ON `mysql`.* TO 'user_ro'@'localhost';
Query OK, 0 rows affected (0.00 sec)

mysql> \q

]$ ./use -u user_ro -p
Enter password:
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 4
Server version: 5.6.24 MySQL Community Server (GPL)

Copyright (c) 2000, 2015, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> SHOW GRANTS;
+--------------------------------------------------------------------+
| Grants for user_ro@localhost                                       |
+--------------------------------------------------------------------+
| GRANT USAGE ON *.* TO 'user_ro'@'localhost' IDENTIFIED BY PASSWORD |
| GRANT SELECT ON `mysql`.* TO 'user_ro'@'localhost'                 |
+--------------------------------------------------------------------+
2 rows in set (0.00 sec)

mysql> use mysql
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed

mysql> select * from user where user='user_ro'\G
*************************** 1. row ***************************
                  Host: localhost
                  User: user_ro
              Password: *6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9
           Select_priv: N
           Insert_priv: N
           Update_priv: N
           Delete_priv: N
           Create_priv: N
             Drop_priv: N
           Reload_priv: N
         Shutdown_priv: N
          Process_priv: N
             File_priv: N
            Grant_priv: N
       References_priv: N
            Index_priv: N
            Alter_priv: N
          Show_db_priv: N
            Super_priv: N
 Create_tmp_table_priv: N
      Lock_tables_priv: N
          Execute_priv: N
       Repl_slave_priv: N
      Repl_client_priv: N
      Create_view_priv: N
        Show_view_priv: N
   Create_routine_priv: N
    Alter_routine_priv: N
      Create_user_priv: N
            Event_priv: N
          Trigger_priv: N
Create_tablespace_priv: N
              ssl_type:
            ssl_cipher:
           x509_issuer:
          x509_subject:
         max_questions: 0
           max_updates: 0
       max_connections: 0
  max_user_connections: 0
                plugin: mysql_native_password
 authentication_string:
      password_expired: N
1 row in set (0.00 sec)

and as you can see the hash is visible

I did verify the bug so our documentation team will work on rewording and adding some data into documentation

kind regards
Bogdan Kecman

Thank you for your bug report. This issue has been addressed in the documentation. The updated documentation will appear on our website shortly.

In more recent versions of MySQL, you'll need the SUPER privilege or the IDENTIFIED BY PASSWORD clause will display <secret> rather than the literal hash value.

Or, as Bogdan points out, with the SELECT privilege for the mysql.user table, you can select its contents directly.

As of 5.7.6, SHOW GRANTS does not display IDENTIFIED BY PASSWORD output; use SHOW CREATE USER instead to see nonprivilege account information.