Bug #47961 metadata leak from information_schema.routines via privs on mysql.proc table
Submitted: 9 Oct 2009 21:51 Modified: 9 Oct 2009 23:28
Reporter: Jeff Stoner Email Updates:
Status: Verified Impact on me:
None 
Category:MySQL Server: Information schema Severity:S3 (Non-critical)
Version:5.1.39-community,5.5+ OS:Linux (only tested on CentOS 5.3)
Assigned to: CPU Architecture:Any
Tags: information_schema, privileges, routines

[9 Oct 2009 21:51] Jeff Stoner
Description:
If you grant a user column privileges on mysql.proc then they can access all data in mysql.proc through the information_schema.routines view.

I noticed this behavior on 5.1.22 and tested again on 5.1.39-community.

Similar bugs: 11577, 7214, 10964

How to repeat:
$ mysql -uroot -p
create database foobar;
grant all privileges on foobar.* to 'foo1'@'localhost' identified by 'foo1';
grant select (name) on mysql.proc to 'bar2'@'localhost' identified by 'bar2';
\q

$ mysql -ufoo1 -p

use foobar;
delimiter //
create procedure bugsp()
 begin
 select 1+1;
 end//
delimiter ;
call bugsp();
+-----+
| 1+1 |
+-----+
|   2 |
+-----+
1 row in set (0.00 sec)
\q

$ mysql -ubar2 -p

select name from mysql.proc;
+-------+
| name  |
+-------+
| bugsp |
+-------+
1 row in set (0.00 sec)

select body from mysql.proc;
ERROR 1143 (42000): SELECT command denied to user 'bar2'@'localhost' for column 'body' in table 'proc'

select db from mysql.proc;
ERROR 1143 (42000): SELECT command denied to user 'bar2'@'localhost' for column 'db' in table 'proc'

select * from information_schema.routines\G
*************************** 1. row ***************************
       SPECIFIC_NAME: bugsp
     ROUTINE_CATALOG: NULL
      ROUTINE_SCHEMA: foobar
        ROUTINE_NAME: bugsp
        ROUTINE_TYPE: PROCEDURE
      DTD_IDENTIFIER: NULL
        ROUTINE_BODY: SQL
  ROUTINE_DEFINITION: begin
 select 1+1;
 end
       EXTERNAL_NAME: NULL
   EXTERNAL_LANGUAGE: NULL
     PARAMETER_STYLE: SQL
    IS_DETERMINISTIC: NO
     SQL_DATA_ACCESS: CONTAINS SQL
            SQL_PATH: NULL
       SECURITY_TYPE: DEFINER
             CREATED: 2009-10-09 17:17:09
        LAST_ALTERED: 2009-10-09 17:17:09
            SQL_MODE: 
     ROUTINE_COMMENT: 
             DEFINER: foo1@localhost
CHARACTER_SET_CLIENT: latin1
COLLATION_CONNECTION: latin1_swedish_ci
  DATABASE_COLLATION: latin1_swedish_ci
1 row in set (0.00 sec)

Suggested fix:
Privileges on mysql.proc should translate to information_schema.routines such that if the user cannot see the data in mysql.proc, they should not see the data in information_schema.routines.

If this is not a bug, a detailed explanation on how privileges on the mysql schema affect the information_schema views would be very much appreciated.
[9 Oct 2009 23:28] MySQL Verification Team
Thank you for the bug report. Verified as described.

mysql> select body from mysql.proc;
ERROR 1143 (42000): SELECT command denied to user 'bar2'@'localhost' for column 'body' in table 'proc'
mysql> select db from mysql.proc;                                                                     
ERROR 1143 (42000): SELECT command denied to user 'bar2'@'localhost' for column 'db' in table 'proc'  
mysql> select * from information_schema.routines\G                                                    
*************************** 1. row ***************************                                        
       SPECIFIC_NAME: bugsp                                                                           
     ROUTINE_CATALOG: NULL                                                                            
      ROUTINE_SCHEMA: foobar                                                                          
        ROUTINE_NAME: bugsp                                                                           
        ROUTINE_TYPE: PROCEDURE                                                                       
      DTD_IDENTIFIER: NULL                                                                            
        ROUTINE_BODY: SQL                                                                             
  ROUTINE_DEFINITION: begin                                                                           
 select 1+1;                                                                                          
 end                                                                                                  
       EXTERNAL_NAME: NULL                                                                            
   EXTERNAL_LANGUAGE: NULL                                                                            
     PARAMETER_STYLE: SQL                                                                             
    IS_DETERMINISTIC: NO                                                                              
     SQL_DATA_ACCESS: CONTAINS SQL                                                                    
            SQL_PATH: NULL                                                                            
       SECURITY_TYPE: DEFINER                                                                         
             CREATED: 2009-10-09 20:24:10
        LAST_ALTERED: 2009-10-09 20:24:10
            SQL_MODE:
     ROUTINE_COMMENT:
             DEFINER: foo1@localhost
CHARACTER_SET_CLIENT: latin1
COLLATION_CONNECTION: latin1_swedish_ci
  DATABASE_COLLATION: latin1_swedish_ci
1 row in set (0.01 sec)

mysql> select version();
+------------------+
| version()        |
+------------------+
| 5.5.0-beta-debug |
+------------------+
1 row in set (0.00 sec)

mysql>
[19 Oct 2009 21:33] Timothy Smith
This is a reasonable request to improve the privilege system in this area.

Granting access to mysql.* system tables should be done for privileged users only; such access really should be limited to users with SUPER privilege; other users should get their information via INFORMATION_SCHEMA or SHOW statements.