MySQL Bugs: #64215: SHOW GRANTS does not require any privilege

Bug #64215	SHOW GRANTS does not require any privilege
Submitted:	2 Feb 2012 19:54	Modified:	6 Feb 2012 19:20
Reporter:	Peter Laursen (Basic Quality Contributor)	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server: Documentation	Severity:	S3 (Non-critical)
Version:	any (tried 5.1.60)	OS:	Any
Assigned to:	Paul DuBois	CPU Architecture:	Any
Tags:	qc

Description:
http://dev.mysql.com/doc/refman/5.1/en/show-grants.html

(at the bottom) tells "SHOW GRANTS requires the SELECT privilege for the mysql database.".  But a user without any privilege can "SHOW GRANTS" (for himself - ie. omitting "FOR user@host" in the statement). 

How to repeat:
1) as root

CREATE USER pl2

2) as pl2

SHOW GRANTS;

/* returns
Grants for pl2@%                 
---------------------------------
GRANT USAGE ON *.* TO 'pl2'@'%'  
*/

Suggested fix:
"SHOW GRANTS /*for any other user than current user*/ requires the SELECT privilege for /*the privileges tables in the*/ mysql database."

(as per 2nd comment: no privilege is required to time_zone*, help*, *log etc. tables)

fixed typo in synopsis.

Acutally a user without privilege may even include his own username and host in the statement:

SHOW GRANTS FOR 'pl2'@'%';
-- success

SHOW GRANTS FOR 'other_user'@'any_host';
Error CODE: 1044
Access denied FOR USER 'pl2'@'%' TO DATABASE 'mysql'

My last comment seems not to be correct:

SHOW GRANTS;

/*Grants for pl2@%                                     
-----------------------------------------------------
GRANT USAGE ON *.* TO 'pl2'@'%'                      
GRANT SELECT ON `mysql`.`user` TO 'pl2'@'%'          
GRANT SELECT ON `mysql`.`tables_priv` TO 'pl2'@'%'   
GRANT SELECT ON `mysql`.`procs_priv` TO 'pl2'@'%'    
GRANT SELECT ON `mysql`.`db` TO 'pl2'@'%'            
GRANT SELECT ON `mysql`.`columns_priv` TO 'pl2'@'%'*/

SHOW GRANTS FOR root@localhost
-- Error Code: 1044
-- Access denied for user 'pl2'@'%' to database 'mysql'

But that is a bug and a serious security flaw. I would not necessarily want every user with privilege to SHOW GRANTS to read log tables! Log tables contains real data (email addresses, bank account numbers .. you name it!). So user taht do not have access to data in databases/tables will stil be able to read data they should not from the query (In the WHERE-clause of queries for instance "... WHERE bank_account_no = 123456789"). Actually I'd like a comment on this soon. Alternatively this is subject for my next blog.

Only privilege to privileges *tables* should be required - not to the mysql database per se - in order to execute SHOW GRANTS FOR some_other_user. Or log tables should be in a separate database.

Verified on Windows XP:

C:\Program Files\MySQL\MySQL Server 5.5\bin>mysql -upl2 -P3310
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 18
Server version: 5.1.58-community-log MySQL Community Server (GPL)

Copyright (c) 2000, 2011, 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 pl2@%                |
+---------------------------------+
| GRANT USAGE ON *.* TO 'pl2'@'%' |
+---------------------------------+
1 row in set (0.00 sec)

mysql> show grants for root@localhost;
ERROR 1044 (42000): Access denied for user 'pl2'@'%' to database 'mysql'
mysql> show grants for pl2@'%';
+---------------------------------+
| Grants for pl2@%                |
+---------------------------------+
| GRANT USAGE ON *.* TO 'pl2'@'%' |
+---------------------------------+
1 row in set (0.00 sec)

Hi Peter,

can you explain why this would be a documentation bug? You're suggesting it's a security flaw; in that case it would be a bug in the MySQL Server category. We can't simply "paper over" security issues. ;-)

Also, I'm not clear why you're referring to log files. SHOW GRANTS doesn't read from log files, but rather from the MySQL system tables.

Stefan

1)
The statment "SHOW GRANTS requires the SELECT privilege for the mysql database." is incorrect because a user can "SHOW GRANTS [for himself]" without any privilege at all. Correct would be "SHOW GRANTS *for any other user than current user requires the SELECT privilege for the mysql database."

2) 
I have explained why I think it is a security flaw here: 
http://www.webyog.com/blog/2012/02/03/log-security-and-log-tables/
There could/should be an option to define a database where logs are stored (just like you can define file paths for file-based logs) so that users with SELECT privilege for the mysql database can be banned from reading log content.  Do you want a separate report for this?

Thanks for the feedback, Peter! Assigning the first part of the bug (SELECT priv required when it's not) to my team.

For the other part (security flaw), please create a separate bug report in the MySQL Server (not documentation!) category.

Thanks! Stefan

Thank you for your bug report. This issue has been addressed in the documentation. The updated documentation will appear on our website shortly, and will be included in the next release of the relevant products.

Updated text:

SHOW GRANTS requires the SELECT privilege for the mysql database,
except to see the privileges for the current user.

OK - posted http://bugs.mysql.com/bug.php?id=64243