SHOW GRANTS not displaying all the applicable grants

Bug #1354988 reported by Jaime Sicam on 2014-08-11
10
This bug affects 2 people
Affects Status Importance Assigned to Milestone
MySQL Server
Unknown
Unknown
Percona Server moved to https://jira.percona.com/projects/PS
Fix Released
Medium
Vlad Lesin
5.5
Fix Released
Medium
Vlad Lesin
5.6
Fix Released
Medium
Vlad Lesin

Bug Description

Tested on PXC 5.6.19

Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 3
Server version: 5.6.19-67.0-56 Percona XtraDB Cluster (GPL), Release rel67.0, Revision 824, WSREP version 25.6, wsrep_25.6.r4111

Copyright (c) 2009-2014 Percona LLC and/or its affiliates
Copyright (c) 2000, 2014, 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> create user grantee@localhost identified by 'grantee1';
Query OK, 0 rows affected (0.50 sec)

mysql> create user grantee identified by 'grantee2';
Query OK, 0 rows affected (0.09 sec)

mysql> create database db2;
Query OK, 1 row affected (0.20 sec)

mysql> grant all privileges on db2.* to grantee with grant option;
Query OK, 0 rows affected (0.12 sec)

mysql> flush privileges;
Query OK, 0 rows affected (0.07 sec)

mysql> quit
Bye

[root@localhost log]# mysql -ugrantee -pgrantee1 -h localhost
Warning: Using a password on the command line interface can be insecure.
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 9
Server version: 5.6.19-67.0-56 Percona XtraDB Cluster (GPL), Release rel67.0, Revision 824, WSREP version 25.6, wsrep_25.6.r4111

Copyright (c) 2009-2014 Percona LLC and/or its affiliates
Copyright (c) 2000, 2014, 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 grantee@localhost |
+----------------------------------------------------------------------------------------------------------------+
| GRANT USAGE ON *.* TO 'grantee'@'localhost' IDENTIFIED BY PASSWORD '*9823FF338D44DAF02422CF24DD1F879FB4F6B232' |
+----------------------------------------------------------------------------------------------------------------+
1 row in set (0.04 sec)

mysql> create table db2.t1(a int);
Query OK, 0 rows affected (1.21 sec)

Related branches

Przemek (pmalkowski) on 2014-08-11
Changed in percona-server:
status: New → Confirmed
tags: added: upstream
Vlad Lesin (vlad-lesin) wrote :

The bugfix was contributed to upstream (http://bugs.mysql.com/bug.php?id=53645&contribs=1) and the bug report was reclassified as feature request:

Thank you for the contribution. But as our documentation (http://dev.mysql.com/doc/refman/5.7/en/show-grants.html) clearly states:

"SHOW GRANTS displays only the privileges granted explicitly to the named account. Other privileges might be available to the account, but they are not displayed. For example, if an anonymous account exists, the named account might be able to use its privileges, but SHOW GRANTS will not display them. "

However I do agree that the effective privileges is a really nice information to have. Thus I believe this is a very relevant feature request. Reclassifying it as such.

Percona now uses JIRA for bug reports so this bug report is migrated to: https://jira.percona.com/browse/PS-191

To post a comment you must log in.
This report contains Public information  Edit
Everyone can see this information.

Other bug subscribers

Remote bug watches

Bug watches keep track of this bug in other bug trackers.