MySQL Bugs: #53645: SHOW GRANTS not displaying all the applicable grants

Bug #53645	SHOW GRANTS not displaying all the applicable grants
Submitted:	14 May 2010 12:57	Modified:	27 May 2010 6:02
Reporter:	Georgi Kodinov	Email Updates:
Status:	Verified	Impact on me:	None
Category:	MySQL Server: Security: Privileges	Severity:	S4 (Feature request)
Version:	5.1-bugteam, 5.0, 5.1, 5.6.99	OS:	Any
Assigned to:		CPU Architecture:	Any

Description:
SHOW GRANTS doesn't always show all of the grants applicable to the current user.

How to repeat:
As root:
create user grantee@localhost identified by 'grantee1';
create user grantee identified by 'grantee2';
create database db2;
grant all privileges on db2.* to grantee with grant option;

as grantee1 coming from localhost ("mysql --socket=var/tmp/mysqld.1.sock -u grantee -p" and enter password grantee1) :
mysql> select user(), current_user();
--------------
select user(), current_user()
--------------

+-------------------+-------------------+
| user()            | current_user()    |
+-------------------+-------------------+
| grantee@localhost | grantee@localhost |
+-------------------+-------------------+

mysql> show grants;
--------------
show grants
--------------

+----------------------------------------------------------------------------------------------------------------+
| Grants for grantee@localhost                                                                                   |
+----------------------------------------------------------------------------------------------------------------+
| GRANT USAGE ON *.* TO 'grantee'@'localhost' IDENTIFIED BY PASSWORD '*9823FF338D44DAF02422CF24DD1F879FB4F6B232' |
+----------------------------------------------------------------------------------------------------------------+

Note how GRANT ALL PRIVILEGES ON `db1`.* TO 'grantee1'@'%' is missing.

However I'm fully able to use the privilege (as grantee@localhost): 
mysql> use db2;
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> create table t2 (a int);
--------------
create table t2 (a int)
--------------

Query OK, 0 rows affected (0,09 sec)

I can even grant it to somebody else as grantee1@localhost :
mysql> grant all privileges on db2.* to further_grantee;
--------------
grant all privileges on db2.* to further_grantee
--------------

Query OK, 0 rows affected (0,00 sec)

Suggested fix:
Make sure SHOW GRANTS shows all privileges that are in effect for the current user (as documented).

If you login from another host (and using grantee2 as a password) you get the correct output ("mysql --port=13010 --host=mbpro -u grantee -p" and enter grantee2 as password) :

mysql> select user(),current_user();
--------------
select user(),current_user()
--------------

+-------------------+----------------+
| user()            | current_user() |
+-------------------+----------------+
| grantee@mbpro.gmz | grantee@%      |
+-------------------+----------------+
1 row in set (0,00 sec)

mysql> show grants;
--------------
show grants
--------------

+--------------------------------------------------------------------------------------------------------+
| Grants for grantee@%                                                                                   |
+--------------------------------------------------------------------------------------------------------+
| GRANT USAGE ON *.* TO 'grantee'@'%' IDENTIFIED BY PASSWORD '*4F2A58C99DBF8B6F153C1227BEA2B201675D2DEB' |
| GRANT ALL PRIVILEGES ON `db2`.* TO 'grantee'@'%' WITH GRANT OPTION                                     |
+--------------------------------------------------------------------------------------------------------+
2 rows in set (0,00 sec)

Joro,

this could be OS related.

Was your first system Linux and the second something else then Linux?

Hi,

I've tried this on a MacOSX 10.6 and on Fedora 12 64 bit.

Thank you for the report.

Verified as described. This is not OS-related.

Test case for MTR:

create user grantee@localhost identified by 'grantee1';
create user grantee identified by 'grantee2';
create database db2;
grant all privileges on db2.* to grantee with grant option;

connect (con1, localhost, grantee,grantee1,);
connection con1;
select user(), current_user();
show grants;
use db2;
create table t1(f1 int);

This annoying issue still applies to MySQL 5.6.19.
The "show grants" won't tell me what are my all effective per-database or per-table privileges. Actually I don't think there is any command to get that knowledge other them joining 3 tables from mysql database.

Also it's confusing as you may think an user logs with "userA"@"x.x.x.x" but in fact, all the privileges for "userA"@"x.x.x.%" or "userA"@"%" also apply to him. I guess at least the documentation should be more clear about that.

the patch for revno 4735 of 5.5 (*) I confirm the code being submitted is offered under the terms of the OCA, and that I am authorized to contribute it.

Contribution: mysql-5.5.bug-1354988.patch (text/x-patch), 9.10 KiB.

the patch for revno 6235 of 5.6 (*) I confirm the code being submitted is offered under the terms of the OCA, and that I am authorized to contribute it.

Contribution: mysql-5.6.bug-1354988.patch (text/x-patch), 10.58 KiB.

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.