Bug #64590 "show grants for" show the error privileges when grant *.* and db.* both

• Submitted: 8 Mar 2012 7:16
• Modified: 9 Mar 2012 17:53
• Reporter: Lixun Peng (OCA)
• Status: Not a Bug
• Category: MySQL Server: Security: Privileges
• Severity: S3 (Non-critical)
• Version: 5.1
• OS: Any
• CPU Architecture: Any
• Tags: privileges, show grants

[8 Mar 2012 7:16] Lixun Peng

Description:
when I grant privileges use both "grant *.*" and "grant db.*",
"show grants for" only show the db.* privileges;

How to repeat:
GRANT SELECT ON `abc`.* TO 'account'@'%';

root@localhost : mysql 11:00:46> show grants for account;
+--------------------------------------------------------------------------------------------------------------------------------------------------+
| Grants for account@%                                                                                                                           |
+--------------------------------------------------------------------------------------------------------------------------------------------------+
| GRANT SELECT, REPLICATION SLAVE, REPLICATION CLIENT ON *.* TO 'account'@'%' IDENTIFIED BY PASSWORD '*xxx' |
| GRANT SELECT ON `abc`.* TO 'account'@'%'                                                                                                   |
+--------------------------------------------------------------------------------------------------------------------------------------------------+
2 rows in set (0.00 sec)

root@localhost : (none) 10:48:42> grant select on *.* to account@'%';
Query OK, 0 rows affected (0.00 sec)

root@localhost : (none) 10:48:56> flush privileges;
Query OK, 0 rows affected (0.00 sec)

root@localhost : (none) 10:48:58> show grants for account;
+--------------------------------------------------------------------------------------------------------------------------------------------------+
| Grants for account@%                                                                                                                           |
+--------------------------------------------------------------------------------------------------------------------------------------------------+
| GRANT SELECT, REPLICATION SLAVE, REPLICATION CLIENT ON *.* TO 'account'@'%' IDENTIFIED BY PASSWORD '*xxx' |
| GRANT SELECT ON `abc`.* TO 'account'@'%'                                                                                                   |
+--------------------------------------------------------------------------------------------------------------------------------------------------+
2 rows in set (0.00 sec)

root@localhost : mysql 11:00:32> select * from user where user='account'\G
*************************** 1. row ***************************
                 Host: %
                 User: account
             Password: *xxx
          Select_priv: Y
          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: Y
     Repl_client_priv: Y
     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
             ssl_type:
           ssl_cipher:
          x509_issuer:
         x509_subject:
        max_questions: 0
          max_updates: 0
      max_connections: 0
 max_user_connections: 0
1 row in set (0.00 sec)

[9 Mar 2012 17:53] Sveta Smirnova

Thank you for taking the time to write to us, but this is not a bug. Please double-check the documentation available at http://dev.mysql.com/doc/ and the instructions on
how to report a bug at http://bugs.mysql.com/how-to-report.php

SELECT ON *.* actually reported, because it is already part of following privilege:

GRANT SELECT, REPLICATION SLAVE, REPLICATION CLIENT ON *.* TO 'account'@'%' IDENTIFIED BY PASSWORD '*xxx'